ROLLUP

ROLLUP 是 GROUPING SETS 的层级缩写：当你需要的分组集恰好构成「逐级减少维度」的阶梯时，用 ROLLUP (a, b, c) 一行就能表达 GROUPING SETS ((a,b,c),(a,b),(a),())。它天然贴合「年 → 季 → 月」「大区 → 省 → 市」这类层级报表。学完本节，你将能用一行 ROLLUP 产出带小计与总计的层级汇总，并正确识别哪些行是小计行。

前置知识

阅读本节前，建议先了解：GROUPING SETS

基础概念

上卷（Roll Up） 是 OLAP 中的标准操作：沿维度层级向上聚合，把明细层逐级压缩到更粗的粒度。PostgreSQL 用 ROLLUP 关键字把这个过程写成语法糖。

概念	术语	说明
上卷	Roll Up	沿层级由细到粗逐级聚合的操作
层级分组	Hierarchical Grouping	参与分组的列本身构成上下级关系
分组集	Grouping Set	一组参与分组的列，ROLLUP 展开后得到 N+1 个
小计行	Subtotal Row	高层级对应的行，低层级列为 NULL
总计行	Grand Total Row	对应空分组集 () 的那一行
嵌套上卷	Nested Rollup	用 ROLLUP (a, ROLLUP (b, c)) 表达的复合层级
层级标记	Grouping Flag	GROUPING() 返回值，用于区分明细行与小计行

核心语义：ROLLUP 只做「前缀递减」，不做全组合。这是它与 CUBE 最本质的区别。

GROUP BY ROLLUP (a, b, c)
        ≡ GROUP BY GROUPING SETS ((a, b, c), (a, b), (a), ())
        共 3 + 1 = 4 个分组集（N 列产生 N+1 个）

GROUP BY ROLLUP (a, b)
        ≡ GROUP BY GROUPING SETS ((a, b), (a), ())
        共 2 + 1 = 3 个分组集

GROUP BY ROLLUP (a)
        ≡ GROUP BY GROUPING SETS ((a), ())
        共 1 + 1 = 2 个分组集

完整展开对照表（以三列为例，务必记牢这一张表）：

ROLLUP 写法	展开后的分组集	分组集个数	语义
ROLLUP ()	()	1	仅总计
ROLLUP (a)	(a), ()	2	a 小计 + 总计
ROLLUP (a, b)	(a, b), (a), ()	3	a×b 明细 + a 小计 + 总计
ROLLUP (a, b, c)	(a, b, c), (a, b), (a), ()	4	三级明细 + 二级小计 + 一级小计 + 总计
ROLLUP (a, b, c, d)	(a,b,c,d), (a,b,c), (a,b), (a), ()	5	N 列恒为 N+1 个

多组 ROLLUP 并列：写成 GROUP BY ROLLUP (a), ROLLUP (b) 时，PostgreSQL 会对两组展开结果求笛卡尔积：

写法	展开后的分组集	个数
ROLLUP (a), ROLLUP (b)	(a,b), (a), (b), ()	2 × 2 = 4
ROLLUP ((a, b))	(a, b), ()	2
ROLLUP (a, b)	(a, b), (a), ()	3

注意 ROLLUP ((a, b)) 与 ROLLUP (a, b) 完全不同：外层括号把 (a, b) 当成一个整体列组，因此只产生「整体小计」和「总计」，不产生只按 a 的小计。

嵌套 ROLLUP 可以表达「先算外层层级，再在每层内算内层层级」：

GROUP BY ROLLUP (a, ROLLUP (b, c))
        ≡ GROUP BY GROUPING SETS ((a, b, c), (a, b), (a))
        注意：这里没有 ()

内层 ROLLUP (b, c) 贡献 (b,c)、(b)；外层 a 是固定前缀，只在最后追加一次剥离 a 的机会——但由于内层已经把自己剥离到底，最终结果是 ((a,b,c),(a,b),(a))，不含总计行。若想要总计，需要显式写成 ROLLUP (a, ROLLUP (b, c)), ()。

语法与用法

-- 基本形态：按层级逐级上卷
SELECT 维度列..., 聚合函数(...)
FROM 表
GROUP BY ROLLUP (层级列1, 层级列2, 层级列3)
ORDER BY GROUPING(层级列1), 层级列1,
         GROUPING(层级列2), 层级列2,
         GROUPING(层级列3), 层级列3;

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

