SQL—解决多维度随机组合查询场景：grouping sets函数

一、引入

        注意：通常用在构建数据集市和复杂随机组合场景查询时使用

        对于经常需要对数据进行多维度的聚合分析的场景，您既需要对A列做聚合，也要对B列做聚合，同时要对A、B两列做聚合，因此需要多次使用union all。

        案例：        

        比如此处有一张表temp：

        temp表字段：城市、渠道、订单类型、销售额；

        temp表数据：

城市	渠道	订单类型	订单金额
北京	淘宝	家具	1600
上海	淘宝	家具	900
北京	淘宝	数码	1800
上海	淘宝	数码	700
北京	京东	数码	800
上海	京东	数码	1600
北京	京东	家具	1000
上海	京东	家具	800

        业务需求：求渠道和订单类型两个维度随机组合下的订单总金额

        通常的解决方案，我们是先按渠道和订单类型做聚合求订单总金额，再按订单类型维度做聚合求订单总金额，再按渠道类型维度做聚合求订单总金额，之后求渠道和订单类型下的订单总金额，最后四个结果union在一起：

        代码：

select 渠道, '全部订单类型' 订单类型,sum(订单金额)
from temp
group by 渠道

union all

select '全部渠道' 渠道,订单类型,sum(订单金额)
from temp
group by 订单类型

union all

select 渠道,订单类型,sum(订单金额)
from temp
group by 渠道,订单类型

union all

select '全部渠道' 渠道,'全部订单类型' 订单类型,sum(订单金额)
from temp
；

        结果：

渠道	订单类型	订单总金额
全部渠道	数码	4900
全部渠道	家具	4300
全部渠道	全部订单类型	9200
京东	全部订单类型	4200
淘宝	全部订单类型	5000
京东	数码	2400
京东	家具	1800
淘宝	数码	2500
淘宝	家具	2500

        当然这只是涉及两个维度的随机组合，那试想一下如果是涉及n个维度呢，那组合方式岂不是有种组合，写如此多的union显然对于我们代码开发和运行带来极大的不便，本文将给大家介绍一种对于经常需要对数据进行多维度的聚合分析的场景下非常好用的方法——grouping sets函数。

二、grouping sets函数

        其实grouping sets就是由多个group by联合起来，关系如下。　　

select A , B from table group by grouping sets(A, B)  

等价于

select A , null as B  from table group by A

union all

select null as A ,  B  from table group by B

        针对于引入中的问题，用grouping sets函数：        

        代码：

select 
    渠道
    ,订单类型
    ,sum（订单金额）as 订单总金额
from temp
group by 渠道,订单类型
grouping sets(渠道,订单类型)

        结果：

渠道	订单类型	订单总金额
null	数码	4900
null	家具	4300
null	null	9200
京东	null	4200
淘宝	null	5000
京东	数码	2400
京东	家具	1800
淘宝	数码	2500
淘宝	家具	2500

        可以看出，通过grouping sets函数，极大提高我们的开发效率，即使有更多维度组合的增加，只需要在grouping sets函数中新增即可，当然案例中姓名不具有实质性分组聚合意义，数据的展示问题需要做进一步处理，但相较于大量的union，grouping sets函数已经可以极大的对代码进行调优。

        当然grouping sets函数也可以有其他使用方法：只查询渠道存在、订单类型存在的结果：

        代码：

select 
    渠道
    ,订单类型
    ,sum（订单金额）as 订单总金额
from temp
group by 城市,渠道,订单类型
grouping sets(渠道,订单类型)

        结果：

渠道	订单类型	总金额
null	数码	4900
null	家具	4300
null	null	9200
京东	null	4200
淘宝	null	5000

三、其他函数 

3.1 grouping( )

　　grouping函数用来区分NULL值，这里NULL值有2种情况，一是原本表中的数据就为NULL，二是由字段是否选中生成的NULL值。

        代码：

select 
    渠道
    ,订单类型
    ,sum(订单金额) as 订单总金额
    ,grouping(渠道) ga
    ,grouping(订单金额) gb
from temp
group by 城市,渠道,订单类型
grouping sets(渠道,订单类型)

        结果：

渠道	订单类型	订单总金额	ga	gb
null	数码	4900	1	0
null	家具	4300	1	0
null	null	9200	1	1
京东	null	4200	0	1
淘宝	null	5000	0	1
京东	数码	2400	0	0
京东	家具	1800	0	0
淘宝	数码	2500	0	0
淘宝	家具	2500	0	0

 3.2 grouping_id( )

　　grouping_id函数也是计算分组级别的函数，注意如果要使用grouping_id函数那必须得有group by字句，而且group by字句的中的列与grouping_id函数的参数必须相等。比如group by A,B，那么必须使用grouping_id（A,B）。下面用一个等效关系来说明grouping_id()与grouping()的联系，grouping_id(A, B)等效于grouping(A) + grouping(B)，但要注意这里的+号不是算术相加，它表示的是二进制数据组合在一起，比如grouping（A）=1，grouping（B）=1，那么grouping_id(A, B)=11B，也就是十进制数3。

   代码：

select 
    渠道
    ,订单类型
    ,sum(订单金额) as 订单总金额
    ,grouping(渠道) ga
    ,grouping(订单金额) gb
    ,grouping_id（渠道,订单金额）gab
from temp
group by 城市,渠道,订单类型
grouping sets(渠道,订单类型)

        结果：

渠道	订单类型	订单总金额	ga	gb	gba
null	数码	4900	1	0	1
null	家具	4300	1	0	1
null	null	9200	1	1	0
京东	null	4200	0	1	2
淘宝	null	5000	0	1	2
京东	数码	2400	0	0	3
京东	家具	1800	0	0	3
淘宝	数码	2500	0	0	3
淘宝	家具	2500	0	0	3

3.3 rollup和cube

        另外 group by rollup() 和 group by cube() 也和group by grouping sets() 有类似的用法。rollup是cube的一种特殊情况，和rollup一样，cube也是根据维度在分组的结果集中进行聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作。 带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。

假设有n个维度，rollup会有n个聚合：
rollup(a,b) 统计列包含：(a,b)、(a)、()
rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、()
……以此类推ing……

假设有n个纬度，cube会有2的n次方个聚合：
cube(a,b) 统计列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
……以此类推ing……