- 点击上方
“中国统计网”
订阅我吧！-
这一篇讲讲
group by plus
，大家应该都知道
group by
，可是 plus 是什么鬼呢？
其实和
iphone plus
一样，就是升级版的意思。
那到底这个 plus是个什么东西呢？
我们接下来慢慢讲。
01 前言
我们先来看个数据需求场景，现在我有一张明细表，这张表里面存储了每个店铺的成交明细，其中包含每个店铺所属的城市、地区、大区属性，我需要通过这张明细表获取到每个店铺、每个城市、每个省份、每个大区以及全国在最近一个月的成交量情况，我该怎么做呢？
明细表 t 如下：
有一种最简单的方法就是，我们写5个 Sql 语句，然后将数据导出来在 Excel 中处理。
5个 Sql 语句如下：
想一下，我们要做上面的那个需求，我们应该怎么做呢？
全国成交量
select count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"
大区成交量
select area ,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area
省份成交量
select area ,province ,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area ,province
城市成交量
select area ,province ,city ,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area ,province ,city
店铺成交量
select area ,province ,city ,shop ,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area ,province ,city ,shop
上面这种方法可以达到我们的目的，满足我们的需求，但是这种方法太低效了，我们在Excel中还需要做合并处理，很麻烦。
能不能把上面几种结果在 Sql 中就进行合并处理，这样就不需要在 Excel 中合并了。
答案是可以的，需要借助的就是 union和 union all，对查询结果进行纵向合并。
union 和 union all的区别在于：
前者是对合并后的结果进行去重处理，而后者返回合并后的所有数据。
具体代码如下：
select null,null,null,null,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"
union all
select area,null,null,null,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area
union all
select area,province,null,null,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area,province
union all
select area,province ,city,null,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area,province,city
union all
select area,province,city,shop,count(orderid) as sales from t where deal_date between "2019-05-01" and "2019-05-31"group by area,province ,city,shop
大家应该注意到上面的语句中 select 了很多 null，那是因为 union all拼接的两个表的列数需要相等。
最后出来的结果如下：
02 grouping sets
利用
union all
要比导出5个Sql然后再在 Excel 中处理简单多了，但是有没有发现上面的代码很长，很冗余。
有人发现了，有人不仅发现了，还想出了一种更好的方法去解决，具体是什么方法呢？
就是我们今天要讲的group by的 plus 版。
真名叫做 grouping sets。
这个 plus 可以根据不同维度组合进行聚合。
比如根据大区聚合、根据大区和省份聚合、根据大区省份和城市聚合、根据大区省份城市和店铺聚合。
将上面 union all 语句用 grouping sets 改写以后，代码如下：
select null ,area ,province ,city ,shop ,count(orderid) as sales ,grouping_idfrom t where deal_date between "2019-05-01" and "2019-05-31"group by null ,area ,province ,city ,shopgrouping sets (null ,area ,(area,province) ,(area,province,city) ,(area,province ,city,shop))order by grouping_id
上面代码得到的效果和利用
union all
拼接得到的效果是一样的，但是要比拼接的代码简洁很多。
group by
后面放的字段表示要分组聚合的全部字段，
grouping sets
后面放的是
group by
后面各种字段的组合，根据实际需求进行组合就行，组合字段用小括号括起来，也可以是单一字段。
在求取全国的成交量的时候其实是不需要分组聚合的，但是为了使用
grouping sets
，所以我们在求取全国成交量的时候用
group by null
。
grouping_id
用来表示每个分组的序号。
1表示第一个分组、2表示第二个分组。
我们可以根据
grouping_id
选取出我们需要的组合。
如果我们需要全国的成交量，让
grouping_id = 1
即可；
如果我们需要每个省份的成交量，让
grouping_id = 3
即可。
其他也是同样的道理。
03 cube
看完
grouping sets
后，我们再来看另一个 plus 版，就是 cube。
这个函数是对 group by 的维度的所有组合进行聚合。
直接来看代码：
select area ,province ,count(orderid) as sales ,grouping_id from t where deal_date between "2019-05-01" and "2019-05-31"group by area ,provincewith cubeorder by grouping_id
上面代码是对区域和省份进行聚合，并利用了 cube ，最后得到的结果如下：
