PHP小码农

Appearance

GROUPING SETS ​

本节讲解 GROUPING SETS——在一次查询里对同一份数据按多种维度组合分别汇总的语法。它用一次扫描替代多次 UNION ALL，是制作「明细 + 小计 + 总计」多维报表的核心工具。学完本节，你将能用一条 SQL 直接产出可交付的四层报表。

前置知识

阅读本节前，建议先了解：LATERAL 实战场景

基础概念 ​

分组集（Grouping Set） 指的是「一组参与分组的列」。普通 GROUP BY a, b 只有一个分组集 (a, b)；而 GROUPING SETS 允许你显式列出多个分组集，查询会对每一个分组集各产出一批分组行，最后把它们纵向拼接成一个结果集。

概念术语说明
分组集Grouping Set一组参与分组的列的集合，如 (city, category)
分组集列表GROUPING SETS一次查询中要计算的多个分组集的并集
空分组集Empty Grouping Set ()不含任何列的分组集，产出「全表总计」一行
汇总行Summary Row某个分组集中被省略的列，在结果里显示为 NULL
明细行Detail Row完整维度组合产生的行，通常对应最细粒度的分组集
行膨胀Row Expansion分组集个数增加导致结果行数成倍增长
混合聚合MixedAggregate优化器为多分组集选择的一种聚合实现方式
位图标记Grouping BitmapGROUPING() 返回的、标识哪些列未参与分组的整数

核心语义：GROUPING SETS 等价于把多个 GROUP BY 的结果做 UNION ALL，但只扫描一次表。

text
GROUP BY GROUPING SETS ((a, b), (a), (b), ())
         等价于
GROUP BY a, b  UNION ALL  GROUP BY a  UNION ALL  GROUP BY b  UNION ALL  无 GROUP BY

先看一个最小对照，理解「未参与分组的列为 NULL」这一点：

分组集输出行的 city输出行的 category语义
(city, category)真实城市真实品类城市 × 品类明细
(city)真实城市NULL城市小计
(category)NULL真实品类品类小计
()NULLNULL全表总计

分组集与 UNION ALL 的代价对比：

维度GROUPING SETS多个 GROUP BY + UNION ALL
基表扫描次数1 次N 次（N = 分组集个数）
中间结果一份N 份，且需去重语义处理
SQL 长度短，维度集中声明长，每个分组集写一遍
排序需求单次排序或哈希聚合每个分支各自排序
计划形态一个 GroupAggregate / MixedAggregateAppend 下挂 N 个聚合节点
可维护性改维度只改一行改维度要改多个分支

关键结论：分组集个数越多、基表越大，GROUPING SETS 的优势越明显。当只有 2 个分组集且基表很小时，两者差别可以忽略，此时可读性优先。

语法与用法 ​

sql
-- 基本形态：显式列出每一个分组集
SELECT 维度列..., 聚合函数(...)
FROM 表
GROUP BY GROUPING SETS (
  (a, b),   -- 明细：a × b
  (a),      -- 小计：按 a
  (b),      -- 小计：按 b
  ()        -- 总计
)
ORDER BY ...;

准备本节贯穿使用的示例表（在 demo 库中执行）：

sql
-- 销售明细表：城市 × 品类 × 渠道
CREATE TABLE sales (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  city       TEXT           NOT NULL,
  category   TEXT           NOT NULL,
  channel    TEXT           NOT NULL,
  qty        INTEGER        NOT NULL CHECK (qty > 0),
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
  sold_at    DATE           NOT NULL
);

-- 造数：5 城市 × 4 品类 × 3 渠道，共 3 万行
INSERT INTO sales (city, category, channel, qty, amount, sold_at)
SELECT
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  (ARRAY['手机', '电脑', '家电', '配件'])[1 + (i % 4)],
  (ARRAY['线上', '门店', '分销'])[1 + (i % 3)],
  1 + (i % 20),
  round((random() * 2000 + 100)::numeric, 2),
  DATE '2026-01-01' + (i % 180)
FROM generate_series(1, 30000) AS i;

-- 为分组键建索引，便于 GroupAggregate 复用索引顺序
CREATE INDEX idx_sales_city_category ON sales (city, category);
CREATE INDEX idx_sales_channel       ON sales (channel);

ANALYZE sales;

四层报表的最小可运行示例：

