ROLLUP 与 CUBE 的作用？多维汇总场景怎么用？

考察要点

这道题面试官想看你是否掌握了 SQL 中用于多维数据分析的高级聚合功能。它考察你对标准 GROUP BY 局限性的理解，以及如何使用 ROLLUP 和 CUBE 高效地生成包含小计（subtotals）和总计（grand total）的汇总报告，避免使用繁琐且低效的 UNION ALL。这体现了你处理复杂报表需求和数据仓库（Data Warehouse）场景的能力。

核心概念

ROLLUP 和 CUBE 都是 GROUP BY 子句的扩展，用于在一个查询中生成多个不同维度级别的聚合结果。它们通常与 SUM, COUNT, AVG 等聚合函数一起使用。

假设我们有以下销售数据表 sales：

 1CREATE TABLE sales (
 2    region VARCHAR(50),
 3    department VARCHAR(50),
 4    sale_amount DECIMAL(10, 2)
 5);
 6
 7INSERT INTO sales VALUES
 8('华东', '家电', 1000),
 9('华东', '家电', 1500),
10('华东', '数码', 800),
11('华北', '家电', 900),
12('华北', '数码', 1200),
13('华北', '数码', 1300);

1. 标准 GROUP BY

只按所有指定列进行唯一分组，不产生任何小计或总计。

 1SELECT region, department, SUM(sale_amount) AS total_sales
 2FROM sales
 3GROUP BY region, department;

输出结果：

2. ROLLUP：分级汇总

ROLLUP 生成具有层级关系的聚合结果。它会从右至左依次去掉 ROLLUP 列表中的列，并对每个级别进行聚合，最后生成总计。GROUP BY ROLLUP(c1, c2) 会产生以下分组集：(c1, c2), (c1), ()。

关键点：顺序很重要！ ROLLUP(a, b) 和 ROLLUP(b, a) 的结果是不同的。

 1-- 按 区域 -> 部门 的层级进行汇总
 2SELECT region, department, SUM(sale_amount) AS total_sales
 3FROM sales
 4GROUP BY ROLLUP(region, department);

输出结果（NULL 代表该级别的汇总）：

3. CUBE：全组合汇总

CUBE 生成所有可能维度组合的聚合结果。GROUP BY CUBE(c1, c2) 会产生以下分组集：(c1, c2), (c1), (c2), ()。

关键点：顺序不影响最终的分组集。 CUBE(a, b) 和 CUBE(b, a) 会产生相同的聚合级别，只是输出顺序可能不同。

 1-- 按 区域 和 部门 的所有可能组合进行汇总
 2SELECT region, department, SUM(sale_amount) AS total_sales
 3FROM sales
 4GROUP BY CUBE(region, department);

输出结果（NULL 代表该级别的汇总）：

4. GROUPING() 函数

为了区分汇总行产生的 NULL 和原始数据中的 NULL，可以使用 GROUPING() 函数。如果该列是因 ROLLUP 或 CUBE 聚合而产生的 NULL，则 GROUPING(column_name) 返回 1，否则返回 0。

 1SELECT
 2    CASE WHEN GROUPING(region) = 1 THEN '所有区域' ELSE region END AS region,
 3    CASE WHEN GROUPING(department) = 1 THEN '所有部门' ELSE department END AS department,
 4    SUM(sale_amount) AS total_sales
 5FROM sales
 6GROUP BY ROLLUP(region, department);

版本兼容性

• MySQL: 从 8.0 版本开始支持 ROLLUP, CUBE, GROUPING SETS。MySQL 5.7 及之前版本不支持。
• PostgreSQL / Oracle / SQL Server: 长期支持这些高级聚合功能。

多种解法（模拟 ROLLUP）

如果数据库版本不支持 ROLLUP（如 MySQL 5.7），可以通过 UNION ALL 手动实现。

解法 1：使用 UNION ALL 模拟 ROLLUP

思路：分别编写多个 SELECT 语句来计算每个层级的聚合，然后用 UNION ALL 将它们合并。

• 版本：✅ 所有 SQL 数据库版本通用。
• SQL 代码：

 1-- 1. 最细粒度：按 (region, department) 聚合
 2SELECT region, department, SUM(sale_amount) AS total_sales
 3FROM sales
 4GROUP BY region, department
 5
 6UNION ALL
 7
 8-- 2. 次级粒度：按 (region) 聚合
 9SELECT region, NULL AS department, SUM(sale_amount) AS total_sales
10FROM sales
11GROUP BY region
12
13UNION ALL
14
15-- 3. 总计
16SELECT NULL AS region, NULL AS department, SUM(sale_amount) AS total_sales
17FROM sales;

• 时间/空间复杂度：性能较差。假设表 sales 有 N 行，此方法需要对表进行 3 次扫描（或索引扫描），复杂度约 $O(3 \times N)$ 或 $O(3 \times N \log N)$（如果需要排序）。而数据库原生 ROLLUP 通常可以通过一次优化的扫描完成。
• 索引依赖：为了优化每个 GROUP BY，最好有一个复合索引 (region, department)。

解法 2：在应用层处理

思路：只查询最细粒度的数据，然后在应用程序（如 Python/Java/Go）中进行二次计算和汇总。

• 版本：不依赖 SQL 版本。
• SQL 代码：

 1-- 只查询最细粒度的数据
 2SELECT region, department, SUM(sale_amount) AS total_sales
 3FROM sales
 4GROUP BY region, department;

