SQL Server GROUPING SETS

2025年3月17日 | 阅读 7 分钟

GROUPING SET 是 GROUP BY 子句的扩展。 GROUP BY 语句是 GROUPING SET 是 GROUP BY 子句的扩展。 GROUP BY 语句与聚合函数（如 SUM、AVG、COUNT 等）结合使用，用于汇总数据。它根据单个或多个列对结果集进行分组。 GROUPING SET 最初是在 SQL Server 2008 版本中引入的。

以下场景解释了 SQL Server 中 GROPING SET 的概念

SQL Server 允许我们通过组合员工表的部门和城市来创建一个组，以检索在某个城市特定部门工作员工的总数。 假设我们想获得在班加罗尔市 IT 部门工作的所有员工。 此外，我们可以进一步将员工类型（作家）添加到部门和城市中，以检索在班加罗尔市 IT 部门工作的所有作家。这是两个不同的组，因此无法使用 GROUP BY 子句在单个查询中执行。我们需要为这些组编写两个不同的语句，第一个是 (部门，城市)，第二个是 (部门，城市，员工类型)。

在这种情况下，我们使用 GROUPING SET，这使其变得简单。它通过在单个查询中指定多个组来增强 GROUP BY 子句的功能。它允许创建多个组，并使用 GROUPING SET 在单个查询中分析和比较它们的属性，因为它是一个由多个组组成的集合。

语法

以下是说明 SQL Server 中 GROUPING SET 的语法

SELECT column_list, aggregate(column_name)
FROM table_name
GROUP BY
GROUPING SETS (
(column1, column2),
(column1),
(column2),
()
);

下面解释了此语法参数

Column_list：它指示我们想要从表中检索到结果集的列的名称。
aggregate(column_name)：它指示应用聚合函数（即 SUM、COUNT、AVG 等）的列
GROUP BY：它用于将一列或多列指定为一个组。
GROUPING SET：它指示必须组合在一起的列的集合。
()：它是一个可选的选项，用于指定一个空的分组集，并单独聚合列。

GROUPING SETS 等价物

GROUPING SETS 等价于 UNION ALL 运算符。 SQL Server 使用 UNION ALL 查询生成相同的信息，每个查询都将 GROUP BY 列表和 GROUPING SETS 定义为 GROUP BY 列表。 GROUPING SETS 也可以用来代替 ROLLUP 和 CUBE。

下表更清楚地解释了 GROUPING SETS 等价的 UNION ALL、ROLLUP 和 CUBE

GROUPING SETS 等价于 UNION ALL
SELECT column1, column2, SUM(colmn3) FROM table1 GROUP BY GROUPING SETS ((column1), (column2));	SELECT column1 NULL AS column2, SUM(column3) FROM table1 GROUP BY column1 UNION ALL SELECT NULL AS column1, column2, SUM(column3) FROM table1 GROUP BY column2;
GROUPING SETS 等价于 ROLLUP
GROUP BY ROLLUP (column1, column2, column3)	GROUP BY GROUPING SETS ((column1, column2, column3), (column1, column2), (column1), ());
GROUPING SETS 等价于 CUBE
GROUP BY CUBE (column1, column2, column3)	GROUP BY GROUPING SETS ( (column1, column2, column3), (column1, column2), (column1, column3), (column2, column3), (column1), (column2), (column3), ());

GROUPING SETS 示例

让我们通过示例了解 GROUPING SETS 在 SQL Server 中的工作方式。我们将首先创建一个名为 ProductQty 的示例表，其中包含已购产品的详细信息，然后在表中插入一些记录。

执行以下语句以创建 ProductQty 表

CREATE TABLE ProductQty(
 Id INT IDENTITY(1,1),
 Region VARCHAR(100),
 Product VARCHAR(50),
 Year INT,
 Quantity INT
)

接下来，执行以下语句将记录插入此表

INSERT INTO ProductQty(Region, Product, Year, Quantity)
VALUES('East', 'Computer', 2020, 13000),
('South', 'Computer', 2020, 45000),
('North', 'Computer', 2020, 25000),
('East', 'Hard Disk', 2020, 1900),
('West', 'Computer', 2021, 25000),
('South', 'Hard Disk', 2021, 5500),
('West', 'Hard Disk', 2021, 6500),
('East', 'Pen Drive', 2021, 1200),
('North', 'Mouse', 2019, 1600),
('South', 'Pen Drive', 2019, 2700),
('East', 'Mouse', 2019, 2000),
('West', 'Pen Drive', 2019, 1900);

现在，我们可以通过执行 SELECT 语句来验证数据

