MySQL - ROLLUP

MySQL - 使用 ROLLUP 汇总数据

什么是 ROLLUP 子句？

WITH ROLLUP 修饰符是 MySQL 中 GROUP BY 子句的一个强大扩展。它用于为 GROUP BY 列表中定义的组生成汇总行（summary rows）。本质上，它在多个层级创建小计（subtotals），最终汇总为总计（grand total）。这是分析查询和商业智能报告中的常见需求。

把它想象成电子表格的透视表（pivot table）。您可以首先按城市汇总数据（例如，销售额总和），然后按州汇总，最后获得整个国家的总计。ROLLUP 在单个查询中提供了这种分层汇总（hierarchical summary）。

语法

SELECT
    column1, column2, ..., AggregateFunction(columnX)
FROM
    table_name
GROUP BY
    column1, column2, ... WITH ROLLUP;

示例设置

我们来创建一个 sales 表，用于跟踪不同区域和年份的产品销售情况。

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    product VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO sales (region, year, product, amount) VALUES
('North', 2022, 'Laptop', 120000.00),
('North', 2022, 'Mouse', 5000.00),
('North', 2023, 'Laptop', 150000.00),
('South', 2022, 'Keyboard', 8000.00),
('South', 2023, 'Keyboard', 10000.00),
('South', 2023, 'Mouse', 7000.00);

ROLLUP 与单列结合使用

ROLLUP 最简单的用法是获取单个分组列的总计。

示例 1：按区域计算总销售额

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region WITH ROLLUP;

结果中包含每项区域总销售额的行，以及一个 region 为 NULL 的额外行，代表所有销售的总计。

region	total_sales
North	275000.00
South	25000.00
NULL	300000.00

ROLLUP 与多列结合使用（分层汇总）

ROLLUP 的真正强大之处在于与多列一起使用时。它会为 GROUP BY 子句中指定的分层结构的每个级别（从右到左）创建小计。

示例 2：按区域和年份汇总销售额

使用 GROUP BY region, year WITH ROLLUP，MySQL 将生成以下汇总：

1. 每个 (region, year) 组合的汇总。
2. 每个 region 的小计（跨其所有年份）。
3. 最终的总计（跨所有区域和年份）。

SELECT
    region,
    year,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region, year WITH ROLLUP;

region	year	total_sales
North	2022	125000.00
North	2023	150000.00
North	NULL	275000.00
South	2022	8000.00
South	2023	17000.00
South	NULL	25000.00
NULL	NULL	300000.00

使用 GROUPING() 函数区分小计

ROLLUP 输出中的 NULL 值可能存在歧义。NULL 是指数据本身为空，还是指 ROLLUP 生成的小计？GROUPING() 函数解决了这个问题。如果列被 ROLLUP 聚合（即它是小计行），它返回 1；否则返回 0。我们可以结合 CASE 语句使用它来提供更具描述性的标签。

示例 3：创建清晰的报告

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN '所有区域'
        ELSE region
    END AS region_summary,
    CASE
        WHEN GROUPING(year) = 1 AND GROUPING(region) = 0 THEN '小计'
        WHEN GROUPING(year) = 1 AND GROUPING(region) = 1 THEN '总计'
        ELSE year
    END AS year_summary,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region, year WITH ROLLUP;

此查询生成了一份更具可读性的报告。

实际应用：销售报告

ROLLUP 对于业务分析而言非常宝贵。应用程序后端可以使用它来生成仪表板（dashboards）和报告数据，通过单个高效的数据库查询，按产品类别、时间段或地理位置显示性能明细。

ROLLUP 与 CUBE 和 GROUPING SETS 的比较

- **`WITH ROLLUP`**：创建分层汇总。对于 `GROUP BY a, b`，它汇总 `(a, b)`、`(a)` 和 `()`。顺序很重要。
- **`WITH CUBE`**：（在 MySQL 中不可用，但在其他 SQL 数据库如 PostgreSQL 和 SQL Server 中常见）。为所有可能的组合分组列创建汇总。对于 `GROUP BY a, b`，它汇总 `(a, b)`、`(a)`、`(b)` 和 `()`。
- **`GROUPING SETS`**：（在 MySQL 中不可用）。最灵活的选项，允许您精确指定要聚合的组合（分组集）。

对于 MySQL 用户来说，ROLLUP 是此类高级聚合的主要工具。理解其分层性质是有效使用它的关键。