spark sql 自定义计算列（sparksql多维度分析之groupingsetsrollup）

在平时的工作中，经常有按照不同维度筛选和统计数据的需求。拿某信贷公司的进件数据来说吧，运营人员想要查看某一天或某城市的成功进件数量或者某城市某一渠道的成功进件数量或者某一渠道的所有成功进件数量时，每天的进件量又很大，现查的话按照不同的维度去查询又很慢。此时本篇文章或许会帮助到你。

group by:主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。可以添加聚合函数。

举个栗子：

select due_date,status,count(*) from bill where due_date in ('2020-06-01','2020-06-02') group by due_date,status order by 1,2

查询结果

grouping sets：对分组集中指定的组表达式的每个子集执行group by，group by A,B grouping sets(A,B)就等价于 group by A union group by B,其中A和B也可以是一个集合，比如group by A,B,C grouping sets((A,B),(A,C))。

举个栗子：

select due_date,status,count(*) from bill where due_date in ('2020-06-01','2020-06-02') group by grouping sets(due_date,status) order by 1,2

查询结果：

rollup：在指定表达式的每个层次级别创建分组集。group by A,B,C with rollup首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对全表进行group by操作。

举个栗子：

select due_date,status,count(*) from bill where due_date in ('2020-06-01','2020-06-02') group by due_date,status WITH CUBE order by 1,2

查询结果：

cube:为指定表达式集的每个可能组合创建分组集。首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。

举个栗子：

select due_date,status,count(*) from bill where due_date in ('2020-06-01','2020-06-02') group by due_date,status WITH CUBE order by 1,2

查询结果：

grouping():区分null值

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

　 当为第一种情况中的空值时，grouping(NULL)返回0；当为第二种情况中的空值时，grouping(NULL)返回1

grouping只能有一个参数

注意：grouping 函数只能和grouping_sets,roullup,cube函数一起使用，否则会报错

以上面rollup的结果集为例：

select case when grouping(due_date)=1 then 'Roll_Up' else due_date end as due_date ,case when grouping(status)=1 then 'Roll_Up' else status end as status ,count(*) as cnt from bill where due_date in ('2020-06-01','2020-06-02') group by due_date,status with rollup order by 1,2

查询结果：

由此可比较出上面rollup结果集中为null的数据由于grouping函数为1，故显示Roll_Up字符串。

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 grouping(due_date) ,grouping(status) ,grouping_id(due_date,status) ,due_date ,status ,count(*) as cnt from bill where due_date in ('2020-06-01','2020-06-02') group by due_date,status with cube

查询结果: