MySQL高级函数之：`GROUPING()`：其在`WITH ROLLUP`中区分汇总行和明细行。

MySQL 高级函数 GROUPING()：WITH ROLLUP 的幕后英雄

大家好，今天我们深入探讨 MySQL 中一个相对低调但功能强大的函数：GROUPING()。 很多人在使用 WITH ROLLUP 进行数据汇总时，可能会忽视它的存在，但它却是区分汇总行和明细行的关键。 本次讲座将从 WITH ROLLUP 的基本用法开始，逐步剖析 GROUPING() 函数的作用，并通过丰富的示例演示其在实际应用中的价值。

WITH ROLLUP：多维数据汇总利器

WITH ROLLUP 是 MySQL 提供的一种用于生成多维度汇总数据的语法。 它允许你在 GROUP BY 子句的基础上，自动生成额外的汇总行，包括针对每个分组的汇总以及最终的总体汇总。

语法结构：

SELECT column1, column2, ..., aggregate_function(columnN)
FROM table_name
GROUP BY column1, column2, ... WITH ROLLUP;

示例：

假设我们有一个 sales 表，记录了不同年份、区域和产品的销售额：

CREATE TABLE sales (
    year INT,
    region VARCHAR(50),
    product VARCHAR(50),
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (year, region, product, sales_amount) VALUES
(2022, 'North', 'Product A', 1000.00),
(2022, 'North', 'Product B', 1500.00),
(2022, 'South', 'Product A', 800.00),
(2022, 'South', 'Product C', 1200.00),
(2023, 'North', 'Product A', 1200.00),
(2023, 'North', 'Product B', 1800.00),
(2023, 'South', 'Product B', 1000.00),
(2023, 'South', 'Product C', 1500.00);

现在，我们想按年份和区域统计销售额，并生成汇总行。 使用 WITH ROLLUP 可以轻松实现：

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

执行结果如下：

year	region	total_sales
2022	North	2500.00
2022	South	2000.00
2022	NULL	4500.00
2023	North	3000.00
2023	South	2500.00
2023	NULL	5500.00
NULL	NULL	10000.00

可以看到，结果集中包含了按年份和区域的明细销售额，以及针对每个年份的汇总（region 为 NULL），以及最终的总体汇总（year 和 region 都为 NULL）。

问题：如何区分汇总行和明细行？

在上面的例子中，NULL 值被用来表示汇总行。 然而，NULL 值本身也可能存在于原始数据中，这会导致我们无法准确区分汇总行和明细行。 此外，NULL 值在一些报表工具中处理起来可能不太方便。 这时，GROUPING() 函数就派上用场了。

GROUPING()：识别汇总行的标记

GROUPING() 函数的作用是判断某个列是否因为 WITH ROLLUP 而被汇总。 它的参数是 GROUP BY 子句中的列名。 如果该列在当前行被汇总了，GROUPING() 函数返回 1，否则返回 0。

语法结构：

GROUPING(column_name)

示例：

让我们在上面的查询中加入 GROUPING() 函数，看看它如何区分汇总行和明细行：

SELECT year, region, SUM(sales_amount) AS total_sales,
       GROUPING(year) AS year_grouping,
       GROUPING(region) AS region_grouping
FROM sales
GROUP BY year, region WITH ROLLUP;

执行结果如下：

year	region	total_sales	year_grouping	region_grouping
2022	North	2500.00	0	0
2022	South	2000.00	0	0
2022	NULL	4500.00	0	1
2023	North	3000.00	0	0
2023	South	2500.00	0	0
2023	NULL	5500.00	0	1
NULL	NULL	10000.00	1	1

观察结果：

• 当 year_grouping 和 region_grouping 都为 0 时，表示该行是明细行。
• 当 region_grouping 为 1 时，表示该行是按年份汇总的行。
• 当 year_grouping 和 region_grouping 都为 1 时，表示该行是总体汇总行。

现在，我们可以使用 GROUPING() 函数来清晰地标识和区分汇总行。

GROUPING() 的应用：更清晰的报表展示

有了 GROUPING() 函数，我们可以根据需要定制报表的展示方式，避免使用 NULL 值，提高可读性。

示例 1：使用 CASE 表达式替换 NULL 值

我们可以使用 CASE 表达式，根据 GROUPING() 函数的结果，将 NULL 值替换为更有意义的文本：

SELECT
    CASE
        WHEN GROUPING(year) = 1 THEN 'Total'
        ELSE CAST(year AS CHAR)
    END AS year,
    CASE
        WHEN GROUPING(region) = 1 THEN 'All Regions'
        ELSE region
    END AS region,
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY year, region WITH ROLLUP;

执行结果如下：

year	region	total_sales
2022	North	2500.00
2022	South	2000.00
2022	All Regions	4500.00
2023	North	3000.00
2023	South	2500.00
2023	All Regions	5500.00
Total	All Regions	10000.00

这样，报表就更加清晰易懂了。

示例 2：过滤掉不需要的汇总行

有时候，我们可能只需要总体汇总行，而不需要其他级别的汇总行。 可以使用 HAVING 子句结合 GROUPING() 函数来实现：

SELECT year, region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY year, region WITH ROLLUP
HAVING GROUPING(year) = 1 AND GROUPING(region) = 1;

执行结果如下：

year	region	total_sales
NULL	NULL	10000.00

这个查询只返回总体汇总行。

示例 3：更复杂的报表逻辑

GROUPING() 函数还可以与其他函数和表达式结合，实现更复杂的报表逻辑。 例如，我们可以根据汇总级别，显示不同的指标：

SELECT
    year,
    region,
    SUM(sales_amount) AS total_sales,
    CASE
        WHEN GROUPING(year) = 1 AND GROUPING(region) = 1 THEN 'Grand Total'
        WHEN GROUPING(region) = 1 THEN 'Yearly Total'
        ELSE 'Regional Sales'
    END AS summary_level
FROM sales
GROUP BY year, region WITH ROLLUP;

执行结果如下：

year	region	total_sales	summary_level
2022	North	2500.00	Regional Sales
2022	South	2000.00	Regional Sales
2022	NULL	4500.00	Yearly Total
2023	North	3000.00	Regional Sales
2023	South	2500.00	Regional Sales
2023	NULL	5500.00	Yearly Total
NULL	NULL	10000.00	Grand Total

这个查询在结果集中增加了一个 summary_level 列，用于描述每一行的汇总级别。

GROUPING_ID()：多列汇总的简便方式

当 GROUP BY 子句包含多个列时，使用多个 GROUPING() 函数可能会显得冗长。 MySQL 5.7.22 引入了 GROUPING_ID() 函数，可以简化这个过程。

GROUPING_ID() 函数接受多个列名作为参数，并返回一个整数，该整数的二进制表示形式反映了每个列是否被汇总。 从右向左，每一位对应一个列，1 表示被汇总，0 表示未被汇总。

语法结构：

GROUPING_ID(column1, column2, ...)

示例：

我们可以使用 GROUPING_ID() 函数来简化上面的查询：

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

执行结果如下：

year	region	total_sales	grouping_id
2022	North	2500.00	0
2022	South	2000.00	0
2022	NULL	4500.00	1
2023	North	3000.00	0
2023	South	2500.00	0
2023	NULL	5500.00	1
NULL	NULL	10000.00	3

观察结果：

• grouping_id 为 0 (二进制 00) 表示 year 和 region 都没有被汇总。
• grouping_id 为 1 (二进制 01) 表示 region 被汇总了，而 year 没有被汇总。
• grouping_id 为 3 (二进制 11) 表示 year 和 region 都被汇总了。

使用 GROUPING_ID() 函数，我们可以使用一个整数来表示多个列的汇总状态，使查询更加简洁。 我们可以使用 CASE 表达式来根据 GROUPING_ID() 的值，实现更复杂的报表逻辑。

示例:

SELECT
    CASE
        WHEN GROUPING_ID(year, region) = 3 THEN 'Total'
        WHEN GROUPING_ID(year, region) = 1 THEN CAST(year AS CHAR)
        ELSE CAST(year AS CHAR)
    END AS year,
    CASE
        WHEN GROUPING_ID(year, region) = 3 THEN 'All Regions'
        WHEN GROUPING_ID(year, region) = 1 THEN 'All Regions'
        ELSE region
    END AS region,
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY year, region WITH ROLLUP;

这个例子实现了和前面使用 GROUPING() 函数相同的报表展示效果，但代码更加简洁。

COALESCE() 配合 GROUPING() 实现更友好的展示

COALESCE() 函数在处理 NULL 值时非常有用。 它可以接受多个参数，并返回第一个非 NULL 的参数。 结合 GROUPING() 函数，我们可以更优雅地处理汇总行的显示。

示例：

SELECT
    COALESCE(CAST(year AS CHAR), 'Total') AS year,
    COALESCE(region, 'All Regions') AS region,
    SUM(sales_amount) AS total_sales
FROM sales
GROUP BY year, region WITH ROLLUP;

这个查询在显示年份和区域时，如果值为 NULL，则分别显示 "Total" 和 "All Regions"。 但是，这并不能区分 NULL 是原始数据还是汇总结果。 所以需要配合 GROUPING()。

正确的使用方式是先用 CASE WHEN GROUPING() = 1 THEN ... ELSE ... END 将 NULL 替换成其他值，然后再使用 COALESCE()。 虽然这个例子中没有直接使用 GROUPING()，但是它说明了 COALESCE() 在处理 NULL 值时的作用。 在更复杂的场景中，COALESCE() 可以与其他函数结合，实现更灵活的报表展示。

总结：GROUPING() 和 WITH ROLLUP 的完美搭档

本次讲座我们深入探讨了 MySQL 中 GROUPING() 函数在 WITH ROLLUP 中的作用。 掌握 GROUPING() 函数，可以帮助我们更好地理解和控制 WITH ROLLUP 生成的汇总数据，避免 NULL 值的歧义，并根据需要定制报表的展示方式。 记住，GROUPING() 函数和 WITH ROLLUP 是数据分析和报表生成的强大工具，善用它们可以提高工作效率和数据洞察力。

提升报表可读性，清晰区分汇总行

GROUPING() 函数为我们提供了一种区分汇总行和明细行的有效手段，结合 CASE 表达式、HAVING 子句和 COALESCE() 函数，可以生成更加清晰、易懂的报表。

简化多列汇总，GROUPING_ID() 值得拥有

当需要对多个列进行汇总时，GROUPING_ID() 函数可以简化代码，提高效率。 掌握 GROUPING_ID() 函数，可以更灵活地处理多维数据汇总。