PostgreSQL 聚合函数 array_agg()、string_agg()多行转一行一个 SQL 就能搞定做数据库开发的人几乎都碰到过这种需求查一张明细表要把同一个分组下的多行记录合并到一行里展示。最常见的例子就是订单表按客户汇总订单号、文章表按分类汇总标签、权限表按用户合并权限码。以前我处理这种需求第一反应是写一堆子查询或者干脆在应用层用循环去拼字符串直到后来被一个同事指着鼻子说你这 SQL 写得也太原始了才老老实实把 PostgreSQL 自带的两个聚合函数用起来——array_agg()和string_agg()。这两个函数解决的就是多行转一行的问题一个输出数组类型一个输出拼接后的字符串语法简单但细节不少。这篇文章我会从基本用法开始把排序、去重、NULL 处理、类型转换、FILTER 条件过滤、以及与 JSON 系列函数的配合全部过一遍也会把我在实际生产环境里踩过的坑一并交代清楚。无论你是刚接触 PostgreSQL 的新手还是已经用了两三年的开发者这篇内容应该都能让你少走一些弯路。1. 先搞清楚它俩的本质差异数组与字符串的分野很多人刚开始用这两个函数时最困惑的就是我到底该用哪个。要回答这个问题得先明确两者输出的数据类型完全不同。1.1 array_agg 的基本用法与输出形态array_agg()把某一列的多行值聚合成一个数组。直接看例子SELECT department_id, array_agg(employee_name) AS employee_list FROM employees GROUP BY department_id;运行结果类似这样department_id | employee_list ---------------------------------------------- 1 | {张三,李四,王五} 2 | {赵六,钱七}注意输出花括号包起来的这是 PostgreSQL 的数组字面量格式。如果你在代码里拿到这个结果它是一个真正的数组类型text[]、int[]、uuid[]等等可以继续用数组函数去操作它比如array_length()查个数、unnest()展开成多行也可以在WHERE条件里用 ANY(...)做匹配。我自己的感受是如果你后续还要对这个结果做结构化处理比如判断某个值在不在集合里、取数组长度、或者传给另一个查询用 ANY()过滤那array_agg是首选。因为数组是数据形态不是展示形态它保留了集合的结构。1.2 string_agg 的基本用法与拼接逻辑string_agg()则是把多行值拼成一个字符串需要显式指定分隔符SELECT department_id, string_agg(employee_name, ,) AS employee_list FROM employees GROUP BY department_id;结果变成department_id | employee_list ---------------------------------------------- 1 | 张三,李四,王五 2 | 赵六,钱七分隔符是你在第二个参数里指定的可以是逗号、顿号、竖线、换行符甚至可以是, 这种带空格的逗号。它没有任何数组结构拿到手就是一个纯字符串适合直接输出到报表、邮件、日志或者页面上。1.3 两者选型的判断标准这里我给出一个我自己用了很久的选型逻辑比较粗暴但很管用结果是要给人看的用string_agg。结果是要给程序用的用array_agg。如果既给人看又要给程序用那就看下游消费方的开发成本。比如展示这个客户最近买了哪些商品人类一眼扫过去肯定是iPhone, 充电器, 手机壳这种字符串更舒服但如果你的接口要返回一个数组让前端去渲染标签组件那array_agg直接映射成 JSON 数组就自然得多省得在 Java 或 Python 里再 split 一次。还有一个细节值得注意这两种聚合函数最终输出的行数是由GROUP BY决定的如果没有GROUP BY整个查询只会返回一行。这个前提听起来是废话但实际业务中经常有人在写复杂查询时忘了这一点最后聚合结果莫名其妙少了很多行。2. 排序问题比想象中更关键聚合内部的 ORDER BY 到底该放哪这绝对是新手到进阶的一道坎。很多人写聚合函数时老觉得聚合出来的顺序差不多就行结果某天在分页接口里看到数据顺序乱跳才回头来查排序问题。2.1 聚合函数内部自带 ORDER BY如果你希望最终合并出来的数据是有序的比如订单号从小到大拼接写法不是去外面套一层ORDER BY而是在聚合函数内部指定排序规则SELECT customer_id, string_agg(order_no, , ORDER BY order_no ASC) AS order_list FROM orders GROUP BY customer_id;为什么不能在GROUP BY外面直接ORDER BY customer_id因为外部的ORDER BY只能控制分组在结果集里的排列顺序控制不了分组内部元素的顺序。聚合函数内部的ORDER BY才是真正决定哪些值先被聚合进去的关键。如果把GROUP BY比作把一堆水果分到不同的篮子里那外层的ORDER BY决定的是篮子怎么摆聚合函数里的ORDER BY决定的是往篮子里面放水果的顺序。两个层面的事不能互相替代。2.2 多列排序和表达式排序的写法聚合函数内部的ORDER BY不只支持单列也可以多列甚至可以使用表达式SELECT project_id, string_agg( task_name, ; ORDER BY priority DESC, created_at ASC ) AS tasks FROM project_tasks GROUP BY project_id;这段的意思是每个项目下先按优先级从高到低排优先级相同再按创建时间从老到新排。这个顺序完全满足了业务上重要且先来的任务放前面的需求。还有更灵活的场景——按表达式排序。比如我想让某个属性值为空的值排到最后SELECT category_id, string_agg( title, , ORDER BY (published_at IS NULL), published_at DESC ) AS titles FROM posts GROUP BY category_id;这里的技巧是(published_at IS NULL)是一个布尔表达式false也就是有发布时间排在true发布时间为空前面实现空值靠后的效果。2.3 排序带来的性能开销排序是有成本的。聚合函数每收到一组数据都要在内存或临时文件里对组内的行做排序。数据量小的时候感觉不出来但当你对一个几百万行的大表做聚合并且组内元素数量也很大时排序的开销会被明显放大。我在生产环境遇到过一个大查询对某张表按用户维度聚合半年内的操作明细一个用户最多有上千条记录整体数据量超过五百万行。加了聚合内ORDER BY之后查询从 200 毫秒涨到了 1.8 秒左右因为 PostgreSQL 需要在聚合阶段为每个分组维护一个有序的收集结构。遇到这种情况我的建议是分三步走第一确认这个排序是不是业务必须的很多场景其实只需要按MIN或MAX取头尾第二如果非排不可尽量对有序的源数据直接做聚合比如通过索引或者预先排好序的子查询让聚合过程本身不额外排序第三数据量实在太大就考虑物化视图或者干脆在应用层处理。3. 实战避坑清单NULL、去重、类型转换与分隔符用这两个函数最折磨人的不是语法本身而是各种边界情况。我在这里把踩过的坑全部列出来每一个都是真实的业务教训。3.1 NULL 值默认忽略但会有全组为空的特殊返回array_agg和string_agg在聚合时会忽略 NULL 值这一点和count(column)的行为类似。比如一组数据是{苹果, NULL, 香蕉}string_agg的结果是苹果,香蕉不会出现苹果,,香蕉这种尴尬的连续分隔符。但这里藏着一个坑如果整个分组的值全部为 NULLstring_agg的返回值是 NULL而不是空字符串array_agg返回的也是 NULL而不是空数组。SELECT string_agg(name, ,) FROM (VALUES (NULL)) AS t(name);结果是NULL不是。这在报表里经常引发连锁反应你在业务逻辑里判断if (result null)会走到异常分支或者前端显示时空态文案不生效。我的习惯是外面包一层COALESCESELECT COALESCE(string_agg(name, ,), ) AS name_list, COALESCE(array_agg(name), ARRAY[]::text[]) AS name_array FROM ...这样保证下游拿到的永远是空字符串或空数组语义更明确。3.2 去重的正确打开方式DISTINCT 的限制和用法array_agg和string_agg都支持DISTINCT写法是-- 数组去重 SELECT array_agg(DISTINCT tag) FROM post_tags; -- 字符串去重 SELECT string_agg(DISTINCT tag, ,) FROM post_tags;如果用文章的标签集合或者用户的角色集合这类业务直接去重是非常实用的。但注意DISTINCT和聚合内的ORDER BY组合时有一个隐性限制ORDER BY的表达式必须出现在DISTINCT的表达式列表中。-- 合法 SELECT array_agg(DISTINCT tag ORDER BY tag) FROM post_tags; -- 报错排序表达式必须出现在 DISTINCT 列表中 SELECT array_agg(DISTINCT tag ORDER BY sort_order) FROM post_tags;所以如果你既想按业务字段排序又想按某个属性值去重不能在一个聚合函数里同时搞定。我的做法是先做一步预处理——在子查询里用DISTINCT ON先去掉重复项再在外面做聚合排序SELECT category_id, string_agg(tag, , ORDER BY sort_order) FROM ( SELECT DISTINCT ON (category_id, tag) category_id, tag, sort_order FROM post_tags ORDER BY category_id, tag, sort_order ) t GROUP BY category_id;这星期我在一个项目里处理用户绑定的所有应用按最近使用时间排序且不重复输出的需求时就是用的这种两步法效果稳定也容易理解。3.3 类型转换聚合前不转聚合后抓瞎遇到非文本类型数字、时间、UUIDstring_agg会要求你把参数显式转成文本-- 错误示例 SELECT string_agg(amount, ,) FROM orders; -- 报错integer 没有默认字符串类型匹配 -- 正确写法 SELECT string_agg(amount::text, ,) FROM orders;同样时间类型的拼接如果不转换会输出一堆莫名其妙的时区格式。建议在做字符串聚合前先明确你要的最终格式比如按业务习惯把日期转成YYYY-MM-DD再拼接SELECT string_agg(to_char(created_at, YYYY-MM-DD), | ) FROM orders;array_agg则不存在这个问题因为它保留原始类型数字列聚合出来就是int[]UUID 列聚合出来就是uuid[]。但这也意味着如果你要把数组直接用于展示还需要额外转换一层。3.4 分隔符的选择是个看起来小、炸起来大的问题string_agg的分隔符不只是在两个值之间插一下它还会影响下游对数据的还原能力。如果分隔符本身可能出现在业务数据里那么拼接结果在反向解析时会产生歧义。我遇到过最典型的场景拼接员工技能标签时用了逗号结果某个员工有个技能就叫C——当然这种不会歧义怕的是技能名里本身带逗号比如项目管理, 敏捷这种写法。所以我的经验是展示用分隔符可以随意逗号顿号都行。需要程序再拆开用的优先选业务数据里几乎不可能出现的分隔符比如|、#、;。更稳妥的做法是直接返回array_agg的数组让下游用数组 API 处理从根上避免解析问题。3.5 一个容易忽视的性能隐患字符串拼接无上限聚合时每行值都会追加到目标缓冲区内。数据量大时内存占用会显著上升尤其是当你要拼接的是长文本时。比如把某篇文章的所有评论内容拼成一个字段这是典型的大字段聚合操作稍不留神就能把数据库节点的内存吃紧。实践中如果遇到这种单组数据极大的场景我倾向于把它从 OLTP 主链路里挪出来要么用定时任务在夜间预处理结果存到单独的汇总表要么在应用层做流式拼接不要把所有压力倒给数据库。这一点在 Pg 16、17 上依然适用优化器再强大也解决不了内存物理边界的问题。4. 进阶组合与 JSON 生态从行集合走向结构化输出基础用法掌握之后再往上走一步就是组合玩法。真正让这两个函数在生产环境值回票价的是它们和其他语法特性、JSON 工具配合起来的威力。4.1 用 FILTER 做条件聚合一个分组输出多套拼接结果FILTER是 SQL 标准里被 PostgreSQL 认真实现的一个子句它的作用是在聚合函数内部按条件过滤参与聚合的行。有了它你就不需要写多个子查询来分别做条件聚合了。实际业务中我经常用它做同类汇总、按条件分列的需求。比如按部门统计两套名单——出勤的员工名单和缺勤的员工名单SELECT department_id, string_agg(employee_name, , FILTER (WHERE is_present)) AS present_list, string_agg(employee_name, , FILTER (WHERE NOT is_present)) AS absent_list FROM attendance_records GROUP BY department_id;一条 SQL、一个分组维度把两张表格的内容合并到了两个字段里。没有FILTER的写法是什么样你得把attendance_records表按条件拆成两个子查询分别聚合再 join 回来SQL 长一倍性能还更差。这个语法在 PostgreSQL 9.4 之后就一直很稳定16、17 系列用起来没有任何问题我强烈建议所有开发同学把它当成常规武器。4.2 与 json_agg/jsob_agg 的配合直接把多行变成 JSON 数组如果你的项目后端用的是 JSON 交互那么array_agg之后往往还要再做一步 JSON 化。其实 PostgreSQL 早就准备好了json_agg()和jsonb_agg()它们同样属于多行转一行家族只不过输出的是 JSON 数组SELECT category_id, json_agg( json_build_object(title, title, views, views) ORDER BY views DESC ) AS posts_json FROM posts GROUP BY category_id;结果长这样[ {title: PostgreSQL 优化实践, views: 1000}, {title: ClickHouse 踩坑记, views: 800} ]前端拿到这个字段直接就能用不需要在服务端再组装一次结构。很多时候string_agg搞不定的结构化输出问题json_agg是更好的答案——这也提醒我们选函数不能只看标题里那两个PostgreSQL 的聚合函数家族远比想象中丰富。4.3 让聚合函数变成窗口函数逐行保留并附带全组拼接结果聚合函数在 PostgreSQL 里不只能配合GROUP BY还经常被用在窗口函数模式下。这时不会把多行合并成一行而是每一行仍然保留只是每个分组的聚合结果被计算出来并附在这一行旁边。比如我要给每个明细行都带上整个订单项的标签总和SELECT order_id, item_name, string_agg(item_name, ,) OVER (PARTITION BY order_id) AS order_items FROM order_items;这在做导出报表时非常方便每一行都有独立的意义但同时也携带了组内的汇总信息不需要再额外 join 一次聚合子查询。不过要注意窗口模式下的聚合不会减少行数如果数据总量很大输出结果集中重复信息会非常多存储和传输成本要提前想清楚。我的原则是报表导出这种一次性低频率场景随便用高并发接口里尽量少碰宁可多写一次子查询让数据提前收敛。4.4 组合使用场景array_agg 与 unnest 实现行转列再转行有时你还会碰到这样的需求要把一个明细表按任意顺序重新展开成多行。这听起来和聚合是反方向的操作但 PostgreSQL 里可以用array_agg unnest两头接起来SELECT order_id, unnest( array_agg(item_name ORDER BY created_at) ) AS ordered_item FROM order_items GROUP BY order_id;先聚合保住组内顺序再展开回多行。这种写法在不支持WITHIN GROUP排序的旧 PostgreSQL 版本里是实现组内重排的经典方案。虽然 PostgreSQL 现在对数组的排序支持已经够好但理解这个套路依然能帮助你理解数据库内部行、列、数组三种形态之间来回转换的逻辑。写在最后的个人体会我从 PostgreSQL 9.x 时代开始用这两个函数到现在看到 16、17 普及语法一直非常稳定也没出现过兼容性断档。每年带新人时我都会把array_agg和string_agg作为SQL 思维进阶的第一课因为它们是少有的几个让我明显感觉到哦SQL 不只是查行查列还能重新组织数据形态的函数。最后再多说一个小技巧当你去网上搜string_agg时可能会看到group_concat这种来自 MySQL 的写法。PostgreSQL 里没有group_concat对应的就是string_agg。如果你在转语言或者换数据库第一件事就是记住 API 名字的差异别在报错界面才想起这茬。毕竟排错五分钟命名不同一小时这种亏我吃过太多次了。