课程 4.5: SQL 中使用 ROLLUP、CUBE 和 GROUPING SETS 的高级聚合

随着报告需求的增长，常规的 GROUP BY 通常不足以满足需求。例如，您可能需要一次性获取以下所有内容：

• 按订单状态和客户的详细信息；
• 按状态的中间总计；
• 按客户的总计；
• 整个数据集的总计。

您可以编写多个查询并使用 UNION ALL 将它们组合在一起，但这会显得冗长且更难维护。对于这些任务，SQL 使用分组表达式修饰符：ROLLUP、CUBE 和 GROUPING SETS。

更准确地说：这些修饰符通过在更高的聚合级别（小计和总计）中丰富聚合查询结果的行。

重要提示：本课程中的所有实际示例均使用 SQL Server (AdventureWorks)。

语法说明：下面的 ROLLUP、CUBE、GROUPING SETS 和 GROUPING() 使用 SQL Server 语法。在 MySQL 中，功能更有限，语法部分不同（例如，通常使用 WITH ROLLUP，而 CUBE 和 GROUPING SETS 在经典形式中可能不可用）。

在本课程中，我们将涵盖：

• ROLLUP、CUBE 和 GROUPING SETS 的区别；
• 小计和总计行是如何构建的；
• 如何使用 GROUPING() 区分生成的总计行。

为什么这很重要

高级聚合帮助您：

• 在单个查询中构建多级报告；
• 减少 SQL 重复；
• 生成一致的详细信息、小计和总计；
• 用更高聚合级别的行丰富详细级别的输出。

核心思想

假设我们在 SalesOrderHeader 中有销售数据，维度为 Status、CustomerID，指标为 TotalDue。

常规的 GROUP BY 仅返回一个分组级别。扩展的分组构造一次返回多个级别。

ROLLUP: 层次总计

ROLLUP 从右到左在列列表中构建层次结构。

语法

GROUP BY ROLLUP (col1, col2, col3)

生成的级别：

• (col1, col2, col3) - 详细信息；
• (col1, col2) - col3 的小计；
• (col1) - col2 和 col3 的小计；
• () - 总计。

示例：按状态和客户的订单金额总计

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY ROLLUP (Status, CustomerID)
ORDER BY Status, CustomerID;

结果：

• 每个 Status + CustomerID 对的行；
• 每个 Status 的小计；
• 整体总计。

CUBE: 所有维度组合

CUBE 为列出列的所有可能组合构建聚合。

语法

GROUP BY CUBE (col1, col2)

对于两个列，级别为：

• (col1, col2)；
• (col1)；
• (col2)；
• ()。

对于三个列，组合已经是 $2^3 = 8$，因此结果大小可能迅速增长。

示例：按状态和客户的订单金额总计，涵盖所有切片

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY CUBE (Status, CustomerID)
ORDER BY Status, CustomerID;

结果： 除了详细信息和总计，您还会得到：

• 每个 Status 的总计；
• 每个 CustomerID 的总计。

GROUPING SETS: 精确控制级别

GROUPING SETS 让您明确列出所需的分组级别。

语法

GROUP BY GROUPING SETS (
    (col1, col2),
    (col1),
    ()
)

示例：仅所需级别，无额外组合

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount
FROM SalesOrderHeader
GROUP BY GROUPING SETS (
    (Status, CustomerID),
    (Status),
    ()
)
ORDER BY Status, CustomerID;

这相当于多个 GROUP BY ... UNION ALL ... 查询，但更紧凑，通常优化得更好。

使用 GROUPING() 区分总计行

在生成的总计行中，维度值通常变为 NULL。问题在于源数据也可能包含真实的 NULL 值。

GROUPING(column) 有助于区分它们：

• 0 - 来自源数据的常规值；
• 1 - 由聚合级别生成的值。

带级别标志的示例

SELECT
    Status,
    CustomerID,
    SUM(TotalDue) AS total_amount,
    GROUPING(Status) AS g_status,
    GROUPING(CustomerID) AS g_customer
FROM SalesOrderHeader
GROUP BY ROLLUP (Status, CustomerID)
ORDER BY Status, CustomerID;

实际报告标记模式：

CASE
    WHEN GROUPING(Status) = 1 AND GROUPING(CustomerID) = 1 THEN '总计'
    WHEN GROUPING(CustomerID) = 1 THEN '状态小计'
    ELSE '详细信息'
END AS row_type

何时使用什么

• 当您需要层次总计时使用 ROLLUP（例如，年 -> 月 -> 日）。
• 当您需要跨维度的所有分析切片时使用 CUBE。
• 当您想严格控制返回的级别时使用 GROUPING SETS。

实用建议

• 始终检查结果大小：CUBE 可能显著增加行数。
• 为可读性标记行类型（详细信息、小计、总计）。
• 添加显式的 ORDER BY 以便总计以可预测的顺序出现。
• 如果需要聚合过滤，请与 HAVING 结合使用。

MySQL 示例

以下是使用 WITH ROLLUP 在 payment 表上的 MySQL 示例：

SELECT
    staff_id,
    customer_id,
    SUM(amount) AS total_amount
FROM
    payment
GROUP BY
    staff_id, customer_id WITH ROLLUP
ORDER BY
    GROUPING(staff_id),
    staff_id,
    GROUPING(customer_id),
    customer_id;

在此查询中：

• 每个 staff_id + customer_id 对的详细信息被返回；
• WITH ROLLUP 添加了每个 staff_id 的小计和一个总计；
• ORDER BY GROUPING(...) 将行按方便的顺序排列：详细信息、小计，然后是总计。

MySQL 的重要说明：

• WITH ROLLUP 提供层次总计，但不是 CUBE/GROUPING SETS 的完全等效。
• 对于更复杂的分组组合，通常需要多个查询与 UNION ALL 结合使用。
• 如果您的 MySQL 版本不支持 GROUPING()，排序和总计行标记通常通过 NULL 检查完成。

实用用法

1. 按状态和客户的订单金额报告，带总计： ROLLUP (Status, CustomerID) 提供详细信息、状态小计和总计。

2. 多维销售分析： CUBE (Status, CustomerID) 提供跨状态和客户的所有切片组合。

3. 自定义订单金额报告： GROUPING SETS 让您仅保留所需的级别：详细信息 + 部门小计 + 总计。

本课程的关键要点

• ROLLUP、CUBE 和 GROUPING SETS 扩展了标准的 GROUP BY。
• ROLLUP 创建层次总计，CUBE 创建所有组合，GROUPING SETS 仅创建明确列出的级别。
• GROUPING() 对于正确解释生成的总计行至关重要。
• 这些工具帮助在单个查询中构建灵活的订单金额分析报告。

通过掌握这些构造，您可以设计更强大的 SQL 报告，而无需长时间的 UNION ALL 链。