多列分组与 ROLLUP

多列分组回顾

前面已经学习了按多列分组：

sql

SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY product_category, customer_name;

结果：每个 (category, customer) 组合一行。

但有时我们需要同时看到：

每个类别的小计
每个客户的小计
总计

这就需要 ROLLUP。

ROLLUP：生成小计和总计

ROLLUP 是 GROUP BY 的扩展，自动生成多层次的汇总。

基本语法

sql

SELECT column1, column2, aggregate_function(column)
FROM table
GROUP BY ROLLUP(column1, column2);

示例：按类别和客户汇总

sql

SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY ROLLUP(product_category, customer_name)
ORDER BY product_category, customer_name;

结果（PostgreSQL）：

text

 product_category | customer_name | orders |  total
------------------+---------------+--------+---------
 Electronics      | Alice         |      2 | 1225.00
 Electronics      | Bob           |      1 |   80.00
 Electronics      | Charlie       |      1 |  250.00
 Electronics      |               |      4 | 1555.00  -- Electronics 小计
 Furniture        | Alice         |      1 |  300.00
 Furniture        | Bob           |      1 |  150.00
 Furniture        | Charlie       |      1 |   50.00
 Furniture        |               |      3 |  500.00  -- Furniture 小计
                  |               |      7 | 2055.00  -- 总计

ROLLUP 生成的行：

每个 (category, customer) 的明细
每个 category 的小计（customer 为 NULL）
总计（category 和 customer 都为 NULL）

ROLLUP 的层次结构

ROLLUP(A, B, C) 生成以下分组：

(A, B, C) - 完整分组
(A, B) - B 的小计
(A) - A 的小计
() - 总计

顺序很重要！ROLLUP(A, B) 与 ROLLUP(B, A) 结果不同。

识别小计行

使用 GROUPING() 函数识别小计行：

sql

SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total,
    GROUPING(product_category) AS is_category_total,
    GROUPING(customer_name) AS is_customer_total
FROM orders
GROUP BY ROLLUP(product_category, customer_name);

GROUPING() 返回：

0：该列参与分组（实际值）
1：该列是小计（NULL 是聚合产生的）

结果：

text

 product_category | customer_name | orders | total  | is_category_total | is_customer_total
------------------+---------------+--------+--------+-------------------+-------------------
 Electronics      | Alice         |      2 | 1225.00|                 0 |                 0
 Electronics      | Bob           |      1 |   80.00|                 0 |                 0
 Electronics      |               |      4 | 1555.00|                 0 |                 1  -- 小计
 Furniture        | Alice         |      1 |  300.00|                 0 |                 0
 Furniture        |               |      3 |  500.00|                 0 |                 1  -- 小计
                  |               |      7 | 2055.00|                 1 |                 1  -- 总计

美化小计显示

使用 CASE 或 COALESCE 替换 NULL：

sql

SELECT 
    COALESCE(product_category, '总计') AS category,
    COALESCE(customer_name, '小计') AS customer,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY ROLLUP(product_category, customer_name)
ORDER BY product_category NULLS LAST, customer_name NULLS LAST;

更精确的方式（区分小计和总计）：

sql

SELECT 
    CASE 
        WHEN GROUPING(product_category) = 1 THEN '【总计】'
        ELSE product_category 
    END AS category,
    CASE 
        WHEN GROUPING(customer_name) = 1 AND GROUPING(product_category) = 0 THEN '【小计】'
        WHEN GROUPING(customer_name) = 1 THEN '【总计】'
        ELSE customer_name 
    END AS customer,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY ROLLUP(product_category, customer_name);

部分 ROLLUP

可以混合普通分组和 ROLLUP：

sql

-- 按年份固定分组，按类别和客户 ROLLUP
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), ROLLUP(product_category, customer_name);

这会为每个年份分别生成类别和客户的小计。

CUBE：全维度汇总

CUBE 生成所有可能的分组组合。

sql

SELECT 
    product_category,
    customer_name,
    SUM(amount) AS total
FROM orders
GROUP BY CUBE(product_category, customer_name);

CUBE(A, B) 生成：

