SQL中使用ROLLUP和CUBE函数轻松生成汇总行

在数据分析和报表制作中，通常需要对数据进行汇总和分组，我们常用的就是GROUP BY汇总数据，当我们想按照不同维度汇总时，往往需要编写多个GROUP BY预计，而借助ROLLUP 和 CUBE 函数可以一次性生成子总计和总计行，而不需要编写多个 SQL 查询语句。这样可以节省时间和精力，提高数据分析和报表制作的效率。

CUBE 和 ROLLUP 的区别

下面是 CUBE 和 ROLLUP 之间的具体区别及实例：

• CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
• ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

-- 创建示例表格
CREATE TABLE sales_data (
    date DATE,
    region VARCHAR(50),
    sales DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO sales_data (date, region, sales) VALUES
    ('2021-01-01', 'North', 1000),
    ('2021-01-02', 'North', 1500),
    ('2021-01-03', 'North', 2000),
    ('2021-01-01', 'South', 800),
    ('2021-01-02', 'South', 1200),
    ('2021-01-03', 'South', 1800);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1、ROLLUP示例

-- 使用 ROLLUP 函数生成子总计和总计行
SELECT 
    date, region, SUM(sales) AS total_sales
FROM 
    sales_data
GROUP BY 
    ROLLUP(date, region);
1
2
3
4
5
6
7

输出结果：

date        region  total_sales
2021-01-01  North   1000.00
2021-01-01  South   800.00
2021-01-02  North   1500.00
2021-01-02  South   1200.00
2021-01-03  North   2000.00
2021-01-03  South   1800.00
2021-01-01  NULL    1800.00
2021-01-02  NULL    2700.00
2021-01-03  NULL    3800.00
NULL        NULL    8300.00
1
2
3
4
5
6
7
8
9
10
11

上面的结果中，NULL 表示总计行，包括所有日期和地区的销售额总计。

2、CUBE示例

-- 使用 CUBE 函数生成所有可能的聚合数据组合
SELECT 
    date, region, SUM(sales) AS total_sales
FROM 
    sales_data
GROUP BY 
    CUBE(date, region);
1
2
3
4
5
6
7

输出结果：

date        region  total_sales
2021-01-01  North   1000.00
2021-01-01  South   800.00
2021-01-02  North   1500.00
2021-01-02  South   1200.00
2021-01-03  North   2000.00
2021-01-03  South   1800.00
2021-01-01  NULL    1800.00
2021-01-02  NULL    2700.00
2021-01-03  NULL    3800.00
NULL        North   4500.00
NULL        South   3800.00
NULL        NULL    8300.00
1
2
3
4
5
6
7
8
9
10
11
12
13

上面的结果中，NULL 表示所有日期或所有地区的小计行和总计行，例如，NULL 和 North 表示所有日期的 North 地区的小计行和总计行。

区别开表中某列自身的NULL值和的小计行的NULL（汇总行重命名）

以CUBE函数为例，在使用 CUBE 函数时，小计行的 NULL 值与表中某列的 NULL 值是不同的。在 CUBE 函数中，NULL 值表示所有列的小计行。如果您想区分表中某列的 NULL 值和 CUBE 函数中的小计行的 NULL 值，可以使用 GROUPING 函数。

GROUPING 函数可用于检查指定列是否包含在 CUBE 函数的当前小计行中。如果列包含在当前小计行中，则 GROUPING 函数返回 1，否则返回 0。因此，您可以使用 GROUPING 函数来区分表中某列的 NULL 值和 CUBE 函数中的小计行的 NULL 值。

以下是一个示例 SQL 语句，演示如何使用 GROUPING 函数来区分表中某列的 NULL 值和 CUBE 函数中的小计行的 NULL 值（汇总行重命名）：

SELECT 
    CASE WHEN GROUPING(column_name) = 1 THEN 'Total' ELSE column_name END AS column_name,
    SUM(value)
FROM table_name
GROUP BY CUBE(column_name)
1
2
3
4
5

在上面的示例中，如果 column_name 列包含在 CUBE 函数的当前小计行中，GROUPING 函数将返回 1，否则返回 0。通过将 GROUPING 函数的结果与 CASE 语句结合使用，我们可以将包含在当前小计行中的列标记为 “Total”，从而区分表中某列的 NULL 值和 CUBE 函数中的小计行的 NULL 值。