-- 销售明细表：时间层级 年/月/日 + 组织层级 大区/省/城市
CREATE TABLE sales (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sale_year  SMALLINT       NOT NULL,
  sale_month SMALLINT       NOT NULL CHECK (sale_month BETWEEN 1 AND 12),
  sale_day   SMALLINT       NOT NULL CHECK (sale_day BETWEEN 1 AND 31),
  region     TEXT           NOT NULL,   -- 大区
  province   TEXT           NOT NULL,   -- 省
  city       TEXT           NOT NULL,   -- 市
  category   TEXT           NOT NULL,
  qty        INTEGER        NOT NULL CHECK (qty > 0),
  amount     NUMERIC(12, 2) NOT NULL CHECK (amount >= 0)
);

-- 造数：2025—2026 两年 × 4 大区 × 每大区 2 省 × 每省 3 市
INSERT INTO sales (sale_year, sale_month, sale_day, region, province, city, category, qty, amount)
SELECT
  2025 + (i % 2),
  1 + (i % 12),
  1 + (i % 28),
  (ARRAY['华东', '华南', '华北', '西南'])[1 + (i % 4)],
  (ARRAY['江苏', '浙江', '广东', '福建', '山东', '河北', '四川', '云南'])[1 + (i % 8)],
  (ARRAY['南京', '苏州', '杭州', '宁波', '广州', '深圳', '济南', '青岛',
          '石家庄', '唐山', '成都', '昆明'])[1 + (i % 12)],
  (ARRAY['手机', '电脑', '家电', '配件'])[1 + (i % 4)],
  1 + (i % 20),
  round((random() * 2000 + 100)::numeric, 2)
FROM generate_series(1, 40000) AS i;

CREATE INDEX idx_sales_year_month  ON sales (sale_year, sale_month);
CREATE INDEX idx_sales_region_prov ON sales (region, province, city);

ANALYZE sales;

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

-- 一行 ROLLUP 产出：年×月明细 + 年小计 + 总计
SELECT
  sale_year,
  sale_month,
  count(*)    AS 订单数,
  sum(amount) AS 销售额
FROM sales
GROUP BY ROLLUP (sale_year, sale_month)
ORDER BY GROUPING(sale_year), sale_year,
         GROUPING(sale_month), sale_month;

预期输出（截取）：

 sale_year | sale_month | 订单数 |  销售额
-----------+------------+--------+-----------
      2025 |          1 |    141 | 152340.11
      2025 |          2 |    138 | 148921.55
      ...  |        ... |    ... |       ...
      2025 |            |   1667 | 1801233.42   <- 2025 年小计（sale_month 为 NULL）
      2026 |          1 |    145 | 156780.09
      ...  |        ... |    ... |       ...
      2026 |            |   1667 | 1810456.88   <- 2026 年小计
           |            |   3334 | 3611690.30   <- 总计

详细说明

层级语义：为什么是「向左逐级减少」

ROLLUP 的括号内列顺序即层级顺序，从左到右由粗到细。上例中 ROLLUP (sale_year, sale_month) 表示「年包含月」，因此上卷路径是：

(年, 月)  →  (年)  →  ()
 最细粒度     年小计    总计

如果把顺序写反成 ROLLUP (sale_month, sale_year)，语义就变成「月包含年」——分组集成了 (月, 年)、(月)、()，其中 (月) 表示「所有年份里 1 月的合计」。这在业务上通常毫无意义，属于最常见的方向写反错误。

-- 错误！层级顺序颠倒，小计行语义失真（跨年份的「1 月合计」）
SELECT sale_month, sale_year, sum(amount)
FROM sales
GROUP BY ROLLUP (sale_month, sale_year);

识别小计行：GROUPING() 与 NULL 判断

小计行的低层级列显示为 NULL。但若原始数据里本身就可能出现 NULL（例如 city 允许为空），仅凭 IS NULL 无法区分「小计」和「真实空值」。因此判别小计行应统一使用 GROUPING()：

-- 用 GROUPING() 给每一行打上「粒度标签」
SELECT
  sale_year,
  sale_month,
  CASE
    WHEN GROUPING(sale_year) = 1 THEN '总计'
    WHEN GROUPING(sale_month) = 1 THEN '年小计'
    ELSE '月度明细'
  END         AS 行类型,
  sum(amount) AS 销售额
FROM sales
GROUP BY ROLLUP (sale_year, sale_month)
ORDER BY GROUPING(sale_year), sale_year,
         GROUPING(sale_month), sale_month;

GROUPING(col) 返回 1 表示该列未参与当前分组集（即被上卷掉了），返回 0 表示参与了分组。详见 GROUPING / GROUPING_ID。

另一种轻量做法是判断「更高层级列是否为 NULL」——因为 ROLLUP 保证上卷是前缀式的，若 sale_year 为 NULL 则一定是总计行；但一旦列本身可能有空值就不安全，不推荐作为唯一判据。

