在SQL Server 2012中如何使用分组集(一)

在SQL Server 2012中如何使用分组集

分组集就是你据以分组的一个属性集。传统上，SQL中的单个聚合查询定义一个单个分组集。例如，下面的四个查询每个定义了一个单个分组集。

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;

第一个查询定义了分组集(empid，custid)，第二个是(empid)，第三个是(custid)，最后一个查询定义了空分组集()。此代码返回四个结果集，每个查询一个。

假设不是要四个单独的结果集，而是想要一个统一了四个分组集的所有聚合数据的单个结果集，可以使用UNION ALL集合运算符组合四个查询的结果集，实现此目标。由于集合运算符要求所有结果集需要具有相同列数的兼容架构，你需要调整查询，为缺失的列添加占位符（如NULL标记），类似于下面的代码。

SELECT empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY empid, custid



UNION ALL



SELECT empid, NULL, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY empid



UNION ALL



SELECT NULL, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY custid



UNION ALL



SELECT NULL, NULL, SUM(qty) AS sumqty

FROM dbo.Orders;

此代码生成了一个单个结果集，含有被统一的四个分组集的聚合数据。

empid custid sumqty
----------- --------- -----------
2 A 52
3 A 20
1 B 20
2 B 27
1 C 34
3 C 22
3 D 30
1 NULL 54
2 NULL 79
3 NULL 72
NULL A 72
NULL B 47
NULL C 56
NULL D 30
NULL NULL 205
(15 row(s) affected)

尽管你已经达到了目的，此解决方案具有两个主要问题——代码的长度和性能。此解决方案需要为每个分组集指定一个完整的GROUP BY查询，当有很多分组集时，查询可能会很长。此外，为处理查询，SQLServer将会为每个查询分别扫描源表，效率低下。

SQL Server支持几项遵循标准SQL的功能，能够在同一查询中定义多个分组集，包括GROUP BY子句的GROUPING SETS、CUBE和ROLLUP从属子句，以及GROUPING和GROUPING_ID函数。

1. GROUPING SETS 从属子句

GROUPING SETS从属子句是一个对GROUP BY子句的强大增强，主要用于报表和数据仓库。通过使用此从属子句，可以在同一查询中定义多个分组集。只需列出你要定义的分组集，在GROUPING SETS从属子句的括号内以逗号分隔，并且每个分组集列出的成员在其括号内也要以逗号分隔。例如，下面的查询定义四个分组集：(empid, custid)、(empid)、(custid)和()。

SELECT empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY

  GROUPINGSETS

  (

    (empid,custid),

    (empid),

    (custid),

    ()

  );

此查询在逻辑上等效于之前统一了四个聚合查询结果集的解决方案，返回相同的输出。不过，此查询相比之前的解决方案有两个主要优势——显然是它要求更少的代码，并且SQLServer会优化扫描源表的次数，而不是必须为每个分组集单独扫描源表。

2. CUBE从属子句

GROUP BY 子句的CUBE从属子句提供了一种定义多个分组集的简单方式。在CUBE从属子句的括号中，提供了一个以逗号分隔的成员列表后，会得到基于所定义的输入成员的所有可能的分组集。例如，CUBE(a, b, c)等效于GROUPING SETS( (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())。在集合理论中，能够从一个特定集合生成所有的元素子集的集合，称之为幂集。你可以认为CUBE子句就是分组集的幂集，它由给定的元素集合构成。

作为对之前查询中使用GROUPING SETS从属子句定义的四个分组集(empid, custid)、(empid)、(custid)和()的替代，可以简单地使用CUBE(empid, custid)。下面是完整的查询。

SELECT empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY CUBE(empid, custid);

3. ROLLUP从属子句

GROUP BY 子句的ROLLUP从属子句也是提供了一种定义多个分组集的简单方式。然而，与CUBE从属子句不同的是，ROLLUP不会基于输入成员生成能够被定义的所有可能分组集，而只是其中的一部分。ROLLUP假定输入成员之间是一个层次结构，并生成鉴于层次结构意义的所有分组集。换句话说，CUBE(a, b, c)根据三个输入成员生成了所有可能的八个分组集，而ROLLUP(a, b, c)仅生成四个分组集，其假定层次结构为a>b>c，等效于指定了GROUPING SETS( (a, b, c), (a, b), (a), () )。

例如，假设要基于时间层次结构“订单年度>订单月份>订单日”，为所有能够定义的分组集返回订购数量总计，可以使用GROUPING SETS从属子句并显式地列出所有的四个可能分组集。

GROUPING SETS(

 (YEAR(orderdate), MONTH(orderdate), DAY(orderdate)),

 (YEAR(orderdate), MONTH(orderdate)),

 (YEAR(orderdate)),

  () )

此逻辑与使用更为经济的ROLLUP从属子句是等效的。

ROLLUP(YEAR(orderdate), MONTH(orderdate),DAY(orderdate))

下面是你需要运行的完整查询。