PHP小码农

Appearance

ORDER BY 在聚合中 ​

sum / count 这类聚合对输入顺序无所谓，但 string_agg / array_agg / json_agg 的输出直接等于输入行的排列顺序。而 PostgreSQL 官方文档明确写着：聚合的输入行顺序默认是未定义的——它取决于执行计划的扫描顺序、并行度、甚至 work_mem 的大小。这意味着今天返回 "A, B, C" 的查询明天可能返回 "C, A, B"。本节的结论只有一条但必须牢记：凡是结果依赖行顺序的聚合，都必须显式写聚合内部的 ORDER BY。

前置知识

阅读本节前，建议先了解：FILTER 子句

基础概念 ​

概念术语说明
聚合内 ORDER BYAggregate Order By写在聚合参数括号内，决定聚合读取行的顺序
外层 ORDER BYQuery Order By写在整条 SELECT 末尾，决定输出行的顺序
未定义顺序Unspecified Order不写聚合内 ORDER BY 时 PostgreSQL 不保证任何顺序
排序敏感的聚合Order-Sensitive Aggregatestring_agg / array_agg / json_agg / jsonb_agg
排序无关的聚合Order-Insensitive Aggregatesum / avg / count / min / max / bool_and
有序集聚合Ordered-Set Aggregate用 WITHIN GROUP (ORDER BY ...) 语法，如 percentile_cont
反聚合函数Inverse Function窗口聚合复用排序结果时的逆运算，影响计划选择
排序溢出Sort Spill排序数据超出 work_mem 时落盘，是聚合内排序的主要代价来源

三种 ORDER BY 的位置与作用域完全不同，混淆它们是本节乃至整个 SQL 学习中最常见的困惑：

text
  SELECT department,
         string_agg(name, ', ' ORDER BY hire_date)     ← ① 聚合内部：决定拼接顺序
  FROM employees
  WHERE status = 'active'
  GROUP BY department
  ORDER BY department;                                 ← ② 查询末尾：决定输出行顺序
--  ↑ ①括号内、②语句末尾
位置语法作用对象决定什么
聚合参数括号内string_agg(x, ',' ORDER BY y)单个聚合该聚合读取行的顺序
WITHIN GROUP 内percentile_disc(0.5) WITHIN GROUP (ORDER BY x)有序集聚合排序后取位次，语法强制要求
OVER 括号内sum(x) OVER (ORDER BY y)窗口聚合帧滑动的顺序
语句末尾ORDER BY x结果集输出行的排列

哪些聚合受顺序影响：

聚合受聚合内 ORDER BY 影响吗说明
string_agg是输出字符串的拼接顺序
array_agg是输出数组的元素顺序
json_agg / jsonb_agg是输出 JSON 数组的元素顺序
xmlagg是输出 XML 的元素顺序
json_object_agg / jsonb_object_agg否键值对象无序；jsonb 会重排键
sum / avg / count否（sum 浮点除外）数值累加与顺序无关（float 累加有微小误差）
min / max / bool_and / bool_or否结果与顺序无关
stddev / variance / corr否统计量与顺序无关

最小可运行示例（连跑两次看到顺序不同）：

sql
-- 不写聚合内 ORDER BY：顺序由计划决定，不可依赖
SELECT string_agg(sku, ', ') AS 商品串
FROM (VALUES ('A001'), ('B007'), ('C010')) AS t(sku);
text
   商品串
-------------
 A001, B007, C010
(1 row)
sql
-- 显式写聚合内 ORDER BY：顺序由语法保证
SELECT string_agg(sku, ', ' ORDER BY sku) AS 商品串
FROM (VALUES ('C010'), ('A001'), ('B007')) AS t(sku);
text
   商品串
-------------
 A001, B007, C010
(1 row)

第二次的输入行顺序是乱的，但因为写了 ORDER BY sku，输出仍然是排好序的。这就是显式排序的价值：把「碰巧正确」变成「语法保证正确」。

贯穿本节的示例表：

sql
-- 示例库：demo
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT    NOT NULL,
  department  TEXT    NOT NULL,
  title       TEXT    NOT NULL,
  salary      NUMERIC(10,2) NOT NULL,
  hire_date   DATE    NOT NULL,
  status      TEXT    NOT NULL DEFAULT 'active'  -- active / left
);