sql
-- 一条 SQL 同时产出：城市 × 品类明细 + 城市小计 + 品类小计 + 总计
SELECT
  city,
  category,
  count(*)          AS 订单数,
  sum(amount)       AS 销售额,
  GROUPING(city)    AS g_city,      -- 1 表示城市列被汇总，未参与该分组集
  GROUPING(category) AS g_category
FROM sales
GROUP BY GROUPING SETS (
  (city, category),   -- 明细层
  (city),             -- 城市小计层
  (category),         -- 品类小计层
  ()                  -- 总计层
)
ORDER BY GROUPING(city), city, GROUPING(category), category;
text
  city  | category | 订单数 |  销售额   | g_city | g_category
--------+----------+--------+-----------+--------+------------
 上海   | 家电     |    300 | 315000.00 |      0 |          0
 上海   | 手机     |    300 | 312000.00 |      0 |          0
 ...
 上海   | 家电     |   1200 | 1258000.00|      0 |          1
 ...
          | 手机     |   1500 | 1560000.00|      1 |          0
 ...
          |          |   6000 | 6258000.00|      1 |          1

上面的 NULL 不是数据缺失，而是该行代表汇总——这正是引出 GROUPING() 函数的原因，详见 GROUPING / GROUPING_ID。

详细说明 ​

分组集语义与 UNION ALL 等价性 ​

把 GROUPING SETS 手工展开成 UNION ALL，是理解它语义最快的方式：

sql
-- 写法一：GROUPING SETS（只扫一次 sales）
SELECT city, category, sum(amount) AS 销售额
FROM sales
WHERE sold_at >= DATE '2026-03-01'
GROUP BY GROUPING SETS ((city, category), (city), (category), ())
ORDER BY city NULLS LAST, category NULLS LAST;

-- 写法二：UNION ALL（sales 被扫 4 次）
SELECT city, category, sum(amount) AS 销售额
FROM sales WHERE sold_at >= DATE '2026-03-01' GROUP BY city, category
UNION ALL
SELECT city, NULL,    sum(amount) FROM sales WHERE sold_at >= DATE '2026-03-01' GROUP BY city
UNION ALL
SELECT NULL, category, sum(amount) FROM sales WHERE sold_at >= DATE '2026-03-01' GROUP BY category
UNION ALL
SELECT NULL, NULL,     sum(amount) FROM sales WHERE sold_at >= DATE '2026-03-01';

两种写法结果集完全相同（列类型与顺序都对得上），但计划形态截然不同：

text
-- GROUPING SETS 的计划：一个聚合节点处理全部 4 个分组集
GroupAggregate
  Group Key: city, category
  Grouping Sets: ((city, category), (city), (category), ())
  -> Sort
        -> Seq Scan on sales
              Filter: (sold_at >= '2026-03-01'::date)

-- UNION ALL 的计划：4 个分支各自聚合，Append 拼接
Append
  -> GroupAggregate  -> Seq Scan on sales  (Filter ...)
  -> GroupAggregate  -> Seq Scan on sales  (Filter ...)
  -> GroupAggregate  -> Seq Scan on sales  (Filter ...)
  -> Aggregate       -> Seq Scan on sales  (Filter ...)

GROUPING SETS 版本中 sales 只出现一次，UNION ALL 版本出现四次。这是本节最重要的性能论点。

空分组集 () 与全表总计 ​

空分组集 () 表示「不按任何列分组」，等价于不带 GROUP BY 的聚合，永远产出恰好一行：

sql
-- 下面两条语义完全相同，都返回一行全表总计
SELECT sum(amount) AS 总计 FROM sales;

SELECT sum(amount) AS 总计 FROM sales GROUP BY GROUPING SETS (());

把 () 放进分组集列表，就是给报表追加一行「总计」：

sql
-- 城市明细 + 总计（两行）
SELECT coalesce(city, '【总计】') AS city, sum(amount) AS 销售额
FROM sales
GROUP BY GROUPING SETS ((city), ())
ORDER BY GROUPING(city), city;
text
   city   |  销售额
----------+-----------
 上海     | 1258000.00
 北京     | 1249000.00
 广州     | 1252000.00
 深圳     | 1250000.00
 杭州     | 1251000.00
 【总计】 | 6258000.00
(6 rows)

注意：分组集列表里出现重复的分组集会报错，分组集之间不能有包含关系导致的歧义（见「注意事项」）。

