PostgreSQL ARRAY_AGG函数用法教程：如何将分组数据转为数组详解

先说一个常见的坑：ARRAY_AGG默认会把NULL值也塞进数组里。比如聚合结果是[1, NULL, 3]，这时候用UNNEST展开或者做JSON转换就容易出问题。正确的做法是加上FILTER显式过滤，或者用COALESCE把NULL替换掉。

ARRAY_AGG 会把 NULL 值也塞进数组里吗？

答案是：会的。只要分组里某一行这个字段是NULL，ARRAY_AGG就照单全收，把它放进数组里。它不会自动跳过，也不会变成空字符串。很多人在后续用UNNEST展开或者应用层解析时，才发现被NULL坑了。

• 想排除NULL的话，必须显式过滤：ARRAY_AGG(col) FILTER (WHERE col IS NOT NULL)
• 如果字段是字符串类型，希望用空字符串替代NULL，得先用COALESCE(col, '')转换再聚合
• 注意：FILTER子句不能和普通WHERE混用——前者作用于聚合输入行，后者作用于整个分组前的原始行

ORDER BY 在 ARRAY_AGG 里怎么写才生效？

这个顺序必须写在函数内部，而不是外部GROUP BY之后的ORDER BY。外部的ORDER BY只控制结果集的排列顺序，管不了数组内部元素的顺序。

• 正确写法：ARRAY_AGG(col ORDER BY col DESC)
• 支持多字段排序：ARRAY_AGG(col ORDER BY priority DESC, id ASC)
• 如果排序字段可能为NULL，记得加NULLS FIRST或NULLS LAST，否则不同PostgreSQL版本行为可能不一致（比如15+默认NULLS LAST）

聚合字符串时，用 ARRAY_AGG 还是 STRING_AGG？

这取决于下游怎么消费。如果应用代码需要按索引取值、做集合运算（比如用@>做包含判断），或者要保留重复项，那就用ARRAY_AGG；如果只是拼成逗号分隔的字符串用来展示或导出，STRING_AGG更轻量，也更易读。

• ARRAY_AGG返回text[]类型，支持数组操作符（ANY、@>、&&等），但序列化开销略高
• STRING_AGG可指定分隔符和ORDER BY，且对NULL默认忽略（不像ARRAY_AGG那样需要手动FILTER）
• 别硬套：把ARRAY_AGG结果再用ARRAY_TO_STRING转成字符串，不如直接用STRING_AGG

大表 GROUP BY + ARRAY_AGG 性能突然变慢，可能卡在哪？

问题往往不在聚合本身，而是内存不够，触发了磁盘临时文件（temp_file）。尤其当单个分组产出超大数组时，比如一个用户关联了几千条标签，默认的work_mem很容易撑不住。

• 查是否用了磁盘：EXPLAIN (ANALYZE, BUFFERS) SELECT ... ARRAY_AGG(...) GROUP BY ...，看是否有Temp File行
• 临时调优可设会话级SET work_mem = '64MB'，但别全局改——容易耗尽内存
• 更可持续的做法是：提前用子查询限制每组最大数量，例如ARRAY_AGG(sub.col) FILTER (WHERE sub.rn <= 100)，配合ROW_NUMBER() OVER (PARTITION BY ...)

所以，数组元素顺序、NULL处理、内存水平——这三个点，漏掉任何一个，都可能让查询在测试环境跑得欢，一上线就慢得像蜗牛，甚至直接OOM。