SQL Server 分组操作解析

本篇文章聊聊分组集(groupingset)的概念，以及在SQLServer中分组织可以支持的一些新功能，示例数据库使用上一篇文章的示例数据库。

 

一.概念

简单来说，分组集就是分组(GROUP BY子句）使用的一组属性（或列名）。在传统的SQL中，一个聚合查询只能定义一个分组集。例如，对于下列4个查询，它们每个都只定义了一个分组集：

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); 最后一个查询定义了一个空的分组集（）。这些代码返回4个结果集（每个查询对应一个结果集）。假设现在不想生成4个单独的结果集，而是希望生成一个统一的结果集，其中包含所有4个分组集的聚合数据。为了实现该目标，可以使用UNIONALL共合运算，将所有4 个查询的结果共合并在一起。由千集合运算要求所有结果集包含相同的列数，对应列的架构定义要保持兼容，所以可能须要对查询进行调整，用增加占位符（比如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; 

虽然设法得到了期望的结果， 但这种解决方案存在两个主要问题：代码长度和性能。 首先，因为须要为每个分组集指定完整的GROU BY查询， 所以当分组集的数蜇很大时， 查询语句可能变得相当长。 其次，为了处理查询， SQL Server须要为每个查询分别单独扫描源表， 导致效率低下。SQL Server 2008引入了很多遵循标准SQL的新功能，能够支持在同一查询语句中定义多个分组集。这些分组集可以是GROUP BY子句的GROUPING SETS、CUBE、ROLLUP 从属子句(subclause), 以及GROUPING_ID函数。

 

二.GROUPING SETS从属子句

GROUPING SETS从属子句是增强GROUP BY子句的有力保证，主要用千生成报表和数据仓库处理。 借助该从属子句，就可以在同一查询中定义多个分组集。 只要简单地在 GROUPING SETS从屈子句的圆括号内列出想要定义的各分组集， 分组媒之间用逗号分隔开 。对于每个分组集， 也在圆括号中列出它们各自的成员， 成员之间用逗号隔开。 例如，下面的查询定义了4个分组集：(empid, custid)、(empid)、(custid)和（）：

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

这个查询与前一种解决方案（对4个聚合查询的结果集进行合并）在逻辑上是等效的，返回相同的输出。 不过，和前一种解决方案相比， 这个查询具有两大主要优势： 一是需要的代码明显少得多；二是SQLServer能够优化扫描源表的次数， 不须要为每个分组集单独对源表进行扫描。对于SQLServer 2008之前的版本，除了显式合并多个聚合查询的结果集以外，没有其他在逻辑上与GROUPING SETS从属子句等价的方法。

 

三.CUBE 从属子句

CUBE从属子句为定义多个分组集提供了一种简略的方法。在CUBE从属子句的圆括号中， 只须要列出由逗号分隔开的元素成员， 就可以得到基千输入成员 而定义的所有可能的分组集。例如，CUBE(a, b, c)与GROUPING SETS [ (a, b, c) , (a, b) , (a, c) , (b, c) , (a) , (b) , (c) ,())等效。 在集合论中， 由给定集合的所有子集构成的集合称为幂集(powerset) 。 对于由给定元素构成的一个分组集（如，(a, b, c)) , 可以把CUBE从属子句看作是用于生成这个分组集的幕集。除了像前面的查询那样用GROUPING SETS从属子句定义4个分组共： (empid, custid)、(empid)、 (custid)和（）， 也可以简单地使用CUBE(empid, custid)来实现这个查询。 下面给出完整的查询：

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

 

四.ROLLUP从属子句

ROLLUP从属子句也提供了一种定义多个分组集的简略方法。 不过， 与CUBE从属子句不同的是， ROLLUP并不是生成基于输入成员而定义的所有可能的分组集， 而只是生成其中的一个子集 。 ROLLUP认为输入成员之间存在一定的层次关系， 从而生成让这种层次关系有意义的所有分组集。 换句话说， CUBE (a, b, c)生成由3个输入成员得到的所有 8 个可能的分组找；而ROLLUP认为这 3 个输入成员存在a>b>c的层次关系，所以只生成4个分组焦，在效果上相当于指定了GROUPING SETS ((a, b, c), (a, b), (a),()) 。
例如， 假设现在想按时间层次关系： 订单年份＞订单月份＞订单日， 以这样的关系来定义所有分组集， 并为每个分组织返回其总订货蜇。 如果使用GROUPING SETS从属子句， 就得显式列出所有可能的4个分组集：

GROUPING SETS( 
(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)), 
(YEAR(orderdate), MONTH(orderdate)), 
(YEAR(orderdate)), 
())

而使用逻辑上等效的ROLLUP从屈子句， 则要更精简得多：

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

以下是完整的查询语句：

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));

 

五.GROUPING和GROUPING_ID函数

如果一个查询定义了多个分组集，可能还想能够把结果行和分组共关联起来，也就是说，为每个结果行标识出它是和哪个分组织关联的。只要所有分组元素都定义为NOT NULL。 例如，考虑下面的查询：

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

因为Orders表的 empid和custid列都定义为NOTNULL, 这些列中的NULL值只代表一个占位符， 表示该列并不属于当前的分组集。 所以， 所有empid 和custid均不为NULL 的行都与分组集(empid,custid)相关联；所有empid不为NULL、 custid为NULL的行都与分组集(empid)有关联，以此类推。 有些人使用ALL或其他类似的标志来代替NULL （假设原始列不允许为NULL), 这种做法对生成报表很有用。但是， 如果表中的分组列定义为允许取NULL值， 这时就无法区分结果集中的NULL是来自原始数据， 还是占位符（表示该列不是分组集的成员）。 如果想以确定性的方式来判断分组集的关联（即使分组列允许为NULL) , 一种方法就是使用GROUPING函数。 这个函数接受一个列名， 如果该列是当前分组织的成员， 就返回 o. 否则返回l。 例如， 以下查询为每个分组元素调用GROUPING函数：

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

现在就不须要再依靠NULL来计算结果行和分组集之间的关联了。例如，所有grpemp 为0,而且grpcust也为0的行与分组集(empid,custid)关联；所有grpemp为o,而且 grpcust为1的行与分组集(empid)关联，以此类推。

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！

 

转载于:https://www.cnblogs.com/johnvwan/p/9492243.html