NULL 的歧义：汇总产生的 NULL 与数据本身的 NULL ​

这是 GROUPING SETS 最容易踩的坑。若 sales.city 本身允许为 NULL（例如「未填写城市」），那么结果里的 NULL 就可能有两种来源：

city 显示值可能来源是否参与 GROUPING() 判断
NULL数据里 city IS NULLGROUPING(city) = 0（参与了分组）
NULL该行是「品类小计」或「总计」GROUPING(city) = 1（未参与分组）
sql
-- 制造一个「城市真为 NULL」的场景
INSERT INTO sales (city, category, channel, qty, amount, sold_at)
VALUES (NULL, '配件', '线上', 3, 199.00, DATE '2026-03-01');

-- 光看结果无法区分两种 NULL
SELECT city, category, sum(amount) AS 销售额
FROM sales
WHERE category = '配件'
GROUP BY GROUPING SETS ((city, category), ())
ORDER BY city NULLS LAST;

-- 加上 GROUPING() 才能区分
SELECT
  city,
  category,
  sum(amount)    AS 销售额,
  GROUPING(city) AS g_city,
  GROUPING(category) AS g_category
FROM sales
WHERE category = '配件'
GROUP BY GROUPING SETS ((city, category), ())
ORDER BY GROUPING(city), city;
text
  city  | category |  销售额  | g_city | g_category
--------+----------+----------+--------+------------
        | 配件     |   199.00 |      0 |          0   <- 真实 NULL 数据
 上海   | 配件     | 31500.00 |      0 |          0
 北京   | 配件     | 31200.00 |      0 |          0
 ...
        |          | 62000.00 |      1 |          1   <- 总计行

两条 city IS NULL 的行，靠 g_city 一列即可分辨。

CROSS JOIN 与 GROUPING SETS 的等价改写 ​

分组集列表也可以由两个维度列表做笛卡尔积得到，这种写法叫 CROSS JOIN 形式，与显式 GROUPING SETS 完全等价：

sql
-- 写法一：显式列出 4 个分组集
GROUP BY GROUPING SETS ((city, category), (city), (category), ())

-- 写法二：两个维度列表做笛卡尔积
GROUP BY GROUPING SETS (city, ()), GROUPING SETS (category, ())

-- 写法三：用 CROSS JOIN 语义展开（部分数据库支持，PostgreSQL 用写法二）
GROUP BY GROUPING SETS ((city), ()), GROUPING SETS ((category), ())

GROUPING SETS (a, ()) 的笛卡尔积语义是「要么按 a 分组、要么不分组」，两个这样的列表相乘就得到 4 个分组集：

左侧列表右侧列表乘积分组集
(city)(category)(city, category)
(city)()(city)
()(category)(category)
()()()

这种表达方式在需要「每个维度都可以独立出现或消失」时特别简洁，也是理解 ROLLUP、CUBE 展开规则的桥梁。

分组集与 HAVING、ORDER BY 的配合 ​

HAVING 在分组集之后过滤，它对每一个分组集产出的行都生效：

sql
-- HAVING 过滤所有分组集产出的行：销售额不足 5 万的整行被剔除
SELECT city, category, sum(amount) AS 销售额
FROM sales
GROUP BY GROUPING SETS ((city, category), (city), ())
HAVING sum(amount) >= 50000
ORDER BY GROUPING(city), city, GROUPING(category), category;

这带来一个常见困扰：总计行也可能被 HAVING 一起干掉。若只想过滤明细层，必须把分组集类型带出来再判断：

sql
-- 只过滤明细层，保留所有小计与总计
SELECT city, category, sum(amount) AS 销售额, GROUPING(city) AS g_city
FROM sales
GROUP BY GROUPING SETS ((city, category), (city), ())
HAVING GROUPING(city) = 1 OR sum(amount) >= 50000
ORDER BY g_city, city, category;

ORDER BY 可以用 GROUPING() 把层级排整齐。推荐的两段式排序键：

sql
-- 明细在前、小计居中、总计最后
ORDER BY GROUPING(city), city NULLS LAST, GROUPING(category), category NULLS LAST;

-- 若希望「先按城市聚合，再在城市内展示品类明细」，把小计排在该城市明细之后
ORDER BY city NULLS LAST, GROUPING(category), category NULLS LAST;
排序目标推荐 ORDER BY
所有明细行最前，汇总行最后GROUPING(city), city, GROUPING(category), category
每个城市内部：明细 → 城市小计city NULLS LAST, GROUPING(category), category NULLS LAST
总计永远在最后一行追加 GROUPING(city) DESC 或直接用 GROUPING(city) 升序

