string_agg(expression, separator) 把每组中的值拼接成一个字符串,array_agg(expression) 把它们收集成一个数组。在调用内部写 ORDER BY 来控制顺序:string_agg(name, ', ' ORDER BY name)。
IT 返回 Bo, Kim, Li 和 {6100,4800,3900};Sales 返回 Ana, Raj 和 {5000,4200}。两者都是和 count、sum 一样的普通聚合函数:配合 GROUP BY 时按组计算,不加时对整张表计算。
给值排序
如果聚合函数内部没有 ORDER BY,值的顺序就是行到达的顺序,每次运行都可能不同。写在查询末尾的 ORDER BY 排的是结果行,而不是每个字符串内部的值。内部的 ORDER BY 写在最后一个参数之后,中间没有逗号:
string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)
用 DISTINCT 去重
Ada 买了两次笔,但 DISTINCT 只列出一次:book, pen。使用 DISTINCT 时,ORDER BY 只能用被聚合的值。按其他列排序会报错“in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list”。
NULL 和空分组的表现
string_agg 会跳过 NULL 值,返回 Bo, Li。array_agg 会保留它们:{Bo,Li,NULL}。要从 array_agg 中去掉 NULL,加上 FILTER (WHERE name IS NOT NULL)。
在零行上,两者都返回 NULL,而不是空字符串或空数组。如果调用方需要一个值,就把它们包起来:COALESCE(string_agg(...), '') 或 COALESCE(array_agg(...), '{}')。
用 FILTER 只聚合部分行
FILTER (WHERE ...) 让某一个聚合函数只处理匹配的行,而查询的其余部分仍然能看到所有行:
IT 有 3 个人,列出 Bo, Li;Sales 有 2 个人,列出 Ana。
给另一张表的每一行附上一个列表
要显示每位客户及其商品列表,就在子查询中聚合,这样外层查询不需要 GROUP BY:
SELECT c.id, c.name, c.email,
(SELECT string_agg(o.product, ', ' ORDER BY o.product)
FROM orders o
WHERE o.customer_id = c.id) AS products
FROM customers c;
没有订单的客户仍然留在结果中,products 为 NULL。如果改用连接加 GROUP BY,只有 LEFT JOIN 才能保留他们。
常见报错和变体
- 数字:
string_agg接收文本,所以string_agg(id, ',')会报错“function string_agg(integer, unknown) does not exist”。转换一下:string_agg(id::text, ',')。 - 每组前 N 个:对排好序的数组切片,
(array_agg(name ORDER BY salary DESC))[1:2]。 - 输出 JSON:
json_agg和jsonb_agg把行收集成 JSON 数组,jsonb_object_agg(key, value)构造一个对象;见 JSONB。 - 已经有数组:
array_to_string(arr, ', ')拼接已有的数组,unnest把它展开回行(数组)。 - 窗口函数版本:两者都能用作窗口函数,比如
string_agg(name, ', ') OVER (PARTITION BY dept),在每一行上显示完整的列表。
常见问题
PostgreSQL 中和 GROUP_CONCAT 对应的是什么?
string_agg(column, ',')。和 MySQL 的 GROUP_CONCAT 不同,分隔符是必填的,排序要写在调用内部:string_agg(name, ', ' ORDER BY name)。
string_agg 怎么用于数字?
把值转换成文本:string_agg(id::text, ',')。string_agg 只接受文本(或 bytea),传入整数会报错“function string_agg(integer, unknown) does not exist”。
string_agg 有长度限制吗?
没有单独的限制;结果是普通的 text 值,最多可以容纳 1 GB。MySQL 的 GROUP_CONCAT 会在 group_concat_max_len 处截断,而 PostgreSQL 不会截断。
怎么用 array_agg 取前 N 个值?
在聚合函数内部排序,再对结果切片:(array_agg(name ORDER BY score DESC))[1:3] 返回每组得分最高的三个名字。