复杂聚合

问题

如何使用 GROUPING SETS、ROLLUP、CUBE 实现多维度聚合分析？

答案

在做数据报表时，我们经常需要在同一个查询中产生多种分组方式的汇总结果。例如，一张销售报表既要看"每个区域每个月的销售额"，又要看"每个区域的年度总额"，还要看"每个月的全公司总额"，以及"总计"。

普通 GROUP BY 一次只能按一种方式分组。为了满足多维汇总需求，SQL 提供了三个高级聚合操作符：

操作符	作用	类比
GROUPING SETS	指定多组分组方式	自定义多个 GROUP BY
ROLLUP	按层级逐级汇总（小计→总计）	Excel 分类汇总
CUBE	所有维度的全组合汇总	数据透视表

数据库支持

PostgreSQL、SQL Server、Oracle 完整支持。MySQL 8.0+ 支持 ROLLUP，但不支持 GROUPING SETS 和 CUBE（需要用 UNION ALL 模拟）。

---

准备示例数据

CREATE TABLE sales (
    region VARCHAR(20),
    product VARCHAR(20),
    quarter VARCHAR(5),
    amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES 
('华东', '手机', 'Q1', 50000),
('华东', '手机', 'Q2', 55000),
('华东', '电脑', 'Q1', 30000),
('华东', '电脑', 'Q2', 35000),
('华北', '手机', 'Q1', 40000),
('华北', '手机', 'Q2', 42000),
('华北', '电脑', 'Q1', 25000),
('华北', '电脑', 'Q2', 28000);

---

GROUPING SETS

手动指定需要哪几种分组方式。

语法

SELECT region, product, SUM(amount)
FROM sales
GROUP BY GROUPING SETS (
    (region, product),  -- 按区域+产品分组
    (region),           -- 按区域分组
    (product),          -- 按产品分组
    ()                  -- 总计（不分组）
);

执行结果

region	product	sum
华东	手机	105000
华东	电脑	65000
华北	手机	82000
华北	电脑	53000
华东	NULL	170000
华北	NULL	135000
NULL	手机	187000
NULL	电脑	118000
NULL	NULL	305000

理解 NULL 的含义

结果中的 NULL 表示该维度被聚合了。例如 region=华东, product=NULL 表示华东区域所有产品的合计。region=NULL, product=NULL 表示全部数据的总计。

等价的 UNION ALL 写法

-- 不支持 GROUPING SETS 的数据库（如 MySQL 8.0）可以用 UNION ALL 模拟
SELECT region, product, SUM(amount) FROM sales GROUP BY region, product
UNION ALL
SELECT region, NULL, SUM(amount) FROM sales GROUP BY region
UNION ALL
SELECT NULL, product, SUM(amount) FROM sales GROUP BY product
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM sales;

UNION ALL 模拟的缺点

1. 每个分组方式需要扫描一次表，性能比 GROUPING SETS 差
2. SQL 代码冗长，维护困难
3. 无法使用 GROUPING() 函数区分"值本身是 NULL"和"聚合产生的 NULL"

---

ROLLUP

按维度列表从右到左逐级去掉维度，产生层级小计和总计。

语法

SELECT region, product, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, product);

ROLLUP 展开逻辑

ROLLUP(region, product) 等价于：

GROUPING SETS (
    (region, product),  -- 最细粒度
    (region),           -- 去掉最右边的 product → 区域小计
    ()                  -- 全部去掉 → 总计
)

执行结果

region	product	sum
华东	手机	105000
华东	电脑	65000
华东	NULL	170000
华北	手机	82000
华北	电脑	53000
华北	NULL	135000
NULL	NULL	305000

ROLLUP 的典型场景

适合有层级关系的维度汇总，如：

• ROLLUP(年, 季度, 月) → 月度数据 + 季度小计 + 年度小计 + 总计
• ROLLUP(国家, 省, 市) → 市级数据 + 省级小计 + 国家小计 + 总计
• ROLLUP(部门, 岗位) → 岗位数据 + 部门小计 + 总计

