37 分组集
37.1 分组集的定义
是多个分组的并集,用于在一个查询中,按照不同的分组列对集合进行聚合运算,等价于对单个分组使用“UNION ALL”,计算多个结果集的并集。
37.2 分组集种类
SQL Server的分组集共有三种GROUPING SETS,CUBE以及ROLLUP,其中CUBE和ROLLUP可以当做是GROUPING SETS的简写版。
37.3 GROUPING SETS
GROUPING SETS子句允许你指定多个GROUP BY选项。增强了GROUP BY的功能。
可以通过一条SELECT语句实现复杂繁琐的多条SELECT语句的查询。并且更加的高效,解析存储一条SQL于语句。
GROUPING SET示例:
先对城市和省份进行分组,统计出他们的数量,
SELECT 城市,NULL 省份,COUNT(城市) FROM Customers GROUP BY 城市 UNION ALL SELECT NULL,省份,COUNT(省份) FROM Customers GROUP BY 省份;
结果为:
再使用GROUPING SETS来统计,
SELECT 城市, 省份, COUNT(客户ID) 数量 FROM Customers GROUP BY GROUPING SET(城市,省份);
结果如下:
其实上下两个结果是一样的,只是UNION ALL不排序,而GROUPING SETS增加了排序。这样不仅减少了代码,而且这样的效率会比UNION ALL的效率高。通常GROUPING SETS使用在组合分析中。
37.4 ROLLUP
ROLLUP也是GROUPING SETS的一种简略写法,举例说明一下。
先使用GROUPING SETS的多层组合:
SELECT 省份, 城市, COUNT(1) 数量 FROM Customers GROUP BY GROUPING SETS ( 省份,(省份,城市) )
其结果为:
使用ROLLUP可以这样写:
SELECT 省份, 城市, COUNT(客户ID) 数量 FROM Customers GROUP BY 省份,城市 WITH ROLLUP;
其结果为:
ROLLUP的作用是对每个列先进行一次分组,并且对第一列的数据在每个组内还进行一次汇总,最后对所有的数据再进行一次汇总,所以相比GROUPING SETS会多了个数据汇总。这个在对组内进行聚合时是经常使用到的。
37.5 CUBE
CUBE相比ROLLUP多一个维度,举例说明。
SELECT 省份, 城市, COUNT(客户ID) 数量 FROM Customers GROUP BY 省份,城市 WITH CUBE;
结果如下:
在ROLLUP的基础上,还会将第一列每组的汇总数据额外显示在最后。
37.6 批注
分组集类似于Excel的透视图,可以对各类数据进行组内计算,不止可以进行数量统计,也可以进行求和,最大最小值等操作。是我们在进行数据分析时经常使用到的一组功能。