• 复杂度：数据库端压力小，网络传输数据量小。计算压力转移到应用服务器。如果结果集很大，会消耗大量应用内存。
• 适用场景：当聚合结果集不大，或者后续有复杂的业务逻辑需要在应用层处理时，这是一种常见的工程选择。

常见低分回答

1. 混淆 ROLLUP 和 CUBE：错误地认为 ROLLUP 会生成所有组合，或者 CUBE 是有层级顺序的。
2. 不知道 ROLLUP 的顺序敏感性：回答 ROLLUP(a, b) 和 ROLLUP(b, a) 结果一样。这是 CUBE 的特性，不是 ROLLUP 的。
3. 无法处理汇总行的 NULL：不知道 GROUPING() 函数的存在，当被问及如何区分真实 NULL 和汇总 NULL 时无法回答，或者只能想到用 COALESCE(region, '总计') 这种不严谨的方式。
4. 在不支持的版本上强行使用：在面试中不提及 MySQL 8.0 才支持，会显得知识体系有盲区。
5. 模拟方案效率低下：使用 UNION 代替 UNION ALL。UNION 会进行去重操作，带来不必要的性能开销，并且在聚合场景下，如果分组键不同，结果本就不会重复，UNION ALL 是正确且高效的选择。

工程实践与优化

• 性能对比：原生 ROLLUP/CUBE 远优于 UNION ALL。数据库的查询优化器可以将 ROLLUP 作为一个整体来规划执行，通常只需对数据进行一次扫描和排序/哈希处理，而 UNION ALL 会导致多次扫描。
• 执行计划分析：在 MySQL 8.0+ 中，使用 EXPLAIN 分析 ROLLUP 查询，你会在 Extra 列看到 Using rollup，这表明优化器识别并使用了 ROLLUP 的专属执行路径。
• 索引优化：对于 GROUP BY ROLLUP(c1, c2, c3)，一个 (c1, c2, c3) 的复合索引是最佳选择。数据库可以利用索引的有序性，以流式方式高效完成多级聚合，避免额外的排序（filesort）和临时表。
• 大数据量场景：当数据量达到上亿甚至几十亿级别时，即使是原生的 ROLLUP/CUBE 也可能非常慢并消耗大量内存。在这种情况下，应考虑：
  • 预聚合：创建并定时更新物化视图（Materialized View）或汇总表（Summary Table）。报表查询直接访问这些小得多的表。
  • OLAP 数据库：使用专门为多维分析设计的列式存储数据库，如 ClickHouse, Apache Druid, StarRocks 等。它们对这类聚合查询有极致的性能优化。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，你刚才写的 ROLLUP 查询会怎样？如何优化？

   • 回答要点 1 (瓶颈分析)：数据量增长 100 倍后，实时 ROLLUP 查询的瓶颈会出现在 I/O 和 CPU 上。全表扫描和在线聚合计算会非常耗时，可能导致查询超时或拖慢整个数据库。
   • 回答要点 2 (优化方案)：首选方案是预聚合。我会创建一个每日/每小时的汇总表，通过定时任务（如 Airflow, XXL-Job）运行 ROLLUP 查询，并将结果存入该表。前端报表直接查询这个汇总表，响应速度会是毫秒级。如果需要更实时的数据，可以采用增量更新汇总表的策略。对于需要即席（Ad-hoc）多维分析的场景，我会建议引入 OLAP 引擎。

2. 追问：刚才你提到了 GROUPING() 函数，它和 GROUPING_ID() 有什么区别？

   • 回答要点：GROUPING() 对单个列进行判断，返回 0 或 1。而 GROUPING_ID() 可以接受多个列作为参数，它将每个列的 GROUPING() 结果（0或1）串联成一个二进制字符串，然后转换为对应的十进制整数。这可以用来在一个 CASE 语句中方便地识别不同的聚合级别。例如，GROUPING_ID(region, department)：
     • (region, department) 级别: GROUPING(region)=0, GROUPING(department)=0 -> 二进制 00 -> 十进制 0
     • (region) 级别: GROUPING(region)=0, GROUPING(department)=1 -> 二进制 01 -> 十进制 1
     • () 级别: GROUPING(region)=1, GROUPING(department)=1 -> 二进制 11 -> 十进制 3
   • GROUPING_ID() 更像是一个用于程序化处理聚合级别的“ID”。

3. 追问：除了 ROLLUP 和 CUBE，SQL 标准里还有其他类似功能吗？它们之间是什么关系？

   • 回答要点：是的，还有 GROUPING SETS。它提供了最大的灵活性。ROLLUP 和 CUBE 实际上是 GROUPING SETS 的语法糖。
     • ROLLUP(a, b) 等价于 GROUPING SETS((a, b), (a), ())。
     • CUBE(a, b) 等价于 GROUPING SETS((a, b), (a), (b), ())。
   • GROUPING SETS 允许你自定义你想要的任何分组组合，而不是局限于 ROLLUP 的层级或 CUBE 的全组合。例如，如果你只想看 (region, department) 的聚合和 (region) 的聚合，但不需要总计，你可以写 GROUP BY GROUPING SETS((region, department), (region))。这在优化特定报表时非常有用，可以避免计算不需要的聚合层级。