部分 ROLLUP

只对部分列做 ROLLUP：

-- region 始终保留，只对 product 和 quarter 做层级汇总
SELECT region, product, quarter, SUM(amount)
FROM sales
GROUP BY region, ROLLUP(product, quarter);

---

CUBE

产生所有维度的全部组合。

语法

SELECT region, product, SUM(amount)
FROM sales
GROUP BY CUBE (region, product);

CUBE 展开逻辑

CUBE(region, product) 等价于：

GROUPING SETS (
    (region, product),  -- 两个维度
    (region),           -- 只有 region
    (product),          -- 只有 product
    ()                  -- 无维度（总计）
)

对比 ROLLUP 只有 3 组，CUBE 有 4 组（$2^n$ 种组合，n 为维度数）。

三维 CUBE 示例

-- 3 个维度的 CUBE 产生 2^3 = 8 种分组
SELECT region, product, quarter, SUM(amount)
FROM sales
GROUP BY CUBE (region, product, quarter);

-- 等价于 GROUPING SETS (
--     (region, product, quarter),
--     (region, product),
--     (region, quarter),
--     (product, quarter),
--     (region),
--     (product),
--     (quarter),
--     ()
-- )

---

GROUPING 函数

区分结果中的 NULL 是"原始数据的 NULL"还是"聚合产生的 NULL"。

SELECT 
    region,
    product,
    SUM(amount) AS total,
    -- GROUPING() 返回 1 表示该列被聚合了（不参与分组）
    GROUPING(region) AS is_region_aggregated,
    GROUPING(product) AS is_product_aggregated
FROM sales
GROUP BY ROLLUP (region, product);

region	product	total	is_region_aggregated	is_product_aggregated
华东	手机	105000	0	0
华东	电脑	65000	0	0
华东	NULL	170000	0	1
华北	手机	82000	0	0
华北	NULL	135000	0	1
NULL	NULL	305000	1	1

利用 GROUPING 美化输出

SELECT 
    CASE WHEN GROUPING(region) = 1 THEN '【全部区域】' ELSE region END AS region,
    CASE WHEN GROUPING(product) = 1 THEN '【全部产品】' ELSE product END AS product,
    SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product);

输出更直观：

region	product	total
华东	手机	105000
华东	电脑	65000
华东	【全部产品】	170000
华北	手机	82000
华北	电脑	53000
华北	【全部产品】	135000
【全部区域】	【全部产品】	305000

---

三者对比

特性	GROUPING SETS	ROLLUP	CUBE
分组方式	手动指定	层级递减	全排列
灵活性	最高	中等	最低
分组数量	自定义	n+1	$2^n$
典型场景	自定义报表	层级汇总	多维分析
MySQL 支持	❌	✅ 8.0+	❌
PostgreSQL	✅	✅	✅

面试记忆口诀

• ROLLUP = 卷起来 → 从细到粗逐级汇总（右到左去掉维度）
• CUBE = 立方体 → 所有面、边、顶点的组合
• GROUPING SETS = 自选套餐 → 想要哪些分组就写哪些

---

数据分析实战场景

场景一：销售日报中的多级汇总

-- 需求：按区域-产品-日期的三级汇总报表
-- 每个层级都有小计，最后有总计
SELECT 
    CASE WHEN GROUPING(region) = 1 THEN '总计'
         WHEN GROUPING(product) = 1 THEN CONCAT(region, ' 小计')
         ELSE region END AS region_display,
    CASE WHEN GROUPING(product) = 1 THEN '-' ELSE product END AS product,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count,
    ROUND(AVG(amount), 2) AS avg_amount
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY 
    GROUPING(region), region,
    GROUPING(product), product;

场景二：BI 报表的多维分析

-- 需求：同时查看渠道维度、地区维度和交叉维度的数据
-- 适合用 CUBE 或 GROUPING SETS
SELECT 
    COALESCE(channel, '全部渠道') AS channel,
    COALESCE(region, '全部区域') AS region,
    COUNT(*) AS orders,
    SUM(amount) AS revenue,
    COUNT(DISTINCT user_id) AS buyers
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY CUBE (channel, region)
ORDER BY 
    GROUPING(channel), channel,
    GROUPING(region), region;

场景三：MySQL 中模拟 CUBE 的做法

-- MySQL 不支持 CUBE，用 WITH ROLLUP + UNION ALL 变通
-- 方案：两次 ROLLUP 取并集
SELECT region, product, SUM(amount) AS total, 'by region>product' AS group_type
FROM sales GROUP BY region, product WITH ROLLUP
UNION ALL
SELECT region, product, SUM(amount), 'by product>region'
FROM sales GROUP BY product, region WITH ROLLUP
-- 去除重复的总计行
HAVING NOT (region IS NULL AND product IS NULL);

场景四：GROUPING_ID 快速区分聚合层级

-- PostgreSQL 支持 GROUPING() 返回位掩码
-- GROUPING(region, product) 返回一个 2-bit 的数值
SELECT 
    region, product, SUM(amount),
    GROUPING(region, product) AS gid
    -- gid=0: 明细行 (region✓, product✓)
    -- gid=1: 区域小计 (region✓, product✗)
    -- gid=2: 产品小计 (region✗, product✓)
    -- gid=3: 总计 (region✗, product✗)
FROM sales
GROUP BY CUBE (region, product)
ORDER BY gid, region, product;

---

常见面试问题

Q1: ROLLUP 和 CUBE 有什么区别？

答案：

核心区别在于产生的分组数量：

• ROLLUP(A, B, C) 产生 4 组：(A,B,C), (A,B), (A), () → 层级递减，n+1 组
• CUBE(A, B, C) 产生 8 组：所有子集 → $2^n$ 组

ROLLUP 适合有层级关系的维度（年→季度→月），CUBE 适合平行维度的全组合分析。

Q2: MySQL 不支持 GROUPING SETS，怎么模拟？

答案：

用 UNION ALL 拼接多个 GROUP BY 查询：

-- 模拟 GROUPING SETS ((region, product), (region), ())
SELECT region, product, SUM(amount) FROM sales GROUP BY region, product
UNION ALL
SELECT region, NULL, SUM(amount) FROM sales GROUP BY region
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM sales;

缺点：多次扫描表，性能差；无法使用 GROUPING() 函数。如果需要复杂多维分析，建议使用 PostgreSQL 或在应用层处理。

Q3: 如何区分 NULL 是原始值还是聚合产生的？

答案：

使用 GROUPING() 函数：

SELECT 
    region, product,
    GROUPING(region) AS rg,    -- 1=聚合产生的NULL, 0=正常值
    GROUPING(product) AS pg,
    SUM(amount)
FROM sales
GROUP BY ROLLUP (region, product);

在 GROUPING() 返回 1 时，该列的 NULL 是"被聚合了"的含义，而非数据本身为空。

Q4: 写一个带有小计和总计的报表查询

答案：

-- 部门-岗位的薪资报表，带小计和总计
SELECT
    COALESCE(
        CASE WHEN GROUPING(department) = 1 THEN '===总计===' END,
        CASE WHEN GROUPING(position) = 1 THEN department || ' 小计' END,
        department
    ) AS department,
    CASE WHEN GROUPING(position) = 1 THEN '' ELSE position END AS position,
    COUNT(*) AS headcount,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY ROLLUP (department, position)
ORDER BY
    GROUPING(department),   -- 总计排最后
    department,
    GROUPING(position),     -- 小计排在明细后面
    position;

Q5: ROLLUP 和 ORDER BY 组合时有什么注意事项？

答案：

ROLLUP 结果中混合了明细行、小计行和总计行，直接 ORDER BY 排序列会让小计行混入明细之间，阅读体验很差。

