【十八掌●武功篇】第十掌：Hive中的Grouping Sets

自从Hive 0.10.0版本起，Hive增加了Grouping Sets、Cube、rollup操作和Grouping_ID函数。

1、Grouping Sets

可以在Group By语句后面添加Grouping Sets语句，以实现对同一个数据集上同时进行多组的group by操作。可以理解为多个group by 语句进行union操作。可以参考下面的例子：

Grouping 语句	等效的group by语句
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )	SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)	SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) )	SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)	SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)	SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b

2、Grouping_ID函数

因为Grouping sets相当于多个group by的结果进行union操作，规则是：结果中没有参与group by的列值为NULL，参与group by的列的值为group by的值。但是有些参与group by的数据本身就是Null，那么就分不清了。

这个时候，grouping_id函数就可以标识出哪个字段参与了group by，组织一个二进制的序列，一一对应group by语句后面的各个字段，在Hive2.3.0之前的版本中，如果这个字段参与了group by ，那么对应的位置是0，没有参与group by 的字段相应位置为1。grouping_id函数的返回值就是这个二进制序列对应的十进制数。

在Hive2.3.0和之后的版本中，0和1的规则是相反的。

看下面的例子：

有一个表叫tmp_grouping_set,有三个字段key、value、col，数据如下表所示：

Key	Value	Col
1	NULL	1
1	1	1
2	2	1
3	3	1
3	NULL	1
4	5	1

语句1：

SELECT key, value,col, GROUPING__ID, count(*)
FROM tmp_groupping_set
GROUP BY key, value,col
grouping sets(
(key)
);

结果为：

key	value	col	grouping_id	count
1	NULL	NULL	3	2
2	NULL	NULL	3	1
3	NULL	NULL	3	2
4	NULL	NULL	3	1

这个语句group by的是key、value、col三个字段，所以结果中有这三个字段，grouping set只是key字段，那么结果中key有值的，value、col都是null。

根据上面说的规则，key参与了group by，第一位是0，value和col没有参与group by ，第二位和第三位是1，那么二进制011对应的十进制值为3。

语句2：

SELECT key, value,col, GROUPING__ID, count(*)
FROM tmp_groupping_set
GROUP BY key, value,col
grouping sets(
(key,value)
);

结果为：

Key	Value	Col	Grouping_id	count
1	NULL	NULL	1	1
1	1	NULL	1	1
2	2	NULL	1	1
3	NULL	NULL	1	1
3	3	NULL	1	1
4	5	NULL	1	1

这个参与group by 的是第一个和第二个，那么前两个位置是0，第三位是1，001对应的十进制是1。

3、Grouping函数

自从Hive2.3.0增加了grouping函数，返回值是当前group by下，不同组合的grouping_id的值。

4、Cube、Rollup

Group By后面可以添加Cube关键字，对group by中指定的多个字段进行任意组合进行group by。

比如：Group By a,b,c with cube 就相当于 group by a,b,c grouping sets ((a,b,c),(a,b),(b,c),(a,c),(c),())

Group By后面添加Rollup关键字，可以实现对于group by的各个字段进行从右到左递减的统计。

比如 Group By a,b,c with rollup 就相当于group by a,b,c grouping sets((a,b,c),(a,b),(a),())