(A, B) - 完整分组
(A) - 按 A 汇总
(B) - 按 B 汇总
() - 总计

ROLLUP vs CUBE：

ROLLUP：层次汇总（A → AB → 总计）
CUBE：所有维度组合（A、B、AB、总计）

CUBE 适用于数据分析和多维报表。

GROUPING SETS：自定义分组组合

GROUPING SETS 允许精确指定要生成的分组：

sql

SELECT 
    product_category,
    customer_name,
    SUM(amount) AS total
FROM orders
GROUP BY GROUPING SETS (
    (product_category, customer_name),  -- 明细
    (product_category),                 -- 按类别小计
    ()                                  -- 总计
);

这等价于：

sql

SELECT product_category, customer_name, SUM(amount) FROM orders GROUP BY product_category, customer_name
UNION ALL
SELECT product_category, NULL, SUM(amount) FROM orders GROUP BY product_category
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM orders;

但 GROUPING SETS 更高效（只扫描一次表）。

MySQL 的 ROLLUP

MySQL 8.0 支持 ROLLUP，但语法略有不同：

sql

-- MySQL
SELECT 
    product_category,
    customer_name,
    COUNT(*) AS orders,
    SUM(amount) AS total
FROM orders
GROUP BY product_category, customer_name WITH ROLLUP;

注意：MySQL 用 WITH ROLLUP，而 PostgreSQL 用 ROLLUP()。

MySQL 不支持 GROUPING SETS 和 CUBE（截至 8.0）。

实际应用：销售报表

sql

-- 按年、季度、月份的销售报表
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(QUARTER FROM order_date) AS quarter,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS sales,
    CASE 
        WHEN GROUPING(EXTRACT(YEAR FROM order_date)) = 1 THEN '总计'
        WHEN GROUPING(EXTRACT(QUARTER FROM order_date)) = 1 THEN '年度小计'
        WHEN GROUPING(EXTRACT(MONTH FROM order_date)) = 1 THEN '季度小计'
        ELSE '月度数据'
    END AS level
FROM orders
GROUP BY ROLLUP(
    EXTRACT(YEAR FROM order_date),
    EXTRACT(QUARTER FROM order_date),
    EXTRACT(MONTH FROM order_date)
)
ORDER BY year, quarter, month;

性能考虑

ROLLUP/CUBE 比多个 UNION ALL 查询更高效（一次扫描）
对大表，考虑在分组列上创建索引
CUBE 可能生成大量行（2^n 个分组，n 是列数）

练习

基本 ROLLUP：按类别和客户使用 ROLLUP 生成小计和总计。
识别小计：使用 GROUPING() 函数识别哪些行是小计。
美化输出：将小计行的 NULL 替换为"小计"和"总计"。
三维 ROLLUP：按年份、类别、客户三个维度使用 ROLLUP。
CUBE 对比：对比 ROLLUP 和 CUBE 的结果差异（PostgreSQL）。
GROUPING SETS：使用 GROUPING SETS 生成：(category), (customer), (total) 三个分组（PostgreSQL）。
部分 ROLLUP：固定按年份分组，只对类别使用 ROLLUP。
实际报表：创建一个月度销售报表，包含每月明细、每月小计和总计。

完成练习后，打开本章答案页核对。

本章小结

第二章学习了聚合和分组：

聚合函数（COUNT、SUM、AVG、MIN、MAX）
GROUP BY 分组
HAVING 过滤分组结果
ROLLUP 生成小计和总计

这些是数据分析的基础。第三章将学习连接和子查询。

回到第二章概览 · 返回SQL 学习路线

多列分组与 ROLLUP ​

多列分组回顾 ​

ROLLUP：生成小计和总计 ​

基本语法 ​

示例：按类别和客户汇总 ​

ROLLUP 的层次结构 ​

识别小计行 ​

美化小计显示 ​

部分 ROLLUP ​

CUBE：全维度汇总 ​

GROUPING SETS：自定义分组组合 ​

MySQL 的 ROLLUP ​

实际应用：销售报表 ​

性能考虑 ​

练习 ​

本章小结 ​