SQL：grouping sets详解

目录

数据介绍

1、基本group by语法

2、grouping sets

3、利用grouping__id和二进制判断整体

3.1、grouping__id的生成逻辑

3.2、SQL中的"&"，即：二进制判断

3.3、根据grouping__id的值进行二进制判断

最后的延伸：小白鼠喝药的考题

---

数据介绍

现有一份记录某城市下各个行业年度GDP数据，现在需要按照日期，城市，产业类型进行分组统计，汇总数据需要按照如下各种细分粒度整理：

1. 按日期计算每日GDP总额
2. 按日期，城市计算每日每城市GDP总额
3. 按日期，产业计算每日产业大类下GDP总额
4. ......

1、基本group by语法

select dt,city,cate,sum(gdp)as num
 from table_name
 group by dt,city,cate

按照这样的写法，只能得到最细分粒度的数据，即按日期、城市、产业类型粒度进行汇总得到的数据，这样有两个问题：

1、如果想看按日粒度的整体数据，需要再次进行处理，非常麻烦

2、如果存在重复数据（本例中不存在），按照最细粒度去重汇总之后，无法利用分组去重之后的数据，继续向上汇总。

例如：统计下单客户数，分组字段包括日期，商品id，如果计算出每日每个商品有多少客户下单之后，还想看每日有多少客户下单，是不能直接加总处理的，因为可能存在一个客户购买多单的问题

2、grouping sets

基于上述的问题，如果想要一次性得到多种分组粒度统计的数据，就需要使用grouping sets来处理。把想要的各种分组方式以（字段1,字段2）的方式写入grouping sets()中，分组方式按，区分即可，如下代码：

select dt,city,cate,sum(gdp)as num
 from table_name
 group by dt,city,cate
 grouping sets
 (
 (dt),
 (dt,city),
 (dt,cate),
 (dt,city,cate)
 )

这里需要注意，如果想要得到一个不按任何字段分组的整体数据值，可以在grouping sets中加入一个空括号（），这是最粗粒度的分组

select dt,city,cate,sum(gdp)as num
 from table_name
 group by dt,city,cate
 grouping sets
 (
 (),
 (dt),
 (dt,city),
 (dt,cate),
 (dt,city,cate)
 )

使用grouping sets处理完的数据就是下表样式

grouping sets	dt	city	cate	统计值
()	NULL	NULL	NULL
(dt)	实际值	NULL	NULL
(dt,city)	实际值	实际值	NULL
(dt,cate)	实际值	NULL	实际值
(dt,city,cate)	实际值	实际值	实际值
...
(city)	NULL	实际值	NULL

这里可以看到，不参与分组的字段在展示时被赋予了NULL值，这里可不可以采用case when语句直接把NULL值替换为'整体'或者’all‘等字符串呢？这要取决于你的原始数据中是否存在自然的null值，例如某个行业不归属任何一个产业大类，这里产业大类就会记录一个NULL值，这种情况下，如果按照上述方式处理就会让数据完全混乱。那么怎么实现将不参与分组的字段值命名为整体呢？，这就需要使用到grouping__id和二进制的概念。

3、利用grouping__id和二进制判断整体

3.1、grouping__id的生成逻辑

按照group by的顺序，从左到右判断分组维度存在为0，不存在为1，

本例中，group by 的顺序是dt，city，cate，按照这个顺序生成一个三位的二进制数字，并将其转换为十进制，就是grouping__id的值了（示例：如下表）

分组维度	grouping_id十进制	grouping_id二进制	dt	city	cate
group by dt,city,cate	0	000	0	0	0
group by dt,city	1	001	0	0	1
group by dt,cate	2	010	0	1	0
group by dt	3	011	0	1	1
......以此类推
group by city,cate	4	100	1	0	0
group by city	5	101	1	0	1
group by cate	6	110	1	1	0
group by ()	7	111	1	1	1

3.2、SQL中的"&"，即：二进制判断

这里需要介绍一个知识点：SQL中的二进制判断

num1 & num2

运算符查看两个参数的二进制表示法的值，并执行按位”与”操作。两个表达式的同一位均为1时，则结果的该位为 1。否则（也就是两个二进制数据每一位都没有同时为1），结果为 0。

如

100 & 110=100

001 & 111 = 001

000 & 001=000

010 & 100=000

...... 

3.3、根据grouping__id的值进行二进制判断

现在把上面两个知识点结合起来，来判断分组字段是否应该被重命名为整体吧

首先还是按照group by 的顺序，从左到右依次将其判定为1，即：

dt	city	cate	二进制结果	十进制结果
0	0	1	001	1
0	1	0	010	2
1	0	0	100	4

 然后将grouping__id分别和4、2、1进行二进制判断，得到dt，city，cate是否为整体

下面以city（十进制数字：2）为例进行说明，如果:

• grouping__id & 2=0，那么city字段的取值就是实际值
• grouping__id & 2<>0，那么city字段的取值就是整体

select 
if(cast(grouping__id as BIGINT) & 4 = 0, dt,'all' ),
if(cast(grouping__id as BIGINT) & 2 = 0, city,'all' ),
if(cast(grouping__id as BIGINT) & 1 = 0, cate,'all' ),
dt
,city
,cate
,grouping__id
,conv(grouping__id,10,2) as num
,sum(gdp)
from table
group by dt,city,cate
grouping sets(
(dt),
(dt,city),
(dt,cate),  
(dt,city,cate)
)

根据结果可知，这样的判断方法完美的实现了我们的目标。 

分组维度	grouping_id十进制	grouping_id二进制	和010判断	city值
group by dt,city,cate	0	000	000	实际值
group by dt,city	1	001	000	实际值
group by dt,cate	2	010	010	整体
group by dt	3	011	010	整体
......以此类推
group by city,cate	4	100	000	实际值
group by city	5	101	000	实际值
group by cate	6	110	010	整体
group by ()	7	111	010	整体

最后的延伸：小白鼠喝药的考题

之前面试的时候，面试官考过一道小白鼠喝毒药的案例，当时觉得挺莫名其妙的，但是在使用grouping sets的时候才明白，原来这道题的原理在于验证我们是否有熟练使用二进制的工具解决日常计算问题的能力。

这道题的题目以及说明大家可以搜索小白鼠喝药，很多大神给出了非常详细的解答，有兴趣的可以了解一下。