INSERT INTO employees (name, department, title, salary, hire_date, status) VALUES
  ('张伟', '研发', '工程师',   18000.00, '2021-03-01', 'active'),
  ('李娜', '研发', '前端工程师', 16000.00, '2022-07-15', 'active'),
  ('王强', '研发', '研发经理',   28000.00, '2019-05-20', 'active'),
  ('刘婷', '市场', '市场专员',   12000.00, '2023-01-09', 'active'),
  ('陈杰', '市场', '市场总监',   25000.00, '2018-11-01', 'active'),
  ('赵敏', '财务', '会计',       14000.00, '2020-06-18', 'active'),
  ('孙磊', '财务', '财务经理',   22000.00, '2017-09-25', 'left');

语法与用法 ​

sql
-- 1) 单个排序键
string_agg(expr, 分隔符 ORDER BY 排序列 [ASC|DESC] [NULLS FIRST|NULLS LAST])
array_agg(expr ORDER BY 排序列 [ASC|DESC])
json_agg(expr ORDER BY 排序列)
jsonb_agg(expr ORDER BY 排序列)

-- 2) 多个排序键（逗号分隔，与普通 ORDER BY 语法一致）
array_agg(sku ORDER BY created_at DESC, id ASC)

-- 3) 同时带 DISTINCT（两者表达式必须一致，见下文）
string_agg(DISTINCT sku, ', ' ORDER BY sku)

-- 4) 与 FILTER 组合：ORDER BY 在括号内，FILTER 在括号外
string_agg(sku, ', ' ORDER BY id) FILTER (WHERE qty > 1)

-- 5) 排序表达式可以是任意表达式，不必出现在输出中
string_agg(name, ', ' ORDER BY salary DESC, id)   -- 按薪资从高到低点名

位置规则：ORDER BY 永远写在聚合函数的参数括号内部，紧跟在最后一个参数之后。写在括号外会被当成查询级排序，与聚合完全无关：

sql
-- 正确：聚合内排序
SELECT department, string_agg(name, ', ' ORDER BY hire_date) FROM employees GROUP BY department;

-- 错误理解：这里的 ORDER BY 是查询级排序，字符串的拼接顺序仍未定义
SELECT department, string_agg(name, ', ') FROM employees GROUP BY department ORDER BY department;

语法位置只有一条规则：ORDER BY 必须写在所有常规参数之后、参数括号之内。这是最容易写错的地方——把 ORDER BY 插在参数中间，PostgreSQL 不认识这种函数签名，会报「函数不存在」，并附上一条非常直接的 HINT：

sql
-- 错误：ORDER BY 插在了两个参数之间
SELECT string_agg(title ORDER BY title, ',') FROM employees;
text
ERROR:  function string_agg(text) does not exist
LINE 1: SELECT string_agg(title ORDER BY title, ',') FROM employees;
               ^
HINT:  No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate.

正确写法是让 ORDER BY 排在分隔符之后：

sql
SELECT string_agg(title, ',' ORDER BY title) FROM employees;

记住这个报错的特征：报「函数不存在」不一定是函数名写错了，很可能是 ORDER BY 放错了位置。看到 HINT 里出现 misplaced ORDER BY 就能直接定位。

适用性：哪些聚合真的需要聚合内 ORDER BY ​

聚合内 ORDER BY 在语法上对所有聚合都合法，但对可交换聚合毫无意义——sum(x ORDER BY x) 与 sum(x) 结果相同，反而白白付出一次排序代价。

sql
-- 语法允许，但没有任何意义
SELECT sum(salary ORDER BY salary)  AS 合法但无意义,
       count(salary ORDER BY salary) AS 同样无意义
FROM employees;
text
 合法但无意义 | 同样无意义
--------------+------------
    135000.00 |          7
(1 row)
聚合加聚合内 ORDER BY建议
string_agg / array_agg有实际意义，决定元素顺序必须写
json_agg / jsonb_agg / xmlagg有实际意义，决定数组/节点顺序必须写
sum / avg / min / max无意义（float 的 sum 有极小数值差异）不要写
count无意义不要写
bool_and / bool_or无意义不要写
stddev / variance / corr无意义不要写
percentile_cont / percentile_disc语法错误，必须用 WITHIN GROUP见下节
count(*)语法错误count(*) 没有参数，不能带 ORDER BY
sql
-- 错误：count(*) 不能带聚合内 ORDER BY
-- SELECT count(*) ORDER BY salary FROM employees;
-- ERROR:  syntax error at or near "FROM"