问题示例：

-- ❌ 直接 ORDER BY region，小计行会乱入明细中
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY region;  -- NULL（总计行）会排最前或最后，取决于数据库

正确做法：将 GROUPING() 函数加入 ORDER BY，让小计行始终出现在对应明细行之后，总计行排在最末：

-- ✅ 明细行先按维度排序，小计行排在对应组的最后，总计行排最末
SELECT
    CASE WHEN GROUPING(region)  = 1 THEN '【总计】'      ELSE region  END AS region,
    CASE WHEN GROUPING(product) = 1 THEN '  └─小计'      ELSE product END AS product,
    SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY
    GROUPING(region),   -- 0（明细/区域小计）在前，1（总计）在后
    region,             -- 区域名称字母序
    GROUPING(product),  -- 0（明细行）在前，1（区域小计行）在后
    product;            -- 产品名称字母序

排序结果结构：

华东 | 电脑    | 65000   ← 明细
华东 | 手机    | 105000  ← 明细
华东 | └─小计  | 170000  ← 区域小计（在华东所有明细之后）
华北 | 电脑    | 53000   ← 明细
华北 | 手机    | 82000   ← 明细
华北 | └─小计  | 135000  ← 区域小计
【总计】| └─小计 | 305000 ← 总计排最后

记忆技巧

ORDER BY 中 GROUPING(列) 放在该列排序之前，GROUPING = 0 的行（更细粒度）先排，GROUPING = 1 的行（小计/总计）后排。维度越粗，越往后排。

Q6: 面试题——用 ROLLUP 实现一张包含分类小计和总计的销售报表

需求：基于 sales 表，生成按区域和产品汇总的销售报表，要求：

1. 显示每个区域每种产品的销售额
2. 每个区域有一行小计
3. 最后有一行总计
4. NULL 显示为可读的标签而非空白

答案：

SELECT
    -- 用 GROUPING() 把 NULL 替换为可读标签
    CASE
        WHEN GROUPING(region) = 1  THEN '【全部区域合计】'
        WHEN GROUPING(product) = 1 THEN CONCAT(region, ' 小计')
        ELSE region
    END AS region,
    CASE
        WHEN GROUPING(product) = 1 THEN '—'
        ELSE product
    END AS product,
    SUM(amount)        AS total_amount,
    COUNT(*)           AS order_count,
    ROUND(AVG(amount), 2) AS avg_amount,
    -- 用 GROUPING() 判断当前行是否为小计/总计，方便前端标记样式
    GROUPING(region)   AS is_grand_total,
    GROUPING(product)  AS is_subtotal
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY
    GROUPING(region),   -- 总计行最后
    region,
    GROUPING(product),  -- 小计行在明细之后
    product;

输出结果：

region	product	total_amount	order_count	avg_amount	is_grand_total	is_subtotal
华东	电脑	65000	2	32500.00	0	0
华东	手机	105000	2	52500.00	0	0
华东 小计	—	170000	4	42500.00	0	1
华北	电脑	53000	2	26500.00	0	0
华北	手机	82000	2	41000.00	0	0
华北 小计	—	135000	4	33750.00	0	1
【全部区域合计】	—	305000	8	38125.00	1	1

实际应用

is_grand_total 和 is_subtotal 这两列通常不直接展示给用户，而是由前端或 BI 工具根据这两列的值，将小计行和总计行渲染为不同的背景色或字体加粗，提升报表可读性。

Q7: 什么场景下 CUBE 比 ROLLUP 更合适？

答案：

选择哪个取决于维度之间是否存在层级关系：

适合 ROLLUP 的场景——维度之间有固定的从属层级（上级包含下级）：