重复与空分组集的规则 ​

分组集列表里有几条硬规则，违反会直接报错：

写法是否合法说明
GROUPING SETS ((a), (a))❌重复的分组集报错
GROUPING SETS ((a), (a, b))✅无包含关系歧义，允许
GROUPING SETS (a, b)✅裸列名等价于各自的单列分组集 (a)、(b)
GROUPING SETS ((a, b), ())✅最常见的最小四层写法之一
GROUPING SETS ()✅只有一个空分组集，等价于无 GROUP BY
GROUPING SETS ((a, a))❌同一列重复出现在一个分组集内
sql
-- 错误！重复的分组集
SELECT city, sum(amount) FROM sales GROUP BY GROUPING SETS ((city), (city));
text
ERROR:  duplicate GROUPING SET element
sql
-- 正确：裸列名会被自动转成单元素分组集
SELECT city, sum(amount) FROM sales GROUP BY GROUPING SETS (city, ());
sql
-- PostgreSQL 14+：允许在 GROUP BY 中把普通列与 GROUPING SETS 并列混用
SELECT city, channel, sum(amount) AS 销售额
FROM sales
GROUP BY GROUPING SETS ((channel), ()), city
ORDER BY city NULLS LAST, GROUPING(channel), channel;
text
-- 等价展开：city 被「前置」到每一个分组集里
GROUP BY GROUPING SETS ((city, channel), (city), ())

执行计划中的表现 ​

GROUPING SETS 在计划里表现为以下三种形态之一：

计划节点出现条件说明
GroupAggregate + Grouping Sets:输入已按分组键有序复用排序或索引顺序，内存占用小
MixedAggregate多个分组集的键集合不同，需要混合策略部分分组集走哈希、部分走排序
HashAggregate + Grouping Sets:分组集键集合一致，可一次哈希Batches > 1 表示哈希表落盘
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT city, category, sum(amount)
FROM sales
GROUP BY GROUPING SETS ((city, category), (city), (category), ())
ORDER BY GROUPING(city), city, GROUPING(category), category;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Sort  (cost=1891.42..1891.52 rows=29 width=76) (actual time=48.221..48.229 rows=29 loops=1)
   Sort Key: (GROUPING(city)), city, (GROUPING(category)), category
   Sort Method: quicksort  Memory: 26kB
   Buffers: shared hit=204
   ->  MixedAggregate  (cost=1320.00..1890.75 rows=29 width=76) (actual time=48.196..48.206 rows=29 loops=1)
         Hash Key: city, category
         Hash Key: city
         Hash Key: category
         Group Key: ()
         Buffers: shared hit=204
         ->  Seq Scan on sales  (cost=0.00..720.00 rows=30000 width=44) (actual time=0.012..9.834 rows=30000 loops=1)
               Buffers: shared hit=204
 Planning Time: 0.318 ms
 Execution Time: 48.402 ms

读计划的关键点：

  • MixedAggregate 表明优化器同时用了哈希（前三个分组集）和「无分组键聚合」（Group Key: ()）两种策略。
  • Seq Scan on sales ... rows=30000 只出现一次，证明只扫描了一遍。
  • 想让它变成 HashAggregate，可提高 work_mem 避免落盘：
sql
SET work_mem = '128MB';

分组集个数与内存、行数上限 ​

分组集的数量直接决定聚合所需的中间状态数量：

分组集个数结果行数上界内存压力建议
1~4各维度基数之和低常规使用，无特殊处理
5~16组合数快速增长中关注 Batches 是否 > 1
17~32可能数万行高提高 work_mem，或拆成多条查询
> 32通常失去意义很高改用 ROLLUP / CUBE 或物化汇总表

注意：GROUPING SETS 中显式列出的分组集个数没有硬性上限，但每个分组集都会让聚合状态多一份。当分组集很多且基表很大时，HashAggregate 会落盘，性能反而不如多次 UNION ALL。判断依据是计划里的 Batches：

text
HashAggregate  (... rows=8421 width=76) (actual time=... rows=8421 loops=1)
  Grouping Sets: ...
  Batches: 4  Memory Usage: 9281kB   <- Batches > 1：哈希表已落盘，需要提高 work_mem