在这里，我们将从 GROUP BY 子句开始，而不是从 GROUPING SET 开始，这样我们可以逐步扩展逻辑，并清楚地理解 GROUPING SET 的重要性和用法。

1) 查询以确定按地区购买的总数量

此语句使用 “地区” 列创建一个组，以获取总购买量。在这里，我们对 “数量” 列使用聚合函数 SUM 来对总购买量进行求和。

SELECT Region, SUM(Quantity) AS TotalPurchase 
FROM ProductQty 
GROUP BY Region;

执行查询将显示总购买量

2) 查询以确定每年购买的总数量

此语句使用 “年份” 列创建一个组，以获取每年购买的总数量。在这里，我们还使用 SUM 函数对总购买量进行求和。

SELECT Year, SUM(Quantity) AS TotalPurchase 
FROM ProductQty 
GROUP BY Year;

执行查询将显示总购买量

3) 查询以确定每个地区和年份中可用的总购买量

此语句使用 “地区” 和 “年份” 列创建一个组，并返回每个地区和年份的总购买量。

SELECT Region, Year, SUM(Quantity) AS TotalPurchase 
FROM ProductQty 
GROUP BY Region, Year;

执行查询将显示总购买量

4) 如果我们想显示总购买量，我们使用空分组集查询

SELECT SUM(Quantity) AS TotalPurchase 
FROM ProductQty;

执行查询将显示总购买量

正如我们所看到的，上述四个查询使用不同的分组集返回四个结果集。在这里，我们有一个问题，每次执行一个新查询来为从不同角度分析数据的创建新组或类别。 SQL Server 还有另一种方式来获得相同的结果，即将所有查询结果集合并到一个查询中，使用 UNION ALL 运算符[。

以下 UNION ALL 语句产生相同的结果。由于 UNION ALL 运算符要求所有参与查询的结果集具有相同数量的列，因此我们必须在选择列表中添加一个虚拟列 NULL，以确保所有语句都具有相同数量的列。

SELECT Region, NULL AS Product, SUM(Quantity) AS TotalQty  
FROM ProductQty 
GROUP BY Region
UNION ALL
SELECT NULL AS Region, Product, SUM(Quantity) AS TotalQty
FROM ProductQty
GROUP BY Product
UNION ALL
SELECT Region, Product, SUM(Quantity) AS TotalQty  
FROM ProductQty
GROUP BY Region, Product
UNION ALL
SELECT NULL AS Region, NULL AS Product, SUM(Quantity) AS TotalQty 
FROM ProductQty

执行该语句将显示以下输出，我们看到 UNION ALL 生成一个结果，其中包含所有分组集的聚合，正如预期的那样。此结果集也是以上所有四个查询的集成输出。

但是，这种方法在单个查询中返回结果，但它是临时的、繁琐且效率低下的。数据库服务器有很多压力来执行这种方法，这可能导致查询速度变慢和性能问题，因为它必须执行四个不同的查询。在这里，数据库服务器首先组合前两个查询的结果集，然后将这些结果集与第三个查询组合，然后再与第四个查询组合。这种方法需要多次表读取、临时存储和许多 IO。

SQL Server 2008 引入了 GROUPING SET 功能以克服这些缺点，允许我们在单个查询中定义多个组。我们可以通过在 GROUPING SET 子句中包含所有组来实现相同的目标，从而生成一个包含所有组及其基本详细信息的输出。这是在单个语句中定义每个分组集的查询

SELECT Region, Year, SUM(Quantity) AS TotalPurchaseQty  
FROM ProductQty 
GROUP BY GROUPING SETS
((Region), 
(Year), 
(Region, Year), 
()) 
ORDER BY Region, Year;

执行该语句将产生以下输出

在此输出中，我们可以看到此查询给出了相同的结果，并且与使用 UNION ALL 查询相比，它也非常简单高效。在这里我们可以看到它有一行（第一行），它不是一个组；它只是总数量的聚合。

GROUPING 函数

SQL Server 还允许我们通过在上述查询中使用 GROUPING FUNCTION 来检查 GROUP BY 子句中指定的列是否被聚合。此函数返回两个值，0 和 1，其中 1 代表聚合结果集，0 代表未聚合。

以下查询解释了分组函数的使用

SELECT GROUPING(Region) AS Is_RegionGrouped, GROUPING(Year) AS Is_YearGrouped, 
Region, Year, SUM(Quantity) AS TotalPurchaseQty  
FROM ProductQty 
GROUP BY GROUPING SETS ((Region), (Year), (Region, Year), ( )) 
ORDER BY Region, Year;

执行该语句将显示以下输出