-- 时间维度有严格层级：年 > 季度 > 月
-- 地域维度有严格层级：国家 > 省 > 市
SELECT year, quarter, month, SUM(revenue)
FROM sales
GROUP BY ROLLUP (year, quarter, month);
-- 产生：月度明细 + 季度小计 + 年度小计 + 总计
-- 不需要"月份跨季度"的汇总，ROLLUP 足够

适合 CUBE 的场景——维度之间相互独立（平行维度），任意组合都有分析意义：

-- 渠道、地区、产品三者互相独立，每种组合都有业务含义
-- "华东的手机销售额"、"APP 渠道的销售额"、"手机整体销售额"……
SELECT channel, region, product, SUM(revenue)
FROM sales
GROUP BY CUBE (channel, region, product);
-- 产生 2^3 = 8 种分组，覆盖所有组合
-- BI 工具可以任意切换维度做交叉分析

对比总结：

维度关系	推荐	理由
层级关系（年→季度→月）	ROLLUP	只需要同向逐级汇总，CUBE 会产生无意义的组合
平行关系（渠道 × 地区）	CUBE	每种维度组合都有分析价值
部分需要	GROUPING SETS	手动指定只需要的几种分组，避免产生无意义的组合

CUBE 的性能代价

CUBE(A, B, C) 产生 $2^3 = 8$ 种分组，CUBE(A, B, C, D) 产生 16 种。维度数越多，扫描次数越多。超过 4 个维度的 CUBE 要谨慎使用，建议改用 GROUPING SETS 只保留需要的组合。

Q8: 面试题——MySQL 不支持 GROUPING SETS，如何只获取"部分维度的小计"？

场景：在 MySQL（不支持 GROUPING SETS）中，需要一张报表同时展示：

1. 按 region + product 的明细
2. 按 region 的小计（不需要按 product 的小计和总计）

直接用 GROUP BY region, product WITH ROLLUP 会额外产生按 region 的小计和整体总计，但不需要整体总计。

答案：

用 UNION ALL 分别执行需要的 GROUP BY，只合并真正需要的那几种分组：

-- 方案：UNION ALL 合并多个独立 GROUP BY，精确控制需要哪些分组层级

-- 第 1 部分：明细行（region + product 明细）
SELECT
    region,
    product,
    SUM(amount) AS total,
    0           AS is_subtotal   -- 标记：明细行
FROM sales
GROUP BY region, product

UNION ALL

-- 第 2 部分：region 小计行（只要区域小计，不要总计）
SELECT
    region,
    '【小计】'  AS product,
    SUM(amount) AS total,
    1           AS is_subtotal   -- 标记：小计行
FROM sales
GROUP BY region

ORDER BY region, is_subtotal, product;  -- 小计行排在明细之后

输出结果：

region	product	total	is_subtotal
华东	电脑	65000	0
华东	手机	105000	0
华东	【小计】	170000	1
华北	电脑	53000	0
华北	手机	82000	0
华北	【小计】	135000	1

如果同时还需要总计行，再追加一个 UNION ALL：

-- 追加总计行
UNION ALL
SELECT
    '【总计】' AS region,
    ''         AS product,
    SUM(amount) AS total,
    2           AS is_subtotal
FROM sales

与 WITH ROLLUP 的对比

方案	适用场景	缺点
WITH ROLLUP	需要完整的层级小计链（明细→小计→总计）	无法跳过中间层级
UNION ALL	只需要其中某几个层级的汇总	多次扫描表，SQL 冗长
GROUPING SETS（PostgreSQL）	精确指定任意组合	MySQL 不支持

在 MySQL 中，如果只需要"一级小计 + 总计"（不需要更细的层级），WITH ROLLUP 更简洁；如果只需要"一级小计但不要总计"，用 UNION ALL 并排除总计行更灵活。

---

相关链接

• PostgreSQL GROUPING SETS
• MySQL GROUP BY 修饰符
• SQL ROLLUP 教程
• 聚合函数与 GROUP BY - 基础聚合操作
• 窗口函数 - 不减少行数的聚合方式
• 行列转换 - 另一种维度展示方式