Hive之Grouping Sets函数

简单来说：在一个GROUP BY 查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果进行UNION ALL操作。

SETS就是一种将多个GROUP BY逻辑UNION写在一个HIVE SQL语句中的便利写法。GROUPING SETS会把在单个GROUP BY逻辑中没有参与GROUP BY的那一列置为NULL值，这样聚合出来的结果，未被GROUP BY的列将显示为NULL

使用方法：

假如现在又如下场景，a,b,num三个字段，现在要求对a,b字段分别进行统计，有三种情况：(a,b)、(a)、(b)。常规写法我们可能会写成：

SELECT a,b,sum(num) AS total_num
  FROM DW_AAA.BBB
 GROUP BY a,b
 UNION ALL
SELECT a,sum(num) AS total_num
  FROM DW_AAA.BBB
 GROUP BY a
 UNION ALL 
SELECT b,sum(num) AS total_num
  FROM DW_AAA.BBB
GROUP BY B

现在用GROUPING SETS来进行改写：

SELECT 
      a
      b,
      sum(num) AS total_num,
      grouping__id
  FROM DW_AAA.BBB
 GROUP BY a,b
 GROUPING SETS (a,b),(a),(b)

可见代码简洁了很多，并且生成的job数也变少且计算的效率提高了（UNION ALL是多次扫描表）,但是有同学注意到有一个grouping__id,这个是什么作用呢？

grouping__id表示结果属于哪一个分组集合，以二进制的形式表达，那么问题又来了 ，在hivesql和sparksql里二进制的生成方法和规则又不相同，下面具体说下区别：

1、hive中的grouping__id的数字规则
如
select a,b,c,grouping__id from test group by a,b,c
grouping sets ((ab),(ac),(abc))
规则：根据group by 字段 ，从低到高，group sets中出现的字段是1没出现是0
如 （a,b）对应 011
（a,c）对应 101

2、spark中的grouping__id()的数字规则
如
select a,b,c,grouping__id() from test group by a,b,c
grouping sets ((ab),(ac),(abc))
规则：根据group by 字段 ，从高到低，group sets中出现的字段是0没出现是1
如 （a,b）对应 001
（ a,c）对应 010