SQL ROLLUP、CUBE 和 GROUPING SETS

第 4.5 课 · 阅读时间：约 9 分钟 · 练习题 ↓

SQL ROLLUP、CUBE 和 GROUPING SETS 扩展了 GROUP BY：除了普通分组外，它们还会在同一个结果中返回小计行和总计行。在本课中，你将学习这三种语法的区别、如何在 MySQL 中使用 WITH ROLLUP，以及 GROUPING() 函数如何把汇总行与真实的 NULL 值区分开。

典型的报表会显示明细行、小计和总计：每月的收入、每年的合计以及整个期间的合计。使用普通的 GROUP BY，你需要为每个层级分别执行查询，再把结果拼接起来。ROLLUP、CUBE 和 GROUPING SETS 用一个查询就能返回所有层级。

分组层级如何工作

分组层级是用于对行进行分组的列的集合。对于两列，有四种可能的层级：

在小计行中，被“汇总”的列为 NULL。

MySQL 和 MariaDB 中的 WITH ROLLUP

MySQL 和 MariaDB 用自己的语法支持 ROLLUP：在 GROUP BY 列表后加上 WITH ROLLUP。

SELECT YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

结果：每个月的收入（2005 年 5 月至 8 月以及 2006 年 2 月），每年一行小计（其中 pay_month 为 NULL），以及一行总计（两列都为 NULL）。

GROUP BY 中列的顺序决定了层级结构：会为第一列的每个值添加小计，而最后一列最先被“汇总”。

用 GROUPING() 区分汇总行

在汇总行中，被汇总的列为 NULL。但真实数据中也可能含有 NULL，这时就无法区分小计行和值为空的一组行。GROUPING(column) 函数可以解决这个问题：如果该列在这一行中被汇总，它返回 1；如果值来自数据，则返回 0。

MySQL 8.0+、PostgreSQL、SQL Server 和 Oracle 支持 GROUPING()。MariaDB 不支持，只能检查 NULL。

-- MySQL 8.0+
SELECT CASE
           WHEN GROUPING(YEAR(payment_date)) = 1 THEN 'Grand total'
           WHEN GROUPING(MONTH(payment_date)) = 1 THEN 'Year total'
           ELSE 'Month'
       END AS row_type,
       YEAR(payment_date) AS pay_year,
       MONTH(payment_date) AS pay_month,
       SUM(amount) AS revenue
FROM payment
GROUP BY YEAR(payment_date), MONTH(payment_date) WITH ROLLUP;

结果：同样的八行，每行都被标记为 Month、Year total 或 Grand total。

标准的 ROLLUP、CUBE 和 GROUPING SETS

PostgreSQL、SQL Server 和 Oracle 支持这三种语法的标准写法。下面的示例在 SQL Server 中的 AdventureWorks 数据库上运行。产品按开始销售的年份和颜色分组；有些产品没有颜色，所以 Color 中包含真实的 NULL 值。

ROLLUP

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products,
       GROUPING(Color) AS is_year_total
FROM Product
GROUP BY ROLLUP (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

结果：每个年份和颜色的产品数量、每年的小计（is_year_total = 1），最后是总计。2006 年有两行 Color = NULL：is_year_total = 0 的那行是 18 个没有颜色的产品组成的组，is_year_total = 1 的那行是该年的小计。

CUBE

CUBE 为列的每种组合添加汇总，包括各年合并后按颜色的汇总：

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY CUBE (YEAR(SellStartDate), Color)
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, GROUPING(Color), Color;

结果：ROLLUP 返回的所有内容，再加上各年合并后每种颜色的汇总。

对于 n 列，CUBE 会产生 2ⁿ 个分组层级，因此结果增长很快。请只在维度较少时使用它。

GROUPING SETS

GROUPING SETS 只返回你列出的层级。例如，只要按年份的汇总和按颜色的汇总，不要明细行：

SELECT YEAR(SellStartDate) AS start_year,
       Color,
       COUNT(*) AS products
FROM Product
GROUP BY GROUPING SETS ((YEAR(SellStartDate)), (Color), ())
ORDER BY GROUPING(YEAR(SellStartDate)), start_year, Color;

结果：每年一行、每种颜色一行，以及总计。

ROLLUP 和 CUBE 都是简写：ROLLUP (a, b) 等同于 GROUPING SETS ((a, b), (a), ())。

各数据库的支持情况

如果不支持某种语法，可以用 UNION ALL 合并多个 GROUP BY 查询，你将在第 5.10 课中学习它。

何时使用哪一种

• ROLLUP 用于层级结构：年 → 月、地区 → 城市、类别 → 产品。
• CUBE 用于分析几个独立维度的所有切片。
• GROUPING SETS 用于只需要特定层级的情况，例如在一个结果中给出两种独立的拆分。

请始终添加 ORDER BY，让汇总行出现在可预期的位置，并用 GROUPING() 和 CASE 为它们加上标签。

要点：

• ROLLUP、CUBE 和 GROUPING SETS 在 GROUP BY 的结果中添加小计行和总计行。
• ROLLUP (a, b) 产生 (a, b)、(a)、()；CUBE (a, b) 还会添加 (b)。
• GROUPING SETS 恰好返回列出的层级。
• GROUPING(column) = 1 表示该列已被汇总，可将汇总行与真实的 NULL 值区分开。
• MySQL 和 MariaDB 只支持 WITH ROLLUP；MariaDB 没有 GROUPING()。

常见问题

ROLLUP 和 CUBE 有什么区别？

ROLLUP 从右到左生成层级式小计：对于 (year, month)，它添加每年的合计和总计。CUBE 为所有组合生成小计，因此还会添加各年合并后每个月的合计。

如何为 GROUP BY 查询添加总计行？

在 MySQL 和 MariaDB 中，在 GROUP BY 列之后添加 WITH ROLLUP。在 PostgreSQL、SQL Server 和 Oracle 中，使用 GROUP BY ROLLUP (columns)；如果只需要明细和总计，使用 GROUP BY GROUPING SETS ((columns), ())。

为什么 ROLLUP 的结果中有 NULL 值？

在小计行和总计行中，被汇总的列没有单一的值，所以为 NULL。使用 GROUPING(column)，它对这些行返回 1，从而把它们与数据中真实的 NULL 值区分开。