实战示例 ​

场景：为运营团队产出「城市 × 品类」多维销售报表，要求同一份结果集里包含四层：城市 × 品类明细、城市小计、品类小计、全表总计。要求：一次扫描完成、汇总行有中文标签、排序自然。

步骤 1：准备数据与索引 ​

sql
-- 建表与造数（3 万行，5 城市 × 4 品类 × 3 渠道）
CREATE TABLE sales_report (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  city       TEXT           NOT NULL,
  category   TEXT           NOT NULL,
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
  sold_at    DATE           NOT NULL
);

INSERT INTO sales_report (city, category, amount, sold_at)
SELECT
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  (ARRAY['手机', '电脑', '家电', '配件'])[1 + (i % 4)],
  round((random() * 2000 + 100)::numeric, 2),
  DATE '2026-01-01' + (i % 180)
FROM generate_series(1, 30000) AS i;

CREATE INDEX idx_sales_report_city_category ON sales_report (city, category);
ANALYZE sales_report;

步骤 2：四层报表查询 ​

sql
-- 用 CASE + GROUPING() 给汇总行加中文标签
SELECT
  CASE WHEN GROUPING(city) = 1     THEN '【全部城市】' ELSE city     END AS 城市,
  CASE WHEN GROUPING(category) = 1 THEN '【全部品类】' ELSE category END AS 品类,
  count(*)                            AS 订单数,
  sum(amount)                         AS 销售额,
  round(avg(amount), 2)               AS 客单价
FROM sales_report
WHERE sold_at >= DATE '2026-01-01'
  AND sold_at <  DATE '2026-07-01'
GROUP BY GROUPING SETS (
  (city, category),   -- 明细层
  (city),             -- 城市小计
  (category),         -- 品类小计
  ()                  -- 全表总计
)
ORDER BY GROUPING(city), 城市, GROUPING(category), 品类;
text
    城市     |    品类    | 订单数 |  销售额   | 客单价
-------------+------------+--------+-----------+---------
 上海        | 家电       |    300 | 316412.00 | 1054.71
 上海        | 电脑       |    300 | 315102.00 | 1050.34
 上海        | 手机       |    300 | 312004.00 | 1040.01
 上海        | 配件       |    300 | 311908.00 | 1039.69
 上海        | 【全部品类】|   1200 |1255426.00 | 1046.19
 北京        | 家电       |    300 | 313899.00 | 1046.33
 ...
 【全部城市】| 手机       |   1500 |1562100.00 | 1041.40
 【全部城市】| 【全部品类】|   6000 |6258000.00 | 1043.00
(29 rows)

行数验证：5 城市 × 4 品类 = 20 行明细 + 5 行城市小计 + 4 行品类小计 + 1 行总计 = 29 行，与计划里估算的 rows=29 完全一致。

步骤 3：与 UNION ALL 写法做性能对照 ​

sql
-- 对照写法：UNION ALL 需要扫描 4 次
EXPLAIN (ANALYZE, BUFFERS)
SELECT city, category, sum(amount) FROM sales_report GROUP BY city, category
UNION ALL
SELECT city, NULL,     sum(amount) FROM sales_report GROUP BY city
UNION ALL
SELECT NULL, category, sum(amount) FROM sales_report GROUP BY category
UNION ALL
SELECT NULL, NULL,     sum(amount) FROM sales_report;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Append  (cost=1180.00..4920.00 rows=30 width=76) (actual time=... rows=29 loops=1)
   Buffers: shared hit=816
   ->  HashAggregate  (...)
         ->  Seq Scan on sales_report  (...)
   ->  HashAggregate  (...)
         ->  Seq Scan on sales_report  (...)
   ->  HashAggregate  (...)
         ->  Seq Scan on sales_report  (...)
   ->  Aggregate  (...)
         ->  Seq Scan on sales_report  (...)
 Execution Time: 132.640 ms
text
-- GROUPING SETS 版本（同一份数据）
 Execution Time: 48.402 ms
指标UNION ALL（4 分支）GROUPING SETS提升
sales_report 扫描次数41—
shared hit（缓冲块）8162044×
执行耗时132.6 ms48.4 ms2.7×
计划节点数Append + 4 个聚合1 个 MixedAggregate—

步骤 4：验证两种写法结果完全一致 ​

