STRING_AGG / ARRAY_AGG / JSONB_AGG
count / sum 把多行压成一个数，而集合聚合（Collection Aggregate）把多行压成一个集合值：数组、字符串或 JSON 数组。它们是把「一对多」关系折叠进单列、直接喂给前端的关键手段。但这里有两个几乎人人都踩过的坑：聚合结果的顺序默认不确定（外层 ORDER BY 管不到聚合内部），以及各函数对 NULL 的处理并不一致——string_agg 跳过 NULL，array_agg 和 jsonb_agg 却会把 NULL 原样保留。本节把选型、保序、去重与嵌套讲透。
前置知识
阅读本节前，建议先了解：COUNT / SUM / AVG / MAX / MIN
基础概念
| 概念 | 术语 | 说明 |
|---|---|---|
| 集合聚合 | Collection Aggregate | 把多行折叠为数组 / 字符串 / JSON 的聚合函数 |
| 折叠 | Fold | 多行 → 单个集合值的过程 |
| 保序 | Order Preservation | 聚合内要确定性顺序，必须写 ORDER BY |
| 聚合内排序 | Aggregate ORDER BY | 写在函数括号内、参数之后的排序子句 |
| 嵌套构造 | Nested Build | jsonb_agg(jsonb_build_object(...)) 组装对象数组 |
| 展开 | Unnest | 用 unnest() 把数组还原成多行 |
| 集合聚合内存 | work_mem | 折叠大结果集时的内存上限，超出会落盘 |
| XML 聚合 | xmlagg | 把多行拼成 XML 片段序列 |
方向对照：本节讲的全是「多行 → 单值」，与展开类函数方向相反。
| 函数 | 输出类型 | 典型用途 | NULL 是否保留 |
|---|---|---|---|
string_agg(expr, delim) | text / bytea | 商品名拼成一行 | 跳过 |
array_agg(expr) | anyarray | 折叠成数组供后续集合运算 | 保留 |
json_agg(expr) | json | 组内明细序列化 | 保留（输出 JSON null） |
jsonb_agg(expr) | jsonb | 同上，可索引、可比较 | 保留（输出 JSON null） |
json_object_agg(k, v) | json | 折叠成键值对象 | 值为 NULL → JSON null |
jsonb_object_agg(k, v) | jsonb | 同上，二进制形态 | 值为 NULL → JSON null |
xmlagg(expr) | xml | 拼 XML 报表 | 保留 |
折叠方向示意：
行 1 ─┐
行 2 ─┼─ array_agg ─▶ {a,b,c}
行 3 ─┘
行 1 ─┐
行 2 ─┼─ string_agg ─▶ a,b,c
行 3 ─┘
行 1 ─┐
行 2 ─┼─ jsonb_agg ─▶ [{"no":"A"},{"no":"B"}]
行 3 ─┘一句话记忆：要拼接文本用 string_agg（顺带丢 NULL）；要保留结构化数据用 jsonb_agg（NULL 变 null）；要后续做集合运算用 array_agg（也保留 NULL）。
语法与用法
-- 1) 字符串聚合
string_agg(value text, delimiter text [ORDER BY ...]) → text
string_agg(value bytea, delimiter bytea [ORDER BY ...]) → bytea
-- 2) 数组聚合
array_agg(expression anynonarray [ORDER BY ...]) → anyarray
array_agg(expression anyarray [ORDER BY ...]) → anyarray -- 数组的数组
-- 3) JSON 聚合
json_agg(expression anyelement [ORDER BY ...]) → json
jsonb_agg(expression anyelement [ORDER BY ...]) → jsonb
json_object_agg(key anyelement, value anyelement [ORDER BY ...]) → json
jsonb_object_agg(key anyelement, value anyelement [ORDER BY ...]) → jsonb
-- 4) XML 聚合
xmlagg(expression xml [ORDER BY ...]) → xml
-- 5) 通用的「只聚合部分行」修饰（与集合聚合正交）
string_agg(value, ',' ORDER BY ...) FILTER (WHERE 条件)注意 string_agg 的分隔符是必填参数（不像 MySQL 的 group_concat 有默认值），且第一个参数必须是 text / bytea，数字要显式 ::text。
准备本节示例数据：
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no TEXT NOT NULL UNIQUE,
customer TEXT NOT NULL,
channel TEXT, -- 允许 NULL：线下导入的单据没有渠道
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
sku TEXT NOT NULL,
name TEXT, -- 允许 NULL：尚未补录品名
qty INTEGER NOT NULL CHECK (qty > 0),
price NUMERIC(10, 2) NOT NULL
);
INSERT INTO orders (order_no, customer, channel, created_at) VALUES
('SO-1001', 'alice', 'app', TIMESTAMPTZ '2026-02-01 09:00:00+08'),
('SO-1002', 'bob', 'web', TIMESTAMPTZ '2026-02-02 10:30:00+08'),
('SO-1003', 'carol', NULL, TIMESTAMPTZ '2026-02-03 14:20:00+08');
INSERT INTO order_items (order_id, sku, name, qty, price) VALUES
(1, 'CN-ELEC-0001', '无线鼠标', 1, 89.00),
(1, 'CN-ELEC-0002', '机械键盘', 2, 399.00),
(1, 'CN-HOME-0003', NULL, 1, 25.50),
(2, 'US-HOME-0004', '收纳箱', 4, 25.50),
(2, 'CN-ELEC-0005', '显示器', 1, 899.00),
(3, 'CN-BOOK-0006', 'SQL 进阶', 3, 68.00);最小可运行示例：
SELECT
o.order_no,
string_agg(i.name, ', ') AS 品名串,
array_agg(i.sku) AS SKU数组,
jsonb_agg(i.sku) AS SKU的JSON,
count(*) AS 明细数
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.order_no
ORDER BY o.order_no; order_no | 品名串 | SKU数组 | SKU的JSON
----------+-----------------+--------------------------------------------+--------------------------------------------
SO-1001 | 无线鼠标, 机械键盘 | {CN-ELEC-0001,CN-ELEC-0002,CN-HOME-0003} | ["CN-ELEC-0001", "CN-ELEC-0002", "CN-HOME-0003"]
SO-1002 | 显示器, 收纳箱 | {US-HOME-0004,CN-ELEC-0005} | ["US-HOME-0004", "CN-ELEC-0005"]
SO-1003 | SQL 进阶 | {CN-BOOK-0006} | ["CN-BOOK-0006"]
(3 rows)注意 SO-1001 的 品名串：三条明细里有一条 name IS NULL，string_agg 把它丢掉了，拼接结果只剩两个品名，且分隔符只有一处——这正是「跳过 NULL」而非「当作空串占位」的直接证据（若当空串，会得到 无线鼠标, 机械键盘, 这样带尾随分隔符的畸形结果）。对照 SKU数组 仍是三个元素，NULL 被保留了。
详细说明
聚合内的 ORDER BY 是唯一保序手段
集合聚合的输入顺序默认不确定。原因有三：并行扫描会打乱行的到达顺序、HashAggregate 的哈希桶顺序不稳定、更新历史会让同一查询在不同时刻拿到不同顺序。
唯一可靠的保序方式是把 ORDER BY 写进聚合函数的参数列表：
-- 正确：ORDER BY 写在聚合括号内
SELECT
order_no,
array_agg(sku ORDER BY sku) AS 按SKU排,
string_agg(sku, ',' ORDER BY sku DESC) AS 倒序拼接
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY order_no
ORDER BY order_no; order_no | 按SKU排 | 倒序拼接
----------+------------------------------------------+----------------------------------------
SO-1001 | {CN-ELEC-0001,CN-ELEC-0002,CN-HOME-0003} | CN-HOME-0003,CN-ELEC-0002,CN-ELEC-0001
SO-1002 | {CN-ELEC-0005,US-HOME-0004} | US-HOME-0004,CN-ELEC-0005
SO-1003 | {CN-BOOK-0006} | CN-BOOK-0006
(3 rows)外层 ORDER BY 完全管不到聚合内部，这是最容易误判的一点：
-- 错误认知：以为外层排序会影响聚合内的拼接顺序
SELECT
order_no,
string_agg(sku, ',') AS 拼接
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY order_no
ORDER BY order_no; -- 这行只排「结果行」，与拼接顺序无关 order_no | 拼接
----------+--------------------------------
SO-1001 | CN-ELEC-0001,CN-ELEC-0002,CN-HOME-0003
SO-1002 | US-HOME-0004,CN-ELEC-0005
SO-1003 | CN-BOOK-0006
(3 rows)这里的顺序看着对，但只是巧合——它恰好等于物理插入顺序。一旦表变大、走上并行计划或发生 VACUUM FULL 重排，顺序就会变。要区分两者：
| 写法位置 | 作用对象 | 能否决定聚合内顺序 |
|---|---|---|
聚合参数内 agg(x ORDER BY y) | 聚合的输入流 | 能 |
语句末尾 ORDER BY y | 最终结果行 | 不能 |
有序集聚合 WITHIN GROUP (ORDER BY y) | 分位数 / 众数等 | 能（但语法位置不同） |
三类
ORDER BY的语法位置辨析与报错对照，见 ORDER BY 在聚合中。
外层 ORDER BY 无效的直观证明
用一张只有 4 行的临时表就能看出「外层排序不会重排聚合输入」：
WITH t(pos, v) AS (
VALUES (3, 'c'), (1, 'a'), (4, 'd'), (2, 'b')
)
SELECT
string_agg(v, ',') AS 无内部排序, -- 顺序=扫描顺序，不确定
string_agg(v, ',' ORDER BY pos) AS 按pos排序, -- c 一定在 d 前
string_agg(v, ',' ORDER BY v) AS 按值排序
FROM t
ORDER BY 1; -- 外层排序，对上面三列无效 无内部排序 | 按pos排序 | 按值排序
------------+-----------+----------
c,a,d,b | c,a,d,b | a,b,c,d第一列的输出与扫描顺序一致，纯属当前物理布局的结果，换一次 UPDATE 就可能变成 a,d,c,b。
DISTINCT 与 ORDER BY 必须指向同一表达式
去重聚合有一个硬性约束：DISTINCT 与聚合内 ORDER BY 同时出现时，排序表达式必须出现在聚合的参数列表里，否则报错。
-- 错误！排序键 created_at 不在 string_agg 的参数里
SELECT string_agg(DISTINCT tag, ',' ORDER BY created_at)
FROM order_tags;ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
LINE 1: SELECT string_agg(DISTINCT tag, ',' ORDER BY created_at)
^原因是语义上无法成立：DISTINCT 已经把行压成「不同的 tag」，created_at 在这些去重后的行上不再有唯一对应值，排序键失去意义。
-- 正确做法一：排序键就是被聚合的值本身
SELECT string_agg(DISTINCT tag, ',' ORDER BY tag) AS 去重有序
FROM (VALUES ('vip'), ('new'), ('vip'), ('hot')) AS t(tag); 去重有序
-------------
hot,new,vip
(1 row)-- 正确做法二：先在外层去重，再聚合
WITH uniq AS (
SELECT DISTINCT ON (tag) tag, created_at
FROM order_tags
ORDER BY tag, created_at
)
SELECT string_agg(tag, ',' ORDER BY created_at) AS 按首次出现拼接
FROM uniq;数组聚合同理，array_agg(DISTINCT x ORDER BY y) 一样要求 y 在参数中：
-- 多列去重：把「值」写成行构造器，排序键也用同一个构造器
SELECT array_agg(DISTINCT (sku, qty) ORDER BY (sku, qty)) AS 去重明细
FROM order_items;NULL 处理：三种函数三种行为
这是本节最需要在代码评审里盯住的地方：
WITH t(v) AS (VALUES ('a'), (NULL), ('b'))
SELECT
string_agg(v, ',') AS string_agg结果, -- a,b
array_agg(v) AS array_agg结果, -- {a,NULL,b}
array_to_string(array_agg(v), ',') AS 数组转串_跳NULL, -- a,b
array_to_string(array_agg(v), ',', '[空]') AS 数组转串_占位, -- a,[空],b
jsonb_agg(v) AS jsonb_agg结果 -- ["a", null, "b"]
FROM t; string_agg结果 | array_agg结果 | 数组转串_跳NULL | 数组转串_占位 | jsonb_agg结果
----------------+--------------------+-----------------+---------------+-----------------------
a,b | {a,NULL,b} | a,b | a,[空],b | ["a", null, "b"]
(1 row)| 函数 | NULL 行为 | 后果 |
|---|---|---|
string_agg | 跳过 | 拼接结果里没有占位，元素个数少于行数 |
array_agg | 保留 NULL 元素 | 数组长度 = 行数，但含 NULL |
array_to_string(a, d) | 跳过 NULL | 与 string_agg 表面一致 |
array_to_string(a, d, 'X') | 用 'X' 替换 NULL | 明确占位 |
jsonb_agg / json_agg | 保留为 JSON null | 前端要处理 null 元素 |
xmlagg | 保留 | NULL 会变成空内容 |
string_agg 无法通过参数跳过或保留 NULL
string_agg 天生丢弃 NULL，没有 null_string 参数。若业务需要「未补录品名显示为『待补』」，只能先改写表达式：
-- 把 NULL 转成占位文本再聚合
string_agg(coalesce(name, '待补'), ', ' ORDER BY sku)反过来，若要「结果里不要 NULL」，对数组聚合必须显式过滤，因为 array_agg 不会替你跳过：
SELECT
array_agg(sku) AS 含NULL,
array_agg(sku) FILTER (WHERE sku IS NOT NULL) AS 已过滤,
array_remove(array_agg(sku), NULL) AS 事后移除
FROM order_items;嵌套构造：jsonb_agg(jsonb_build_object(...))
把「订单 + 明细」结构化成前端可直接消费的嵌套 JSON，是这个函数组合最经典的用法：
SELECT
o.order_no,
o.customer,
jsonb_agg(
jsonb_build_object(
'sku', i.sku,
'name', i.name, -- 可能是 null
'qty', i.qty,
'price', i.price,
'amount', round(i.qty * i.price, 2)
)
ORDER BY i.sku
) AS items,
sum(i.qty * i.price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.order_no, o.customer
ORDER BY o.order_no; order_no | customer | items | total
----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+---------
SO-1001 | alice | [{"qty": 1, "sku": "CN-ELEC-0001", "name": "无线鼠标", "price": 89.00, "amount": 89.00}, {"qty": 2, "sku": "CN-ELEC-0002", "name": "机械键盘", "price": 399.00, "amount": 798.00}, {"qty": 1, "sku": "CN-HOME-0003", "name": null, "price": 25.50, "amount": 25.50}] | 912.50
SO-1002 | bob | [{"qty": 1, "sku": "CN-ELEC-0005", "name": "显示器", "price": 899.00, "amount": 899.00}, {"qty": 4, "sku": "US-HOME-0004", "name": "收纳箱", "price": 25.50, "amount": 102.00}] | 1001.00
SO-1003 | carol | [{"qty": 3, "sku": "CN-BOOK-0006", "name": "SQL 进阶", "price": 68.00, "amount": 204.00}] | 204.00
(3 rows)要点：
jsonb_build_object的参数是交替的键值对，键必须是文本，值可以是任意类型；键的书写顺序不影响结果（jsonb会按长度与字节序重排键）。ORDER BY写在jsonb_agg(...)的括号内，ORDER BY i.sku必须是聚合参数里的表达式或分组键——上例中i.sku是聚合参数的一层，PostgreSQL 允许在ORDER BY中引用输入行上的列。GROUP BY o.id带上主键即可，o.order_no/o.customer可由主键函数依赖推出（PostgreSQL 9.1+ 的函数依赖分组）。
若确定不需要 jsonb 的运算能力（只做展示），用 json_agg 更省一次解析开销；反之，要往 JSON 里做 @> 包含查询就必须用 jsonb_agg。
json_object_agg / jsonb_object_agg：折叠成键值对象
这两个函数把「两列」折叠成一个 JSON 对象，适合做「码值 → 名称」或「指标名 → 指标值」的字典：
SELECT jsonb_object_agg(channel, cnt) AS 渠道分布
FROM (
SELECT coalesce(channel, 'unknown') AS channel, count(*) AS cnt
FROM orders
GROUP BY 1
) s; 渠道分布
--------------------------------------------
{"app": 1, "web": 1, "unknown": 1}
(1 row)键的唯一性是调用方的责任——重复键不会报错，后者覆盖前者：
SELECT jsonb_object_agg(k, v) AS 结果
FROM (VALUES ('a', 1), ('a', 2)) AS t(k, v); 结果
-------------
{"a": 2}
(1 row)这与
jsonb_object_agg_unique（需要自己写聚合或用DISTINCT预处理）不同，标准函数就是「静默覆盖」。要防止覆盖，先在子查询里按k去重。
另一个限制：键不能为 NULL，会直接报错：
SELECT jsonb_object_agg(k, v) FROM (VALUES (NULL, 1)) AS t(k, v);
-- ERROR: field name must not be nullxmlagg
xmlagg 拼出来的是 XML 片段序列，常用于导出报表或与遗留系统对接：
SELECT
xmlelement(name orders,
xmlagg(xmlelement(name item, i.sku))
) AS xml_doc
FROM order_items i; xml_doc
--------------------------------------------------------------------------------
<orders><item>CN-ELEC-0001</item><item>CN-ELEC-0002</item>...</orders>
(1 row)XML 类型在没有 XMLOPTION 特殊设置时保留空白，且没有原生排序保证，同样需要 ORDER BY：
xmlagg(xmlelement(name item, sku) ORDER BY sku)用 unnest 把集合还原成多行
折叠的逆操作是展开。数组可以用 unnest() 直接变回行，这是「临时把列当表用」的核心技巧：
-- 还原：把 SO-1001 的 SKU 数组展开回多行
SELECT o.order_no, u.sku
FROM (
SELECT order_no, array_agg(sku ORDER BY sku) AS skus
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY order_no
) o
CROSS JOIN LATERAL unnest(o.skus) AS u(sku)
WHERE o.order_no = 'SO-1001'; order_no | sku
----------+---------------
SO-1001 | CN-ELEC-0001
SO-1001 | CN-ELEC-0002
SO-1001 | CN-HOME-0003
(3 rows)JSON 数组则要先用 jsonb_array_elements 拆开：
SELECT o.order_no, e.value ->> 'sku' AS sku, (e.value ->> 'qty')::INT AS qty
FROM (
SELECT order_no, jsonb_agg(jsonb_build_object('sku', sku, 'qty', qty) ORDER BY sku) AS items
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY order_no
) o
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS e(value)
ORDER BY o.order_no, sku;
string_agg无法无损还原：一旦某个值本身含分隔符（如品名里的逗号），拆分就会错位。需要往返转换的场景，一律用数组或 JSON。
性能与 work_mem
集合聚合必须把整个组的中间结果留在内存里——这与 sum / count 只需一个累加器完全不同：
| 聚合 | 内存占用 | 落盘 |
|---|---|---|
count / sum / avg | O(1) | 不落盘 |
string_agg / array_agg / jsonb_agg | O(组内行数 × 单行大小) | 超出 work_mem 时由 HashAggregate / Sort 溢出到临时文件 |
组特别大时（例如「把某客户 5 年订单全部拼成一个数组」），应：
- 提高会话级
work_mem：SET LOCAL work_mem = '128MB'; - 拆分聚合粒度，先按天/按订单折叠，再在外层二次聚合；
- 只取需要的列（
jsonb_build_object里少放字段），避免单行过大。
-- 观察聚合是否落盘：注意 HashAggregate 的 "Disk Usage" 行
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, jsonb_agg(jsonb_build_object('sku', sku, 'qty', qty)) FROM order_items GROUP BY order_id; QUERY PLAN
-------------------------------------------------------------------------------
HashAggregate (cost=1.20..1.30 rows=3 width=32) (actual time=0.045..0.048 rows=3 loops=1)
Group Key: order_id
Batches: 1 Memory Usage: 24kB
-> Seq Scan on order_items (cost=0.00..1.06 rows=6 width=36)小数据量下
Memory Usage只有几十 kB，绝不会落盘。真正需要关注Disk Usage的是单组上万行、单行又很大的场景。另外，work_mem是每个排序/哈希操作的上限，不是整个查询的总量，并发高时不要盲目调大。
实战示例
场景：给订单详情接口产出一行「订单 + 嵌套明细 + 品名摘要」的 JSON，要求顺序稳定、NULL 可控。
第 1 步：拼接商品清单，专门处理 NULL 品名
SELECT
o.order_no,
string_agg(coalesce(i.name, '【待补录】'), ' / ' ORDER BY i.sku) AS 品名摘要,
count(*) AS 明细数,
count(i.name) AS 已补录数
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.order_no
ORDER BY o.order_no; order_no | 品名摘要 | 明细数 | 已补录数
----------+------------------------------------+--------+----------
SO-1001 | 无线鼠标 / 机械键盘 / 【待补录】 | 3 | 2
SO-1002 | 显示器 / 收纳箱 | 2 | 2
SO-1003 | SQL 进阶 | 1 | 1
(3 rows)count(*) 与 count(i.name) 之差（3 vs 2）直接量化了「待补录」的条目数，报表里可以据此告警。
第 2 步：每单产出嵌套 JSON
SELECT
jsonb_build_object(
'order_no', o.order_no,
'customer', o.customer,
'channel', o.channel,
'item_count', count(*),
'total', sum(i.qty * i.price),
'items', jsonb_agg(
jsonb_build_object('sku', i.sku, 'name', i.name, 'qty', i.qty, 'price', i.price)
ORDER BY i.sku
)
) AS payload
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.order_no, o.customer, o.channel
ORDER BY o.order_no
LIMIT 1; payload
---------------------------------------------------------------------------------------------------------------
{"total": 912.50, "items": [{"qty": 1, "sku": "CN-ELEC-0001", "name": "无线鼠标", "price": 89.00}, ...], "channel": "app", "customer": "alice", "order_no": "SO-1001", "item_count": 3}
(1 row)把
jsonb_build_object放在聚合之外，得到的就是「每单一个完整对象」；如果放进去（如前一节示例），得到的是「对象数组」。两种嵌套层次要按接口约定选。
第 3 步：把渠道分布折叠成字典
SELECT jsonb_object_agg(channel, jsonb_build_object('orders', cnt, 'amount', amt)) AS 渠道看板
FROM (
SELECT
coalesce(o.channel, 'unknown') AS channel,
count(DISTINCT o.id) AS cnt,
sum(i.qty * i.price) AS amt
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY 1
) s; 渠道看板
----------------------------------------------------------------------------------------------------
{"app": {"amount": 912.50, "orders": 1}, "web": {"amount": 1001.00, "orders": 1}, "unknown": {"amount": 204.00, "orders": 1}}
(1 row)第 4 步：数组去重 + 排序 + 展开验证
-- 把每个 SKU 前缀（前两段）去重后折叠
WITH prefixes AS (
SELECT order_id, split_part(sku, '-', 1) || '-' || split_part(sku, '-', 2) AS prefix
FROM order_items
)
SELECT
o.order_no,
array_agg(DISTINCT p.prefix ORDER BY p.prefix) AS 前缀去重
FROM orders o JOIN prefixes p ON p.order_id = o.id
GROUP BY o.order_no
ORDER BY o.order_no; order_no | 前缀去重
----------+------------------
SO-1001 | {CN-ELEC,CN-HOME}
SO-1002 | {CN-ELEC,US-HOME}
SO-1003 | {CN-BOOK}
(3 rows)展开验证一下折叠结果与明细一致：
WITH folded AS (
SELECT order_id, array_agg(DISTINCT split_part(sku, '-', 1) || '-' || split_part(sku, '-', 2) ORDER BY 1) AS pf
FROM order_items GROUP BY order_id
)
SELECT o.order_no, count(*) AS 去重前缀数
FROM orders o JOIN folded f ON f.order_id = o.id
CROSS JOIN LATERAL unnest(f.pf) AS u(prefix)
GROUP BY o.order_no
ORDER BY o.order_no; order_no | 去重前缀数
----------+------------
SO-1001 | 2
SO-1002 | 2
SO-1003 | 1
(3 rows)第 5 步：对照 string_agg 与 array_to_string 的 NULL 差异
SELECT
string_agg(i.name, '|' ORDER BY i.sku) AS string_agg_跳NULL,
array_to_string(array_agg(i.name ORDER BY i.sku), '|') AS 数组转串_跳NULL,
array_to_string(array_agg(i.name ORDER BY i.sku), '|', '∅') AS 数组转串_占位
FROM order_items i
WHERE i.order_id = 1; string_agg_跳NULL | 数组转串_跳NULL | 数组转串_占位
-------------------+-----------------+----------------
无线鼠标|机械键盘 | 无线鼠标|机械键盘 | 无线鼠标|机械键盘|∅
(1 row)结论很明确：默认两条路都会丢掉 NULL，想要占位就必须用三参数的 array_to_string，或者聚合前 coalesce。
第 6 步：清理
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;注意事项
聚合内
ORDER BY是保序的唯一手段。外层ORDER BY排的是结果行，与聚合输入顺序无关，且「碰巧正确」不可依赖：sql-- 不可靠 string_agg(x, ',') ... ORDER BY y -- 可靠 string_agg(x, ',' ORDER BY y)string_agg的第二个参数分隔符必填，且分隔符本身不会被转义：sql-- 错误：缺少分隔符 SELECT string_agg(name) FROM order_items; -- ERROR: function string_agg(text) does not existstring_agg只接受text/bytea，数字必须先转换：sql-- 错误 SELECT string_agg(qty, ',') FROM order_items; -- 正确 SELECT string_agg(qty::text, ',') FROM order_items;array_agg与jsonb_agg保留 NULL，string_agg跳过 NULL。同一个报表里混用这两类函数会出现「元素个数对不上」的诡异现象。DISTINCT与聚合内ORDER BY必须同源，排序键必须出现在聚合参数中：sql-- 报错 string_agg(DISTINCT tag, ',' ORDER BY created_at) -- 可行 string_agg(DISTINCT tag, ',' ORDER BY tag)array_agg(DISTINCT a ORDER BY b)中b也必须在参数里；多列去重请用行构造器array_agg(DISTINCT (a, b) ORDER BY (a, b))。jsonb_object_agg的重复键静默覆盖，键为 NULL 直接报错。折叠前先确认键唯一且非空。jsonb_build_object的键顺序不保留（jsonb会重排键）。需要保留原始键序时用json_build_object+json_agg，代价是失去jsonb的索引与运算符能力。string_agg的结果无法安全还原。值里含分隔符时会错位，需要往返转换必须用数组或 JSON。GROUP BY与集合聚合组合时，ORDER BY里只能引用分组键或聚合参数中的列，引用未分组的其他列会报column must appear in the GROUP BY clause。大组折叠会让
work_mem溢出到临时文件，EXPLAIN (ANALYZE, BUFFERS)中若出现Disk Usage就要考虑降低聚合粒度或提高work_mem。array_agg折叠数组列会得到「数组的数组」，再unnest一次才能拉平：sqlSELECT unnest(array_agg(tags)) FROM t; -- 二维 → 一维元素流
最佳实践
只要顺序有必要，就给聚合写上
ORDER BY。哪怕当前数据碰巧有序，也把它显式化——顺序稳定才能被测试断言与缓存依赖：sqlarray_agg(sku ORDER BY sku)展示层拼接优先
string_agg，数据交换层用jsonb_agg。前者轻、可读；后者的结构可被前端直接解析，不会因为值里含逗号而歧义。NULL 语义在 SQL 里显式表态，不要依赖函数默认行为：
sql-- 想显示占位 string_agg(coalesce(name, '待补录'), ', ' ORDER BY sku) -- 想去掉 NULL array_agg(sku) FILTER (WHERE sku IS NOT NULL)嵌套 JSON 用
jsonb_build_object+jsonb_agg组合，并统一字段命名风格；把ORDER BY写在聚合内部保证数组顺序稳定：sqljsonb_agg(jsonb_build_object('sku', sku, 'qty', qty) ORDER BY sku)接口返回 JSON 时限制
jsonb_agg的字段与行数。一次返回上千条明细会把响应体和内存同时撑爆，超出阈值应改为「分页明细接口 + 摘要字段」。先聚合后展开，而不是先展开后聚合。
unnest在GROUP BY之后能精确控制行数；反过来会先把行数放大再折叠，代价成倍。需要「去重 + 有序」时优先在子查询里
DISTINCT，而不是在聚合里叠加DISTINCT与ORDER BY——后者对排序键有额外约束，可读性也差。对超大组的集合聚合做分级折叠：先按天/按订单折叠成较小的数组，再在外层
array_agg拼接，避免单组内存爆掉：sqlWITH daily AS ( SELECT user_id, day, array_agg(event ORDER BY ts) AS events FROM events GROUP BY user_id, day ) SELECT user_id, count(*) AS days, sum(cardinality(events)) AS total_events FROM daily GROUP BY user_id;array_agg的结果若还要做集合运算，加上array_remove/FILTER清理 NULL，否则@>、&&的结果会被 NULL 元素干扰。给拼接结果设定长度上限意识。
text没有硬上限，但一个几 MB 的字符串列会让网络传输、日志与前端渲染同时变慢；必要时用left(string_agg(...), 2000)截断。
下一节