排序键本身可以是任意表达式，个数不限：

sql
-- 多个排序键（与普通 ORDER BY 一致）
string_agg(title, ', ' ORDER BY hire_date DESC, id ASC)

-- 第二个键是常量：合法，但退化成单键排序（常量不参与比较）
sum(salary ORDER BY hire_date, 1)

-- 排序键可以是任意表达式，不必出现在输出中
string_agg(name, ' > ' ORDER BY length(title), id)

聚合内 ORDER BY 1 不是「按第一列排序」

与查询级 ORDER BY 1（表示输出列序号）不同，聚合内的 ORDER BY 1 是常量表达式 1，它对所有行求值都相同，等于没有排序：

sql
SELECT string_agg(name, ',')               AS 无排序,
       string_agg(name, ',' ORDER BY 1)    AS 也等于无排序
FROM employees;
text
               无排序               |            也等于无排序
------------------------------------+------------------------------------
 张伟,李娜,王强,刘婷,陈杰,赵敏,孙磊 | 张伟,李娜,王强,刘婷,陈杰,赵敏,孙磊
(1 row)

两列完全一致。聚合内不认列序号，要排序必须写出真实的列或表达式名。

详细说明 ​

为什么必须显式 ORDER BY：顺序「未定义」意味着什么 ​

PostgreSQL 文档对聚合输入顺序的表述是 unspecified（未指定），而不是「按主键顺序」或「按插入顺序」。这不是文档的保守说法，而是真实可复现的现象：

sql
-- 同一份数据、同一条 SQL，改变计划就能改变输出顺序
EXPLAIN (COSTS OFF)
SELECT department, string_agg(name, ', ') FROM employees GROUP BY department;
text
 HashAggregate
   Group Key: department
   ->  Seq Scan on employees

HashAggregate 逐行喂给聚合，顺序就是 Seq Scan 的物理顺序。一旦优化器改选 GroupAggregate（例如加了索引、或数据量变化），顺序就变成索引顺序。开了并行查询后，多个 worker 各自聚合再合并，顺序更不可能一致。

四种会让顺序发生变化的常见诱因：

诱因顺序如何变化
计划从 HashAggregate 变为 GroupAggregate从物理扫描顺序变为索引顺序
表做过 VACUUM FULL / CLUSTER行的物理位置被重排
开启并行查询（max_parallel_workers_per_gather）worker 各自聚合后合并，顺序随机
数据量变化导致 work_mem 内排序改为外部排序归并段顺序改变

「碰巧正确」是最危险的正确

开发环境数据量小，Seq Scan 的顺序往往与插入顺序一致，于是不写 ORDER BY 的代码测试通过。上线后数据量变大、计划改变，字符串拼接顺序悄悄变了，而下游是「按逗号切分后取第一个」这类逻辑——bug 会以「数据错乱」的形式出现在完全无关的模块里，排查成本极高。

下面用一个最小例子把「计划决定顺序」这件事做实。表里三行的物理顺序与 seq 故意不一致：

sql
DROP TABLE IF EXISTS ev2;
CREATE TABLE ev2 (order_no TEXT, seq INT, code TEXT);

INSERT INTO ev2 VALUES
  ('SO-6001', 3, 'E3'), ('SO-6001', 1, 'E1'), ('SO-6001', 2, 'E2'),
  ('SO-6002', 2, 'E2'), ('SO-6002', 3, 'E3'), ('SO-6002', 1, 'E1');

CREATE INDEX idx_ev2 ON ev2 (order_no, seq);

计划一：HashAggregate + 顺序扫描（走物理顺序）：

sql
SET enable_hashagg = on;
SET enable_seqscan = on;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_no, string_agg(code, ',') FROM ev2 GROUP BY order_no;
text
 HashAggregate (actual time=0.016..0.017 rows=2.00 loops=1)
   Group Key: order_no
   Batches: 1  Memory Usage: 32kB
   Buffers: shared hit=1
   ->  Seq Scan on ev2 (actual time=0.005..0.005 rows=6.00 loops=1)
sql
SELECT order_no, string_agg(code, ',') AS 无排序
FROM ev2 GROUP BY order_no ORDER BY order_no;
text
 order_no |  无排序
----------+----------
 SO-6001  | E3,E1,E2
 SO-6002  | E2,E3,E1
(2 rows)

计划二：GroupAggregate + 索引扫描（走索引顺序）——数据一行没变，只是换了计划：

sql
SET enable_hashagg = off;
SET enable_seqscan = off;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_no, string_agg(code, ',') FROM ev2 GROUP BY order_no;
text
 GroupAggregate (actual time=0.040..0.042 rows=2.00 loops=1)
   Group Key: order_no
   Buffers: shared hit=1 read=1
   ->  Index Scan using idx_ev2 on ev2 (actual time=0.032..0.032 rows=6.00 loops=1)
sql
SELECT order_no, string_agg(code, ',') AS 无排序
FROM ev2 GROUP BY order_no ORDER BY order_no;
text
 order_no |  无排序
----------+----------
 SO-6001  | E1,E2,E3
 SO-6002  | E1,E2,E3
(2 rows)
计划扫描方式SO-6001 的输出
HashAggregateSeq Scan（物理顺序）E3,E1,E2
GroupAggregateIndex Scan（索引顺序）E1,E2,E3

加上聚合内 ORDER BY seq 后，两种计划给出完全一致的结果：

sql
-- 计划一：HashAggregate
SELECT order_no, string_agg(code, ',' ORDER BY seq) AS 有排序
FROM ev2 GROUP BY order_no ORDER BY order_no;
text
 order_no |  有排序
----------+----------
 SO-6001  | E1,E2,E3
 SO-6002  | E1,E2,E3
(2 rows)
sql
-- 计划二：GroupAggregate + Index Scan
SET enable_hashagg = off;
SET enable_seqscan = off;

SELECT order_no, string_agg(code, ',' ORDER BY seq) AS 有排序
FROM ev2 GROUP BY order_no ORDER BY order_no;
text
 order_no |  有排序
----------+----------
 SO-6001  | E1,E2,E3
 SO-6002  | E1,E2,E3
(2 rows)

这就是「语法保证」与「计划巧合」的区别：不写 ORDER BY 时结果取决于优化器当下的选择，写了的则永远一致。

排序键的选择：为什么常常需要「业务字段 + 主键」 ​

只写一个业务字段作为排序键，在取值重复时仍然是不确定的：

sql
-- created_at 相同的两行，谁在前？未定义
array_agg(sku ORDER BY created_at)
sql
-- 加上主键兜底，顺序唯一确定
array_agg(sku ORDER BY created_at, id)
排序键是否确定说明
ORDER BY created_at否同秒（或同日期）的行顺序不定
ORDER BY created_at, id是id 是主键，天然唯一
ORDER BY id是但语义是「插入顺序」，不是业务顺序
ORDER BY created_at DESC, id DESC是方向需保持一致

选择排序键的原则：先在业务上确定「顺序」的含义，再用一个唯一列兜底。常见搭配：

sql
string_agg(sku, ', ' ORDER BY sort_no, id)          -- 有显式排序号
jsonb_agg(row_to_json(t) ORDER BY score DESC, id)   -- 排行榜：分数降序，同分按 id
array_agg(name ORDER BY hire_date, id)              -- 入职先后

与 DISTINCT 共用的约束 ​

DISTINCT 与 ORDER BY 同时出现时，排序表达式必须出现在聚合参数里，否则报错：

sql
-- 正确：排序键就是参数本身
SELECT string_agg(DISTINCT title, ', ' ORDER BY title) FROM employees;

-- 正确：用表达式作为参数时，排序键写成同一个表达式
SELECT string_agg(DISTINCT title || ' x' || salary, ', ' ORDER BY title || ' x' || salary)
FROM employees;

-- 错误：排序键 hire_date 不在参数列表中
-- SELECT string_agg(DISTINCT title, ', ' ORDER BY hire_date) FROM employees;
-- ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

原因：去重后的行里，hire_date 没有唯一确定的取值（同一 title 可能有多条不同 hire_date 的记录），排序键无法定义。

绕过办法是先去重再聚合，用子查询把「每行一个值」和「排序键」都确定下来：

sql
SELECT string_agg(title, ', ' ORDER BY first_seen)
FROM (
  SELECT title, min(hire_date) AS first_seen
  FROM employees
  GROUP BY title
) s;
写法是否合法说明
string_agg(DISTINCT title, ',' ORDER BY title)合法排序键 = 参数
string_agg(DISTINCT title, ',' ORDER BY hire_date)报错排序键不在参数列表
string_agg(title, ',' ORDER BY hire_date)合法（无 DISTINCT）不去重时排序键自由

array_agg 同样如此，排序键与参数写成同一个表达式即可：

sql
-- 合法：排序键就是参数本身
SELECT array_agg(DISTINCT status ORDER BY status) FROM employees;

-- 合法：参数是行构造器（复合类型），排序键写成同一个行构造器
SELECT array_agg(DISTINCT (department, title) ORDER BY (department, title)) FROM employees;

排序键的三个细节：NULL 位置、排序规则、可引用的列 ​

① NULLS FIRST / NULLS LAST 在聚合内同样可用，且方向会影响默认行为——ASC 默认 NULLS LAST，DESC 默认 NULLS FIRST：

sql
SELECT
  string_agg(v::text, ',' ORDER BY v)             AS 升序默认,
  string_agg(v::text, ',' ORDER BY v NULLS FIRST) AS 升序Null在前,
  string_agg(v::text, ',' ORDER BY v DESC)        AS 降序默认
FROM (VALUES (2), (NULL), (1)) AS t(v);
text
 升序默认 | 升序Null在前 | 降序默认
----------+--------------+----------
 1,2      | 1,2          | 2,1

string_agg 会跳过 NULL，所以看不出差别；换成保留 NULL 的 array_agg 就一目了然：

sql
SELECT
  array_agg(v ORDER BY v)             AS 升序默认,     -- NULL 在最后
  array_agg(v ORDER BY v NULLS FIRST) AS 升序Null在前, -- NULL 在最前
  array_agg(v ORDER BY v DESC)        AS 降序默认     -- NULL 在最前
FROM (VALUES (2), (NULL), (1)) AS t(v);
text
 升序默认  | 升序Null在前 | 降序默认
-----------+--------------+-----------
 {1,2,NULL} | {NULL,1,2}   | {NULL,2,1}

② COLLATE 可以写在排序键上，用来覆盖数据库默认排序规则。中文数据的默认规则（zh_CN.UTF-8）按拼音序，C / POSIX 按字节序：

sql
SELECT string_agg(title, ',' ORDER BY title COLLATE "C") FROM employees;
text
 collate_c
-----------
 p,q,r

③ 聚合内 ORDER BY 可以引用未被分组的列，因为它是在组内对行排序，而不是在输出结果上排序。这一点正好是它和查询级 ORDER BY 的关键差异：

sql
-- 合法：id 既不在 SELECT 列表里，也不在 GROUP BY 里，但聚合内排序可以用它
SELECT s, string_agg(b, ',' ORDER BY id) AS 按 id 拼接
FROM ord GROUP BY s;
text
 s | 按 id 拼接
---+------------
 x | p,q
 y | r
(2 rows)
sql
-- 错误：查询级 ORDER BY 不能引用未分组的列
SELECT s, string_agg(b, ',') FROM ord GROUP BY s ORDER BY id;
text
ERROR:  column "ord.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT s, string_agg(b, ',') FROM ord GROUP BY s ORDER BY id;
位置能否引用未分组的列
聚合内 ORDER BY能（作用于组内的行）
查询级 ORDER BY不能（作用于输出的行）

聚合内 ORDER BY 与查询级 ORDER BY：两件事 ​

两者互不替代，一个常见的错误是以为「外面排了序，里面就跟着有序了」：

sql
-- 字符串拼接顺序由①决定；输出行的排列由②决定，与①无关
SELECT
  department,
  string_agg(name, ', ' ORDER BY hire_date) AS 按入职先后   -- ①
FROM employees
GROUP BY department
ORDER BY department;                                        -- ②
text
 department |        按入职先后
------------+--------------------------
 市场       | 陈杰, 刘婷
 研发       | 王强, 张伟, 李娜
 财务       | 孙磊, 赵敏
(3 rows)

GROUP BY 也会影响输出行顺序（HashAggregate 下无序），所以查询级 ORDER BY 同样要显式写：

sql
-- 没有查询级 ORDER BY 时，分组的输出顺序也是未定义的
SELECT department, count(*) FROM employees GROUP BY department;

GROUP BY 的输出顺序同样未定义

很多人以为 GROUP BY 会按分组键排序输出。在 HashAggregate 计划下不会。若报表要求「分组按名称排序」，末尾必须补 ORDER BY department，或者直接依赖 GroupAggregate（不推荐，它是计划细节而非语法保证）。

聚合内 ORDER BY 与 WITHIN GROUP 的区别 ​

WITHIN GROUP 是有序集聚合的专用语法，它的 ORDER BY 不是「可选的排序提示」，而是语义的一部分——分位数就是「排序后第几个值」：

sql
-- 有序集聚合：括号里是参数，排序写在 WITHIN GROUP 里
SELECT
  department,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) AS 中位薪资
FROM employees
GROUP BY department;
特性聚合内 ORDER BYWITHIN GROUP (ORDER BY ...)
适用函数string_agg / array_agg / json_agg 等percentile_cont / percentile_disc / mode
是否可有可无可省略（但顺序未定义）语法强制，不可省略
对结果的影响决定输出元素的顺序决定取第几个值，直接影响结果
语法位置参数括号内参数括号外，紧跟函数名之后
sql
-- 两者可以同时出现在一个查询里
SELECT
  department,
  string_agg(name, ', ' ORDER BY hire_date)                    AS 按入职顺序,   -- ①
  percentile_disc(0.5) WITHIN GROUP (ORDER BY salary)          AS 中位薪资      -- ②
FROM employees
GROUP BY department;

别把 WITHIN GROUP 写成聚合内 ORDER BY

percentile_disc(0.5 ORDER BY salary) 是语法错误。有序集聚合的排序必须写在 WITHIN GROUP (...) 里，这与 string_agg 的写法位置正好相反，也是抄错 SQL 的高发点。

聚合内 ORDER BY 与窗口 OVER (ORDER BY) 的区别 ​

窗口聚合的 ORDER BY 决定帧如何滑动，不是「聚合内部的读取顺序」：

sql
-- 窗口：按日期累积，第 3 行的结果是前 3 行的和
SELECT id, stat_date,
       sum(amount) OVER (ORDER BY stat_date, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计
FROM sales;
sql
-- 聚合：整体求和，与顺序无关；写成窗口则变成累积
SELECT sum(amount) FROM sales;                                    -- 一个总数
SELECT sum(amount) OVER (ORDER BY stat_date) FROM sales;          -- 每个日期一个累计数
场景正确写法
「把这组行的名字按时间拼成字符串」string_agg(name, ',' ORDER BY created_at)
「到目前为止的累计金额」sum(amount) OVER (ORDER BY created_at ROWS ...)
「把这组行按时间排序后取中位数」percentile_disc(0.5) WITHIN GROUP (ORDER BY created_at)

性能：聚合内排序的代价与优化 ​

聚合内 ORDER BY 意味着每个分组内部都要排序。PostgreSQL 有两种执行策略：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT department, string_agg(name, ', ' ORDER BY hire_date)
FROM employees
GROUP BY department;
text
 GroupAggregate
   Group Key: department
   ->  Sort
         Sort Key: department, hire_date
         ->  Seq Scan on employees

若数据量大、分组数多，Sort 节点可能溢出到磁盘：

text
         Sort Method: external merge  Disk: 49152kB
sql
-- 提高 work_mem 让排序留在内存（会话级，注意并发内存总量）
SET work_mem = '64MB';

更彻底的优化是让数据按排序键的顺序流入聚合，此时优化器用 GroupAggregate 直接顺序读取，完全省掉 Sort 节点：

sql
-- 索引前导列是分组键，第二列是聚合内排序键
CREATE INDEX idx_employees_dept_hired ON employees (department, hire_date);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT department, string_agg(name, ', ' ORDER BY hire_date)
FROM employees
GROUP BY department;
text
 GroupAggregate
   Group Key: department
   ->  Index Scan using idx_employees_dept_hired on employees

索引列顺序是「分组键 + 聚合排序键」

索引 (department, hire_date) 让同部门的行按 hire_date 有序到达，聚合无需再排序。若把两列顺序写反（(hire_date, department)），则退化为 Sort + GroupAggregate 或 HashAggregate + Sort，收益消失。多排序键时索引列要按同样的顺序与方向排列（(department, hire_date DESC, id)）。

方案计划形态代价
无索引HashAggregate（无序）或 Sort + GroupAggregate排序可能落盘
只建 (department)仍然需要组内排序无改善
建 (department, hire_date)GroupAggregate 直接读索引最优，零排序
提高 work_mem排序留在内存治标不治本，且占内存

并行查询与顺序 ​

开启并行后，聚合通常走「两阶段聚合」：每个 worker 先局部聚合，再由 leader 合并。合并这一步不保证任何顺序：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT string_agg(sku, ', ' ORDER BY id) FROM large_table;
text
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on large_table

Partial Aggregate 各算各的，Gather 收集后再 Finalize。若写了聚合内 ORDER BY，各 worker 需要先排序（或依赖有序扫描），Finalize 再归并，顺序才是确定的；不写时合并顺序取决于 worker 完成先后。

并行下不写 ORDER BY，结果可能「每次都不一样」

并行 worker 的调度受系统负载影响，同一查询连续执行两次可能返回不同顺序的字符串。这类「偶发不一致」在排查时非常难定位，而根因通常就是少写了一处 ORDER BY。

实战示例 ​

第 1 步：部门人员名单（按入职先后） ​

sql
SELECT
  department AS 部门,
  count(*)   AS 人数,
  string_agg(name, ', ' ORDER BY hire_date, id) AS 按入职先后
FROM employees
WHERE status = 'active'
GROUP BY department
ORDER BY department;
text
 部门 | 人数 |      按入职先后
------+------+----------------------
 市场 |    2 | 陈杰, 刘婷
 研发 |    3 | 王强, 张伟, 李娜
 财务 |    1 | 赵敏
(3 rows)

status = 'left' 的孙磊被 WHERE 排除，所以财务只剩赵敏。

第 2 步：薪资排行榜（JSON 数组 + 双键排序） ​

sql
SELECT
  department AS 部门,
  jsonb_agg(jsonb_build_object('姓名', name, '薪资', salary)
            ORDER BY salary DESC, id) AS 薪资排行
FROM employees
GROUP BY department
ORDER BY 部门;
text
                               薪资排行
--------------------------------------------------------------------------
 [{"姓名": "陈杰", "薪资": 25000.00}, {"姓名": "刘婷", "薪资": 12000.00}]
 [{"姓名": "王强", "薪资": 28000.00}, {"姓名": "张伟", "薪资": 18000.00}, {"姓名": "李娜", "薪资": 16000.00}]
 [{"姓名": "孙磊", "薪资": 22000.00}, {"姓名": "赵敏", "薪资": 14000.00}]

排序键 salary DESC, id 中，id 用于薪资相同时的兜底。注意 jsonb 会重排键的显示顺序（姓名 在 薪资 前不保证），但数组元素顺序严格遵循聚合内 ORDER BY。

第 3 步：DISTINCT + ORDER BY 的正确写法 ​

要「每个部门列出出现过的职位（去重、按字母序）」：

sql
-- 正确：排序键就是参数本身
SELECT department, string_agg(DISTINCT title, ' / ' ORDER BY title) AS 职位列表
FROM employees
GROUP BY department
ORDER BY department;
text
 部门 |              职位列表
------+--------------------------------------
 市场 | 市场专员 / 市场总监
 研发 | 工程师 / 前端工程师 / 研发经理
 财务 | 财务经理 / 会计
(3 rows)

排序结果取决于数据库的排序规则（lc_collate）：zh_CN.UTF-8 下按拼音序，C / POSIX 下按字节序。若报表需要固定的拼音序且数据库规则不是中文规则，应显式写 ORDER BY title COLLATE "zh_CN.UTF-8"，或加一个独立的 sort_key 列。

若排序键必须是与参数不同的列（如「按首次出现时间排」），就得先确定每行的一个时间：

sql
SELECT department,
       string_agg(title, ' / ' ORDER BY first_seen) AS 职位列表
FROM (
  SELECT department, title, min(hire_date) AS first_seen
  FROM employees
  GROUP BY department, title
) t
GROUP BY department
ORDER BY department;

第 4 步：排序键参与计算但不出现在输出中 ​

排序键可以是任意表达式，不要求被选中：

sql
-- 按「职位名称长度」排序（短的在前），长度本身不输出
SELECT department, string_agg(name, ' > ' ORDER BY length(title), id) AS 按职位名长
FROM employees
GROUP BY department
ORDER BY department;
text
 部门 |            按职位名长
------+--------------------------------
 市场 | 刘婷 > 陈杰
 研发 | 张伟 > 王强 > 李娜
 财务 | 赵敏 > 孙磊
(3 rows)

length(title) 相同时（市场部的两个职位都是 4 个字）由 id 决定先后。

第 5 步：验证索引带来的计划改善 ​

sql
-- 建索引前后各看一次计划
CREATE INDEX idx_employees_dept_hired ON employees (department, hire_date);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT department, string_agg(name, ', ' ORDER BY hire_date)
FROM employees
GROUP BY department;
text
 GroupAggregate
   Group Key: department
   ->  Index Scan using idx_employees_dept_hired on employees

计划里没有 Sort 节点，说明聚合直接消费了有序索引流。对比做法：把索引改成 (hire_date, department) 后再看计划，会重新出现 Sort。

sql
DROP INDEX idx_employees_dept_hired;
DROP TABLE employees;

注意事项 ​

  1. 不写聚合内 ORDER BY 时顺序是未定义的，不是「按插入顺序」也不是「按主键顺序」，任何一次计划变化都可能改变输出。
  2. ORDER BY 必须写在聚合函数的参数括号内部；写在括号外是查询级排序，与聚合的读取顺序无关。
  3. DISTINCT 与聚合内 ORDER BY 共用时，排序表达式必须出现在参数列表里，否则报 in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list。
  4. 只写一个非唯一列作为排序键仍是不确定的，同值行的相对顺序未定义；用「业务列 + 主键」双键兜底。
  5. sum / count / min / max / bool_and 不受聚合内 ORDER BY 影响，给它们加排序只会白白付出排序代价。
  6. float 类型的 sum 与顺序有微弱的数值关系（浮点加法不满足结合律），numeric 则与顺序无关。
  7. GROUP BY 的输出行顺序也是未定义的，报表若要按分组键排列，末尾必须显式写查询级 ORDER BY。
  8. 有序集聚合的排序写在 WITHIN GROUP (ORDER BY ...) 里，不是参数括号内；percentile_disc(0.5 ORDER BY x) 是语法错误。
  9. 窗口 OVER (ORDER BY ...) 决定帧的滑动，不是「聚合内读取顺序」，三者不要混淆。
  10. 并行查询下两阶段聚合的合并顺序不确定，不写 ORDER BY 的聚合结果可能每次执行都不同。
  11. 聚合内排序在大数据量下可能溢出到磁盘（计划中的 Sort Method: external merge），此时应优先考虑用索引消除排序，而不是一味调大 work_mem。
  12. ORDER BY 后接 NULLS FIRST / NULLS LAST 是允许的，但要注意 ASC 默认 NULLS LAST、DESC 默认 NULLS FIRST；排序键含 NULL 时不同方向的默认行为会让人意外。

最佳实践 ​

  1. 凡是结果依赖行顺序的聚合，一律显式写 ORDER BY，并在代码评审中把它作为硬性检查项。
  2. 排序键使用「业务字段 + 唯一列」，例如 ORDER BY created_at, id；id 兜底能消除所有同值歧义。
  3. 在 SQL 注释里写明排序键的业务含义，让后来者知道这个顺序是「按入职先后」还是「按薪资高低」：
    sql
    string_agg(name, ', ' ORDER BY hire_date, id)  -- 按入职先后，同天按 id
  4. 不确定某个聚合是否受顺序影响时，先查文档再写；排序敏感的只有 string_agg / array_agg / json_agg / jsonb_agg / xmlagg。
  5. 给「分组键 + 聚合排序键」建复合索引，让聚合走 GroupAggregate 直接读索引，消除排序节点。
  6. 索引的列顺序与方向要和 ORDER BY 一致：ORDER BY hire_date DESC 对应 (department, hire_date DESC)。
  7. DISTINCT 场景下先想清楚排序键：排序键必须来自参数列表；若需要额外排序依据，改用子查询先去重再聚合。
  8. WITHIN GROUP 与聚合内 ORDER BY 同时出现时把它们分行写，让两种语法位置一眼可辨，避免抄错。
  9. 用 EXPLAIN (ANALYZE, COSTS OFF) 检查是否存在多余的 Sort 节点，有则考虑补索引而非调 work_mem。
  10. work_mem 只在确认排序无法用索引消除时调大，且优先在会话级设置，避免并发下总内存超限。
  11. 并行场景下把 ORDER BY 视为必需项：worker 完成顺序随机，缺排序的结果无法复现。
  12. 把「顺序敏感」查询纳入回归测试：同一查询连续执行多次并断言结果完全一致，能有效拦截漏写 ORDER BY 的改动。

下一节

继续学习：COALESCE / NULLIF

参考链接 ​