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

在SQL Server 2012中如何使用分组集分组集就是你据以分组的一个属性集。传统上，SQL中的单个聚合查询定义一个单个分组集。例如，下面的四个查询每个定义了一个单个分组集。?123456789101112131415161718192021222324252627SELECT empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY empid, custid;SELECT empid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY empid;SELECT custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY custid;SELECT SUM(qty) AS sumqtyFROM dbo.Orders;第一个查询定义了分组集(empid，custid)，第二个是(empid)，第三个是(custid)，最后一个查询定义了空分组集()。此代码返回四个结果集，每个查询一个。假设不是要四个单独的结果集，而是想要一个统一了四个分组集的所有聚合数据的单个结果集，可以使用UNION ALL集合运算符组合四个查询的结果集，实现此目标。由于集合运算符要求所有结果集需要具有相同列数的兼容架构，你需要调整查询，为缺失的列添加占位符（如NULL标记），类似于下面的代码。?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960SELECT empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY empid, custidUNION ALLSELECT empid, NULL, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY empidUNION ALLSELECT NULL, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY custidUNION ALLSELECT NULL, NULL, SUM(qty) AS sumqtyFROM dbo.Orders;此代码生成了一个单个结果集，含有被统一的四个分组集的聚合数据。empid custid sumqty- - -2 A 523 A 201 B 202 B 271 C 343 C 223 D 301 NULL 542 NULL 793 NULL 72NULL A 72NULL B 47NULL C 56NULL D 30NULL 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)和()。?12345678910111213141516171819SELECT empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BYGROUPINGSETS(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)。下面是完整的查询。?12345SELECT empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY CUBE(empid, custid);3. ROLLUP从属子句GROUP BY 子句的ROLLUP从属子句也是提供了一种定义多个分组集的简单方式。然而，与CUBE从属子句不同的是，ROLLUP不会基于输入成员生成能够被定义的所有可能分组集，而只是其中的一部分。ROLLUP假定输入成员之间是一个层次结构，并生成鉴于层次结构意义的所有分组集。换句话说，CUBE(a, b, c)根据三个输入成员生成了所有可能的八个分组集，而ROLLUP(a, b, c)仅生成四个分组集，其假定层次结构为abc，等效于指定了GROUPING SETS( (a, b, c), (a, b), (a), () )。例如，假设要基于时间层次结构“订单年度订单月份订单日”，为所有能够定义的分组集返回订购数量总计，可以使用GROUPING SETS从属子句并显式地列出所有的四个可能分组集。?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455GROUPING SETS(YEAR(orderdate), MONTH(orderdate), DAY(orderdate),(YEAR(orderdate), MONTH(orderdate),(YEAR(orderdate),() )此逻辑与使用更为经济的ROLLUP从属子句是等效的。ROLLUP(YEAR(orderdate), MONTH(orderdate),DAY(orderdate)下面是你需要运行的完整查询。SELECTYEAR(orderdate) AS orderyear,MONTH(orderdate) AS ordermonth,DAY(orderdate) AS orderday,SUM(qty) ASsumqtyFROM dbo.OrdersGROUP BY ROLLUP(YEAR(orderdate), MONTH(orderdate),DAY(orderdate);此查询会生成下面的输出。orderyear ordermonth orderday sumqty- - - -2007 4 18 222007 4 NULL 222007 8 2 102007 8 NULL 102007 12 24 322007 12 NULL 322007 NULL NULL 642008 1 9 402008 1 18 142008 1 NULL 542008 2 12 122008 2 NULL 122008 NULL 2009 2 12 102009 2 16 202009 2 NULL 302009 4 18 152009 4 NULL 152009 9 7 302009 9 NULL 302009 NULL NULL 75NULL NULL NULL 205NULL 664. GROUPING和GROUPING_ID函数当有一个定义了多个分组集的单独查询时，你可能需要能够关联结果行与分组集，即确定与每个结果行相关联的分组集。只要所有分组元素定义为NOT NULL，这是很容易的。例如，请考虑下面的查询。?12345678910111213141516171819202122232425SELECT empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY CUBE(empid, custid);此查询会生成以下输出。empid custid sumqty- - -2 A 523 A 20NULL A 721 B 202 B 27NULL B 471 C 343 C 22NULL C 563 D 30NULL D 30NULL NULL 2051 NULL 542 NULL 793 NULL 72因为empid和custid列在dbo.Orders中定义为NOT NULL，这些列中的NULL仅代表一个占位符，指示该列没有参与当前的分组集。那么，例如，empid不为NULL且custid不为NULL的所有行是与分组集(empid, custid)相关联的，empid不为NULL且custid为NULL的所有行是与分组集(empid)相关联的，依此类推。某些人以ALL或类似占位符覆盖NULL标志，就是要原始列不为空，这对报表是有帮助的。但是，如果分组列在表中定义为允许NULL标记，你就不能确定结果集中的NULL是源自数据还是一个不参与分组集成员的占位符。一种能够判断与分组集相关联的确定方式（即使分组列允许NULL标记），是使用GROUPING函数。此函数接受一个列名称，如果该列是当前分组集的成员，返回0，否则返回1。注意我觉得反常的是，GROUPING函数在元素不是分组集成员时返回1，在是的时候返回0。对我来说，在元素是分组集的成员时返回1（即true），不是时返回0，这样会更加清晰。不过，函数就是这么实施的，所以你只需确保已经意识到这一问题即可。例如，下面的查询为每个分组元素调用了GROUPING函数。?1234567891011121314151617181920212223242526272829SELECTGROUPING(empid) AS grpemp,GROUPING(custid) AS grpcust, empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY CUBE(empid, custid);此查询返回下面的输出。grpemp grpcust empid custid sumqty- - - - -0 0 2 A 520 0 3 A 201 0 NULL A 720 0 1 B 200 0 2 B 271 0 NULL B 470 0 1 C 340 0 3 C 221 0 NULL C 560 0 3 D 301 0 NULL D 301 1 NULL NULL 2050 1 1 NULL 540 1 2 NULL 790 1 3 NULL 72现在你再也不需要依靠NULL标记来判断结果行和分组集之间的相关性了。例如，grpemp为0且grpcust为0的所有行是与分组集(empid, custid)相关联的，grpemp为0且grpcust为1的所有行是与分组集(empid)相关联的，依此类推。SQL Server支持另一个名为GROUPING_ID的函数，可以进一步简化结果行与分组集的关联处理。将参与任何分组集的所有元素作为函数的输入，例如GROUPING_ID(a, b, c, d)，该函数返回一个整数位图，其中每位代表一个不同的输入元素，最右边的元素由最右边的位表示。例如，分组集(a, b, c, d)用整数0表示（即08+04+02+01），分组集(a, c)用整数5表示（即08+14+02+11），依此类推。作为对之前查询中对每个分组元素调用GROUPING函数的替代，可以调用GROUPING_ID函数一次将所有分组元素提供给它作为输入，如下所示。?123456789101112131415161718192021222324252627SELECTGROUPING_ID(empid, custid) AS groupingset, empid, custid, SUM(qty) AS sumqtyFROM dbo.OrdersGROUP BY CUBE(empid, custid);此查询会生成下面的输出。grou