SQL Academy · 课时

UNNEST 与聚合

Q: 「UNNEST 与聚合」这节课中我会学到什么？

将数组转换为行，也可反向转换 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

将数组转换为行，也可反向转换

UNNEST 与聚合 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是 UNNEST

PostgreSQL 允许您在单个列中存储数组。但有时您需要分别处理每个元素，这正是 UNNEST 的用途。

UNNEST 是一个返回集合的函数，会将数组展开为一个行集合，每个元素对应一行。您可以将它理解为聚合的反向操作：聚合是将多行合并为一行，而它则是将一个值展开为多行。

UNNEST 基础示例

UNNEST 最简单的用法是直接传入数组字面量。每个元素都会在结果集中成为单独的一行。

下面将一个普通的整数数组展开为多行：

SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;

对文本数组使用 UNNEST

UNNEST 适用于任何数组类型，包括文本数组。当某一列以 PostgreSQL 数组的形式存储标签、类别或类似逗号分隔的数据时，这一功能非常有用。

SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;

从表列中使用 UNNEST

在实际表列上使用 UNNEST 时，它的真正作用才会显现出来。表中的每一行都可以包含长度不同的数组，而 UNNEST 会将所有数组展开为单独的行。

在这个示例中，products 表有一个 tags 文本数组列：

CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Laptop',  ARRAY['electronics', 'computing', 'portable']),
  ('Shirt',   ARRAY['clothing', 'casual']),
  ('Blender', ARRAY['kitchen', 'electronics']);

SELECT name, UNNEST(tags) AS tag
FROM products;

统计标签出现次数

将数组列展开后，您可以像处理其他行一样处理结果，并应用聚合函数。下面统计每个标签关联了多少个产品。

该模式是：在子查询或 CTE 中使用 UNNEST，然后对展开后的值使用 GROUP BY：

SELECT tag, COUNT(*) AS product_count
FROM (
  SELECT UNNEST(tags) AS tag
  FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;

使用 WITH ORDINALITY 的 UNNEST

有时元素在数组中的位置很重要。PostgreSQL 提供 WITH ORDINALITY，为每个展开后的元素附加行号，让您知道它在数组中的原始索引（从 1 开始）。

SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);

在表上使用序号

当您在数组中存储有序列表时，WITH ORDINALITY 尤其有用。例如，曲目顺序很重要的播放列表表：

CREATE TABLE playlists (
  id     SERIAL PRIMARY KEY,
  title  TEXT,
  tracks TEXT[]
);

INSERT INTO playlists (title, tracks) VALUES
  ('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);

SELECT p.title, track, position
FROM playlists p,
     UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;

在 UNNEST 后进行筛选

由于 UNNEST 会将数组元素转换为行，因此您可以使用普通的 WHERE 子句对它们进行筛选。这样，您无需使用数组包含运算符，就能找到数组包含特定值的所有行。

SELECT DISTINCT name
FROM products,
     UNNEST(tags) AS tag
WHERE tag = 'electronics';

重新聚合为数组

UNNEST 的反向操作是 ARRAY_AGG。展开行并对其进行转换或筛选后，您可以将结果重新收集为数组。这种往返模式——展开、处理、重新聚合——是 PostgreSQL 中常见的惯用写法。

SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
  SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;

数组元素去重

将数组展开并重新聚合这一过程的一个实用用途，是移除数组中的重复元素。先对数组执行 UNNEST，再应用 DISTINCT，最后使用 ARRAY_AGG 重新收集：

SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;

组合多个数组列

您可以在 FROM 子句中并行对多个数组执行 UNNEST。PostgreSQL 会按位置将元素配对。如果数组长度不同，较短数组会在较长数组多出的那些位置产生空值。

SELECT key, value
FROM UNNEST(
  ARRAY['name',    'city',      'role'],
  ARRAY['Alice',   'Istanbul',  'DBA']
) AS t(key, value);

知识检查

检验您对 PostgreSQL 中 UNNEST 以及数组聚合的理解。

课程回顾

在本课中，您学习了如何使用 PostgreSQL 内置函数将数组转换为行，再转换回来：

UNNEST — 将数组展开为每个元素一行，同时适用于字面量和表列。
WITH ORDINALITY — 为每个展开的元素附加位置索引，以便您知道它在数组中的原始顺序。
筛选 — 展开数组后，您可以像处理普通行一样使用 WHERE。
ARRAY_AGG — UNNEST 的逆操作；将行重新收集为数组，还可以选择使用 ORDER BY 或 DISTINCT。
并行 UNNEST — FROM 子句中的多个数组会并排展开，并按位置配对。

掌握这种展开、处理、重新聚合的模式后，您就能运用 SQL 的完整表达能力处理数组数据。

常见问题解答

「UNNEST 与聚合」课时是免费的吗？

是的 — 「UNNEST 与聚合」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「UNNEST 与聚合」这节课中我会学到什么？

将数组转换为行，也可反向转换你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「UNNEST 与聚合」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy