PostgreSQL GROUPING SETS 多维汇总：一次扫描替代 UNION ALL

在报表开发中，经常需要同一张订单表按多个粒度汇总：地区×品类、地区、品类和总计。常见写法是用四条聚合 SQL 加 UNION ALL 拼接。它直观，但每条分支都会独立访问订单表，过滤条件重复，维护成本高，而且多个分支可能读到不同快照。PostgreSQL 的 GROUPING SETS 允许在一个查询中声明多组分组键，通常只需对基表扫描一次；再配合 GROUPING() 函数，可以区分真实 NULL 和汇总行产生的 NULL。

问题场景与表结构

下面用一张简化的订单表演示。金额使用 numeric，日期用于限定报表周期。样例数据中特意保留 region 或 category 为 NULL 的行，用来验证 GROUPING() 的区分能力。

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  region text,
  category text,
  amount numeric(12,2) NOT NULL,
  created_at date NOT NULL
);

INSERT INTO orders (region, category, amount, created_at) VALUES
  ('north', 'book', 100, DATE '2024-01-01'),
  ('north', 'book', 50,  DATE '2024-01-02'),
  ('north', 'toy', 30,   DATE '2024-01-03'),
  ('south', 'book', 80,  DATE '2024-01-04'),
  (NULL, 'book', 20,     DATE '2024-01-05'),
  ('north', NULL, 40,    DATE '2024-01-06');

一条 SQL 生成四种粒度

查询使用 GROUPING SETS 声明四组分组键：(region, category)、(region)、(category) 和 ()。空集 () 表示总计。SELECT 中的 GROUPING(region) 和 GROUPING(category) 返回 0 或 1：0 表示该列参与了当前分组，1 表示该列被汇总掉了。

SELECT
  region,
  category,
  GROUPING(region) AS g_region,
  GROUPING(category) AS g_category,
  SUM(amount) AS total_amount
FROM orders
WHERE created_at >= DATE '2024-01-01'
  AND created_at < DATE '2024-02-01'
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  (category),
  ()
)
ORDER BY
  CASE
    WHEN GROUPING(region)=0 AND GROUPING(category)=0 THEN 1
    WHEN GROUPING(region)=0 AND GROUPING(category)=1 THEN 2
    WHEN GROUPING(region)=1 AND GROUPING(category)=0 THEN 3
    ELSE 4
  END,
  region NULLS LAST,
  category NULLS LAST;

这里的关键不是把 NULL 当成“汇总”标记，而是用 g_region、g_category 标记行的层级。真实 region 为 NULL 的明细行是 region IS NULL AND g_region=0；地区汇总行的 region 也可能是 NULL，但 g_region=0 AND g_category=1，含义完全不同。

运行方法

将建表、插入和查询语句保存为 demo.sql，在测试库执行：

psql -h <主机> -p 5432 -d <数据库> -U <用户> -f demo.sql

也可以进入 psql 后使用 \i demo.sql。执行前确认连接的是测试环境，示例表名和字段名按实际报表调整。

结果与排序示例

按上面的 ORDER BY，输出会先列明细，再列地区小计、品类小计，最后是总计。金额结果如下：

其中 north NULL 0 0 40.00 是真实 category 为 NULL 的明细；NULL book 0 0 20.00 是真实 region 为 NULL 的明细。两个标志都为 1 的行才是总计。展示层可以根据标志生成“明细/地区小计/品类小计/总计”，不要直接用 COALESCE 把汇总 NULL 改成 0 或“未知”。

EXPLAIN 对比扫描次数

先看等效的 UNION ALL 写法。四个分支的过滤条件相同，但每个分支各自聚合：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT region, category, 0 AS g_region, 0 AS g_category, SUM(amount) AS total_amount
FROM orders
WHERE created_at >= DATE '2024-01-01' AND created_at < DATE '2024-02-01'
GROUP BY region, category
UNION ALL
SELECT region, NULL::text, 0, 1, SUM(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01' AND created_at < DATE '2024-02-01'
GROUP BY region
UNION ALL
SELECT NULL::text, category, 1, 0, SUM(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01' AND created_at < DATE '2024-02-01'
GROUP BY category
UNION ALL
SELECT NULL::text, NULL::text, 1, 1, SUM(amount)
FROM orders
WHERE created_at >= DATE '2024-01-01' AND created_at < DATE '2024-02-01';

再用 GROUPING SETS 观察计划，可暂时去掉 ORDER BY 以突出扫描和聚合节点：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT
  region,
  category,
  GROUPING(region) AS g_region,
  GROUPING(category) AS g_category,
  SUM(amount) AS total_amount
FROM orders
WHERE created_at >= DATE '2024-01-01'
  AND created_at < DATE '2024-02-01'
GROUP BY GROUPING SETS ((region, category), (region), (category), ());

读计划时重点看 orders 的扫描节点。UNION ALL 计划中，orders 的扫描节点会按分支重复出现；GROUPING SETS 计划中通常只出现一个 orders 扫描节点，聚合部分可能表现为 MixedAggregate 或拆成多个 GroupAggregate。若表是分区表，Append 下每个分区仍可能出现子扫描，不能只数节点个数。实际计划受版本、统计信息、索引和工作负载影响，应使用 EXPLAIN (ANALYZE, BUFFERS) 在目标环境验证，不要只凭成本估算下结论。

边界与限制

• GROUPING SETS 和 GROUPING() 在较新的 PostgreSQL 版本中可用；若目标环境版本较旧，需要改用 UNION ALL 或外层查询。
• GROUPING() 的参数应来自 GROUP BY 中的分组表达式，不能随意用于未分组的列。
• HAVING 会作用于所有粒度。若在 HAVING 中过滤金额，可能连小计和总计一起过滤；需要保留层级时，把过滤放到外层查询。
• 不同粒度需要不同 WHERE 条件时，GROUPING SETS 不能直接表达，应使用条件聚合或 UNION ALL。
• 四种粒度会输出更多行，排序和聚合可能使用临时文件；数据量大时关注 work_mem 和计划中的 Sort、HashAggregate 节点。
• 它减少的是重复扫描和重复过滤，不替代索引设计；查询仍要读取满足条件的行，性能应以实际执行计划为准。

小结

GROUPING SETS 把多粒度报表收敛到一个查询里，配合 GROUPING() 保留 NULL 的语义，适合地区、品类、总计这类固定维度的汇总。先用小数据验证层级和 NULL 标记，再用 EXPLAIN 对比实际计划，比直接堆 UNION ALL 更易维护。