稳定排序：ORDER BY GROUPING(...), 列

直接 ORDER BY sale_year, sale_month 会把 NULL 排序到末尾（PostgreSQL 中 NULL 默认最大），导致总计行跑到最后、年小计跑到各年之后，层级结构被打散。正确做法是先按 GROUPING() 排，再按列值排：

-- 推荐：GROUPING() 在前，保证小计紧跟在它的子行之后
ORDER BY GROUPING(sale_year), sale_year,
         GROUPING(sale_month), sale_month;

输出顺序为：所有年度明细 → 2025 小计 → 2026 小计 → 总计？实际上由于 sale_year 的比较在 GROUPING(sale_year) 之后，2025 的月度明细按 sale_year=2025 排序，2025 小计行 sale_year=2025 相同、GROUPING(sale_month)=1 排在该年明细之后，然后才轮到 2026。总计行 sale_year IS NULL 在 GROUPING(sale_year)=1 的分组里排最后。这正是报表想要的顺序。

与 FILTER / CASE 配合：只保留小计行

有时业务只要小计与总计，不要最细的明细层。除了改用 GROUPING SETS 精确列举，也可以在 SELECT 里用 CASE 把明细行折叠掉——但更简洁的是直接收缩 ROLLUP 的列：

-- 只要「年小计 + 总计」，去掉月明细：ROLLUP 列从两列减到一列
SELECT sale_year, sum(amount) AS 销售额
FROM sales
GROUP BY ROLLUP (sale_year)
ORDER BY GROUPING(sale_year), sale_year;

如果坚持保留 ROLLUP (sale_year, sale_month) 但只想输出汇总行，就在外层过滤：

-- 外层包裹 + WHERE 过滤：不能在同一个查询的 WHERE 里用 GROUPING()
SELECT *
FROM (
  SELECT
    sale_year,
    sale_month,
    GROUPING(sale_year)  AS g_year,
    GROUPING(sale_month) AS g_month,
    sum(amount)          AS 销售额
  FROM sales
  GROUP BY ROLLUP (sale_year, sale_month)
) t
WHERE g_year = 1 OR g_month = 1     -- 只留小计与总计
ORDER BY g_year, sale_year, g_month, sale_month;

再配合聚合过滤函数 FILTER，还能实现「小计只统计符合条件的子集」：

SELECT
  sale_year,
  count(*)                                    AS 全部订单,
  count(*) FILTER (WHERE category = '手机')    AS 手机订单,
  sum(amount) FILTER (WHERE category = '手机') AS 手机销售额
FROM sales
GROUP BY ROLLUP (sale_year)
ORDER BY GROUPING(sale_year), sale_year;

执行计划中的表现

ROLLUP 在解析阶段就被改写成等价的 GROUPING SETS，因此计划形态与 GROUPING SETS 完全一致：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT sale_year, sale_month, sum(amount)
FROM sales
GROUP BY ROLLUP (sale_year, sale_month);

             QUERY PLAN
-------------------------------------
 GroupAggregate
   Group Key: sale_year, sale_month
   Group Key: sale_year
   Group Key: ()
   ->  Index Scan using idx_sales_year_month on sales
         Buffers: shared hit=214
 Planning Time: 0.312 ms
 Execution Time: 18.744 ms

关键信号是 GroupAggregate 下重复出现多个 Group Key:——每个 Group Key 行对应一个分组集。当分组集不适合走索引顺序时，优化器可能改选 MixedAggregate 或 HashAggregate：

 MixedAggregate
   Hash Key: sale_year
   Hash Key: <none>
   Group Key: sale_year, sale_month
   ->  Seq Scan on sales

若出现 HashAggregate 且计划里带 Batches: N（N > 1），说明哈希表溢出了 work_mem 并落盘，此时应适当调大 work_mem。

行数上限与内存消耗

ROLLUP 的结果行数 = 各分组集行数之和，最坏情况接近「明细行数 + 各层级分组数」。维度基数越高，汇总行越多：

层级列基数	分组集个数	最坏结果行数量级
2	3	明细 + a + 1
3	4	明细 + ab + a + 1
5	6	明细 + abcd + abc + ab + a + 1

真正吃内存的是分组集的中间哈希表。当某列基数达数十万时，单是 (a, b) 这一层的哈希表就可能远超 work_mem。经验规则：ROLLUP 的列数控制在 4 层以内，基数高的列（如 user_id、order_no）不要放进 ROLLUP。

常见误用