sql
-- 双向差集自检，两个计数都应为 0
WITH gs AS (
  SELECT city, category, count(*) AS c, sum(amount) AS s
  FROM sales_report
  GROUP BY GROUPING SETS ((city, category), (city), (category), ())
),
un AS (
  SELECT city, category, count(*) AS c, sum(amount) AS s
  FROM sales_report GROUP BY city, category
  UNION ALL
  SELECT city, NULL,     count(*), sum(amount) FROM sales_report GROUP BY city
  UNION ALL
  SELECT NULL, category, count(*), sum(amount) FROM sales_report GROUP BY category
  UNION ALL
  SELECT NULL, NULL,     count(*), sum(amount) FROM sales_report
)
SELECT 'only_in_gs' AS 差异方向, count(*) FROM (
  SELECT * FROM gs EXCEPT ALL SELECT * FROM un
) t
UNION ALL
SELECT 'only_in_un', count(*) FROM (
  SELECT * FROM un EXCEPT ALL SELECT * FROM gs
) t;
text
  差异方向  | count
------------+-------
 only_in_gs |     0
 only_in_un |     0
(2 rows)

步骤 5：只保留汇总行（去明细） ​

「只交付小计与总计，不给明细」是常见的报表形态，用 GROUPING() 在外层过滤即可：

sql
-- 只保留城市小计 + 品类小计 + 总计，共 10 行
SELECT * FROM (
  SELECT
    city, category,
    sum(amount)     AS 销售额,
    GROUPING(city)     AS g_city,
    GROUPING(category) AS g_category
  FROM sales_report
  GROUP BY GROUPING SETS ((city, category), (city), (category), ())
) t
WHERE g_city = 1 OR g_category = 1
ORDER BY g_city, g_category, city NULLS LAST, category NULLS LAST;
text
  city  | category |  销售额  | g_city | g_category
--------+----------+-----------+--------+------------
 上海   |          | 1255426.00|      0 |          1
 北京   |          | 1252100.00|      0 |          1
 广州   |          | 1251800.00|      0 |          1
 深圳   |          | 1250600.00|      0 |          1
 杭州   |          | 1248074.00|      0 |          1
        | 家电     | 1563200.00|      1 |          0
        | 电脑     | 1562100.00|      1 |          0
        | 手机     | 1561400.00|      1 |          0
        | 配件     | 1571300.00|      1 |          0
        |          | 6258000.00|      1 |          1
(10 rows)

为什么必须外套一层子查询？ 因为 GROUPING() 与聚合函数一样，不能在 WHERE 里使用，只能写在 HAVING 或外层查询中。这是最容易犯的语法错误。

注意事项 ​

  1. GROUPING() 不能出现在 WHERE 里，必须用 HAVING 或外层子查询：
sql
-- 错误！GROUPING() 在 WHERE 中不可用
SELECT city, sum(amount) FROM sales
WHERE GROUPING(city) = 1
GROUP BY GROUPING SETS ((city), ());
-- 报错：aggregate functions are not allowed in WHERE

-- 正确：用 HAVING
SELECT city, sum(amount) FROM sales
GROUP BY GROUPING SETS ((city), ())
HAVING GROUPING(city) = 1;

-- 正确：用外层子查询（更灵活，可同时排序）
SELECT * FROM (
  SELECT city, sum(amount) AS s, GROUPING(city) AS g FROM sales
  GROUP BY GROUPING SETS ((city), ())
) t WHERE g = 1;
  1. 不要用 coalesce(city, '总计') 判断汇总行。当数据本身含 NULL 时，这个写法会把真实 NULL 误标为「总计」：
sql
-- 不推荐：数据里的真实 NULL 会被误标
SELECT coalesce(city, '【总计】') AS city, sum(amount) FROM sales GROUP BY ROLLUP (city);

-- 推荐：用 GROUPING() 判断
SELECT CASE WHEN GROUPING(city) = 1 THEN '【总计】' ELSE city END AS city, sum(amount)
FROM sales GROUP BY ROLLUP (city);
  1. GROUPING SETS 里不能有重复的分组集，也不要写 (a, a)：
sql
-- 错误！duplicate GROUPING SET element
GROUP BY GROUPING SETS ((city), (city));

-- 错误！同一分组集内列重复
GROUP BY GROUPING SETS ((city, city));
  1. HAVING 会同时作用于所有分组集，包括总计行。只想过分明细层时，务必把 GROUPING() 条件带进 HAVING：
