SQL 语言基础：分组集（Grouping Sets）、CUBE 和 ROLLUP

原创于 2025-03-31 10:17:57 发布 · 513 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#T-SQL #T-SQL 分组集 #GroupingSETS #T-SQL CUBE #T-SQL ROLLUP #T-SQL GROUPING #GROUPING_ID

SQL Server 同时被 2 个专栏收录

49 篇文章

订阅专栏

#TSQL 语言基础

26 篇文章

订阅专栏

介绍

在 SQL Server 中，分组集（Grouping Sets）、CUBE 和 ROLLUP 是用于生成报表和数据仓库处理的强大工具。它们允许你在一个查询中定义多个分组集，从而简化复杂的聚合查询。

示例

GROUP BY 子句

一个聚合查询只能定义一个分组集。以下查询每个都只定义了一个分组集：

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY empid, custid;

SELECT empid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY empid;

SELECT custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY custid;

SELECT SUM(qty) AS sumqty
FROM dbo.orders;

GROUPING SETS 从属子句

-- 增强 GROUP BY，主要用于生成报表和数据仓库处理
-- 可以在同一查询定义多个分组集

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY
    GROUPING SETS
    (
        (empid, custid),
        (empid),
        (custid),
        ()
    );

输出结果：

empid  custid  sumqty
2      A       52
3      A       20
NULL   A       72
1      B       20
2      B       27
NULL   B       47
1      C       34
3      C       22
NULL   C       56
3      D       30
NULL   D       30
NULL   NULL    205
1      NULL    54
2      NULL    79
3      NULL    72

CUBE 从属子句

CUBE 为定义多个分组集提供了一种简略方法：

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY CUBE(empid, custid);

输出结果：

empid  custid  sumqty
2      A       52
3      A       20
NULL   A       72
1      B       20
2      B       27
NULL   B       47
1      C       34
3      C       22
NULL   C       56
3      D       30
NULL   D       30
NULL   NULL    205
1      NULL    54
2      NULL    79
3      NULL    72

ROLLUP 从属子句

ROLLUP 提供了一种定义多个分组集的简略方法，与 CUBE 不同，ROLLUP 只生成一个子集：

-- 输入成员之间存在一定层次关系，从而生成让这种层次关系有意义的所有分组集
-- CUBE(a, b, c) 生成由3个输入成员得到的所有 8 个可能的分组集
-- ROLLUP 认为 3 个输入成员存在 a> b> c的层次关系，所以只生成 4 个分组集
-- 效果上相当于指定 GROUPING SETS((a, b, c),(a, b),())

示例：按时间层次：订单年份 > 订单月份 > 订单日定义分组集，并为每个分组集返回其总订货量

SELECT
    YEAR(orderdate) AS orderYear,
    MONTH(orderdate) AS orderMonth,
    DAY(orderdate) AS orderDay,
    SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));

输出结果：

orderYear  orderMonth  orderDay  sumqty
2007       4           18        22
2007       4           NULL      22
2007       8           2         10
2007       8           NULL      10
2007       12          24        32
2007       12          NULL      32
2007       NULL        NULL      64
2008       1           9         40
2008       1           18        14
2008       1           NULL      54
2008       2           12        12
2008       2           NULL      12
2008       NULL        NULL      66
2009       2           12        10
2009       2           16        20
2009       2           NULL      30
2009       4           18        15
2009       4           NULL      15
2009       9           7         30
2009       9           NULL      30
2009       NULL        NULL      75
NULL       NULL        NULL      205

GROUPING 和 GROUPING_ID 函数

GROUPING 和 GROUPING_ID 函数用于标识结果行和分组集的关联：

SELECT 
    GROUPING(empid) AS grpemp,
    GROUPING(custid) AS grpcust, 
    empid, custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY CUBE(empid, custid);

输出结果：

grpemp  grpcust  empid  custid  sumqty
0       0        2      A       52
0       0        3      A       20
1       0        NULL   A       72
0       0        1      B       20
0       0        2      B       27
1       0        NULL   B       47
0       0        1      C       34
0       0        3      C       22
1       0        NULL   C       56
0       0        3      D       30
1       0        NULL   D       30
1       1        NULL   NULL    205
0       1        1      NULL    54
0       1        2      NULL    79
0       1        3      NULL    72

-- 因为 orders 表的 empid 和 custid 都定义为 NOT NULL
-- 输出中的 NULL值只时一个占位符，表示该列并不属于当前的分组集
-- 所有 empid 和 custid 均不为 NULL 的行都与分组集（empid, custid） 相关联
-- 所有 empid 不为 NULL/ custid 为 NULL 的行都与 分组集（EMPId） 有关联
-- 如果表中的分组定义为允许 NULL 值，这时就无法区分结果集中的 NULL 是不是占位符

使用 GROUPING_ID 函数：

-- 使用 GROUPING 函数，接受一个列名，如果该列时当前分组集的成员，返回 0， 否则 1

SELECT
    GROUPING_ID(empid, custid) AS GROUPINGSET,
    empid, custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY CUBE(empid, custid);

输出结果：

GROUPINGSET  empid  custid  sumqty
0            2      A       52
0            3      A       20
2            NULL   A       72
0            1      B       20
0            2      B       27
2            NULL   B       47
0            1      C       34
0            3      C       22
2            NULL   C       56
0            3      D       30
2            NULL   D       30
3            NULL   NULL    205
1            1      NULL    54
1            2      NULL    79
1            3      NULL    72

ROLLUP 和 GROUPING_ID

创建一个 Sales 表并插入数据：

CREATE TABLE Sales (
    [year] NVARCHAR(10),
    Region NVARCHAR(50),
    [Quarter] NVARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales
VALUES
('2006','North', 'ProductA', 100.00),
('2007','North', 'ProductB', 150.00),
('2006','South', 'ProductA', 200.00),
('2007','South', 'ProductB', 250.00);

使用 ROLLUP 和 GROUPING_ID：

SELECT 
    Year,
    Quarter,
    Region,
    SUM(SalesAmount) AS TotalSales,
    GROUPING_ID(Year, Quarter, Region) AS GroupingID
FROM 
    Sales
GROUP BY 
    ROLLUP(Year, Quarter, Region);

输出结果：

Year    Quarter   Region   TotalSales  GroupingID
2006    ProductA  North    100.00      0
2006    ProductA  South    200.00      0
2006    ProductA  NULL     300.00      1
2006    NULL      NULL     300.00      3
2007    ProductB  North    150.00      0
2007    ProductB  South    250.00      0
2007    ProductB  NULL     400.00      1
2007    NULL      NULL     400.00      3
NULL    NULL      NULL     700.00      7

-- 返回一个整数值，表示 Year、Quarter 和 Region 列的分组情况。
0 表示所有列都参与分组。
1 表示 Region 未参与分组。
3 表示 Quarter 和 Region 未参与分组。
7 表示 Year、Quarter 和 Region 都未参与分组（即总计行）。

总结

SQL Server 中的分组集（Grouping Sets）、CUBE 和 ROLLUP 提供了强大的数据分组和聚合功能，适用于生成报表和数据仓库处理。通过这些功能，可以在一个查询中定义多个分组集，从而简化复杂的聚合查询，并使用 GROUPING 和 GROUPING_ID 函数准确标识结果行和分组集的关联。