-- 错误！以为 ROLLUP (a, b) 会生成 (b) 这一层
-- 实际只有 (a,b)、(a)、()，没有 (b)
SELECT city, category, sum(amount)
FROM sales
GROUP BY ROLLUP (city, category);

-- 想要 (b) 层必须显式补上
SELECT city, category, sum(amount)
FROM sales
GROUP BY GROUPING SETS ((city, category), (city), (category), ());

-- 错误！把高基数列塞进 ROLLUP，结果行数爆炸且无业务意义
SELECT order_id, city, sum(amount)
FROM sales
GROUP BY ROLLUP (order_id, city);

-- 不推荐：用 UNION ALL 手写层级，扫描多次且容易漏层
SELECT city, category, sum(amount) FROM sales GROUP BY city, category
UNION ALL
SELECT city, NULL, sum(amount) FROM sales GROUP BY city
UNION ALL
SELECT NULL, NULL, sum(amount) FROM sales;

实战示例

步骤 1：时间层级报表（年 → 月 → 日）

-- 三层 ROLLUP：4 个分组集
SELECT
  sale_year,
  sale_month,
  sale_day,
  CASE
    WHEN GROUPING(sale_day) = 1 AND GROUPING(sale_month) = 1 THEN '年小计'
    WHEN GROUPING(sale_day) = 1 THEN '月小计'
    ELSE '日明细'
  END             AS 层级,
  count(*)        AS 订单数,
  sum(amount)     AS 销售额
FROM sales
WHERE sale_year = 2026          -- 限定单年，避免结果过大
GROUP BY ROLLUP (sale_year, sale_month, sale_day)
ORDER BY GROUPING(sale_year), sale_year,
         GROUPING(sale_month), sale_month,
         GROUPING(sale_day), sale_day
LIMIT 30;

 sale_year | sale_month | sale_day |  层级  | 订单数 | 销售额
-----------+------------+----------+--------+--------+---------
      2026 |          1 |        1 | 日明细 |      5 | 5541.20
      2026 |          1 |        2 | 日明细 |      4 | 4390.77
      2026 |          1 |          | 月小计 |    145 | 156780.09

步骤 2：组织层级报表（大区 → 省 → 市）

-- 三层组织层级 + 只保留汇总行
SELECT
  region,
  province,
  city,
  CASE
    WHEN GROUPING(region)  = 1 THEN '全国总计'
    WHEN GROUPING(province) = 1 THEN '大区小计'
    WHEN GROUPING(city)     = 1 THEN '省小计'
    ELSE '城市明细'
  END             AS 层级,
  count(DISTINCT province) AS 覆盖省数,
  sum(amount)     AS 销售额
FROM sales
GROUP BY ROLLUP (region, province, city)
HAVING GROUPING(city) = 1 OR GROUPING(province) = 1   -- 丢掉城市明细行
ORDER BY GROUPING(region), region,
         GROUPING(province), province,
         GROUPING(city), city;

 region | province | city |   层级   | 覆盖省数 |  销售额
--------+----------+------+----------+----------+-----------
 华东   | 江苏     |      | 省小计   |        1 |  451233.10
 华东   |          |      | 大区小计 |        2 |  902188.44
 华南   | 广东     |      | 省小计   |        1 |  448901.55
 华南   |          |      | 大区小计 |        2 |  897201.09
        |          |      | 全国总计 |        8 | 3611690.30

步骤 3：与 UNION ALL 的结果一致性自检

用 EXCEPT ALL 双向比对，确保改写后语义完全等价：

-- ROLLUP 版本
WITH r AS (
  SELECT region, province, city, sum(amount) AS amt
  FROM sales
  GROUP BY ROLLUP (region, province, city)
),
-- 手写 UNION ALL 版本
u AS (
            SELECT region, province, city, sum(amount) FROM sales GROUP BY region, province, city
  UNION ALL SELECT region, province, NULL, sum(amount) FROM sales GROUP BY region, province
  UNION ALL SELECT region, NULL,     NULL, sum(amount) FROM sales GROUP BY region
  UNION ALL SELECT NULL,   NULL,     NULL, sum(amount) FROM sales
)
SELECT '仅出现在 ROLLUP' AS 差异, * FROM (SELECT * FROM r EXCEPT ALL SELECT * FROM u) x
UNION ALL
SELECT '仅出现在 UNION ALL', * FROM (SELECT * FROM u EXCEPT ALL SELECT * FROM r) y;

 差异 | region | province | city | amt
------+--------+----------+------+-----
(0 rows)     -- 两个方向都无差集，证明改写等价

步骤 4：性能对照