sql
-- 不推荐：总计行也可能因金额不足被剔除，报表最后一行消失
GROUP BY GROUPING SETS ((city, category), ())
HAVING sum(amount) >= 50000;

-- 推荐：显式放行汇总行
HAVING GROUPING(city) = 1 OR sum(amount) >= 50000;
  1. count(*) 在汇总行里是「小计」而非「去重后总行数」，多层级相加会重复计数。若需要「去重的总量」，必须单独算：
sql
-- 各城市 count(*) 之和 = 30000，但总计行的 count(*) 也是 30000，不是 60000
SELECT city, count(*) FROM sales GROUP BY GROUPING SETS ((city), ());
  1. 分组集很多时注意 Batches 落盘。计划里出现 Batches > 1 说明哈希表溢出到临时文件，性能会明显劣化：
text
-- 异常信号
HashAggregate (actual time=... rows=...)
  Batches: 8  Memory Usage: 4096kB
sql
-- 缓解手段：提高会话级 work_mem
SET work_mem = '256MB';

  1. 分组键上有 COLLATE 或函数包裹时，GroupAggregate 可能退化为 HashAggregate，失去「免排序」的优势。需要稳定计划时，考虑建表达式索引。

  2. GROUPING SETS 不改变 ORDER BY 的语义。不写 ORDER BY 时输出顺序完全不确定，报表必须显式排序。

最佳实践 ​

  1. 能用 GROUPING SETS 就别写多个 UNION ALL。 分组集越多、表越大，收益越明显：
sql
-- 推荐骨架
SELECT 维度..., 聚合...
FROM 表
GROUP BY GROUPING SETS ((d1, d2), (d1), (d2), ())
ORDER BY GROUPING(d1), d1 NULLS LAST, GROUPING(d2), d2 NULLS LAST;
  1. 汇总行的标签一律用 CASE WHEN GROUPING(col) = 1，不要用 coalesce。这是唯一能区分「汇总 NULL」与「真实 NULL」的手段：
sql
CASE WHEN GROUPING(city) = 1 THEN '【全部城市】' ELSE city END AS 城市
  1. 分组集列表按「从细到粗」书写，与阅读顺序一致，也便于排错：
sql
GROUP BY GROUPING SETS (
  (city, category),   -- 1. 最细
  (city),             -- 2. 次细
  (category),         -- 3. 次细
  ()                  -- 4. 最粗
)
  1. 排序统一以 GROUPING() 开头，让明细、小计、总计自然分层：
sql
ORDER BY GROUPING(city), city NULLS LAST, GROUPING(category), category NULLS LAST
  1. 给分组键建与 GROUP BY 列顺序一致的复合索引，让 GroupAggregate 能复用索引顺序而免去 Sort：
sql
CREATE INDEX idx_sales_city_category ON sales (city, category);
  1. 报表口径过滤放在 WHERE，把「组级」条件留给 HAVING：
sql
-- 时间范围是行级条件 → WHERE
SELECT city, category, sum(amount)
FROM sales
WHERE sold_at >= DATE '2026-01-01' AND sold_at < DATE '2026-07-01'   -- 行级
GROUP BY GROUPING SETS ((city, category), (city), ())
HAVING GROUPING(city) = 1 OR sum(amount) >= 50000;                   -- 组级
  1. 改写后必须用 EXCEPT ALL 双向差集验证结果一致，尤其是从 UNION ALL 迁移过来时：
sql
SELECT 'only_in_new', count(*) FROM (SELECT * FROM new_q EXCEPT ALL SELECT * FROM old_q) t
UNION ALL
SELECT 'only_in_old', count(*) FROM (SELECT * FROM old_q EXCEPT ALL SELECT * FROM new_q) t;
  1. 分组集超过 16 个时先评估是否该换工具：层级汇总用 ROLLUP、全组合用 CUBE（但要注意 2^N 膨胀）、跨语句复用则直接建物化视图：
sql
-- 层级汇总：ROLLUP 写起来更短、意图更清楚
GROUP BY ROLLUP (year, quarter, month)     -- 等价于 4 个分组集

-- 全组合：CUBE 但 N 不宜超过 4
GROUP BY CUBE (city, category, channel)    -- 8 个分组集

下一节

继续学习：ROLLUP

参考链接 ​