-- ROLLUP：单次扫描
EXPLAIN (ANALYZE, BUFFERS)
SELECT region, province, sum(amount)
FROM sales
GROUP BY ROLLUP (region, province);

 GroupAggregate  (cost=0.29..3892.11 rows=41 width=...) (actual time=0.041..31.882 rows=41 loops=1)
   Group Key: region, province
   Group Key: region
   Group Key: ()
   Buffers: shared hit=214
 Execution Time: 31.882 ms

-- UNION ALL：三次扫描
EXPLAIN (ANALYZE, BUFFERS)
SELECT region, province, sum(amount) FROM sales GROUP BY region, province
UNION ALL
SELECT region, NULL, sum(amount) FROM sales GROUP BY region
UNION ALL
SELECT NULL, NULL, sum(amount) FROM sales;

 Append  (actual time=35.114..101.770 rows=41 loops=1)
   ->  GroupAggregate  (actual time=0.030..35.114  rows=32 loops=1)   <- 第一次扫描
   ->  GroupAggregate  (actual time=0.024..33.201  rows=8  loops=1)   <- 第二次扫描
   ->  Aggregate       (actual time=0.019..31.442  rows=1  loops=1)   <- 第三次扫描
 Buffers: shared hit=642      -- 约为 ROLLUP 的 3 倍
 Execution Time: 101.770 ms   -- 约为 ROLLUP 的 3.2 倍

指标	ROLLUP	三个 GROUP BY + UNION ALL	相对提升
执行时间	31.9 ms	101.8 ms	3.2×
共享缓冲命中	214	642	3.0×
基表扫描次数	1	3	—

注意事项

1. 列顺序即层级顺序，写反会让小计行语义完全改变：

   -- 错误！层级方向反了
   GROUP BY ROLLUP (sale_month, sale_year)

2. ROLLUP (a, b) 不产生 (b) 层。需要「按 b 的小计」必须显式用 GROUPING SETS 补上。
3. ROLLUP ((a, b)) ≠ ROLLUP (a, b)。前者把 (a, b) 当整体，只产生 2 个分组集。
4. ROLLUP (a), ROLLUP (b) 是笛卡尔积，产生 4 个分组集，容易误以为还是 N+1 个：

   -- 不推荐：多数场景这并不是你要的
   GROUP BY ROLLUP (region), ROLLUP (category)

5. 不要在 WHERE 里用 GROUPING()，它只能在 HAVING 或外层查询中使用：

   -- 错误！WHERE 在聚合之前求值
   SELECT region, sum(amount) FROM sales
   WHERE GROUPING(region) = 1
   GROUP BY ROLLUP (region);
   
   -- 正确：用 HAVING
   SELECT region, sum(amount) FROM sales
   GROUP BY ROLLUP (region)
   HAVING GROUPING(region) = 1;

6. 不要用 IS NULL 单独判别小计行，原始列含空值时会产生歧义，优先用 GROUPING()。
7. 高基数列不要放进 ROLLUP，会让分组集中间结果爆炸、HashAggregate 落盘。
8. 嵌套 ROLLUP 不含 ()：ROLLUP (a, ROLLUP (b, c)) 只有 ((a,b,c),(a,b),(a))，需要总计要额外补 ()。
9. ROLLUP 需要 PostgreSQL 9.5+；更早版本只能用 GROUPING SETS 之外的 UNION ALL 手写。

最佳实践

1. 层级列按「粗 → 细」排列，与业务维度层次保持一致，并在 SELECT 注释里写明层级。
2. 统一用 CASE WHEN GROUPING(...) = 1 生成中文层级标签，让结果可直接交付业务方。
3. 排序一律 ORDER BY GROUPING(x), x，保证小计紧跟其明细，总计在最后。
4. 能用 ROLLUP 就不用 UNION ALL：单次扫描、少写重复分支、少一类漏层 bug。
5. 列数控制在 4 层以内，超过时改用 GROUPING SETS 精确指定真正需要的分组集。
6. 只输出汇总层时用 HAVING GROUPING(...) = 1，比在应用层过滤更省传输。
7. 改写后用 EXCEPT ALL 做回归验证，确保与旧 UNION ALL 结果零差异。
8. 用 EXPLAIN (ANALYZE, BUFFERS) 复核：确认 Group Key 行数等于分组集个数、Buffers 未随分组集线性增长。
9. 基表为大表时先建复合索引（如 (sale_year, sale_month)），让 GroupAggregate 直接复用索引顺序，避免额外 Sort。

下一节

继续学习：CUBE

参考链接

• GROUPING SETS / ROLLUP / CUBE
• Aggregate Expressions
• Aggregate Functions
• SELECT — GROUP BY Clause
• Using EXPLAIN