Hive cube / rollup / grouping sets/GROUPING__ID用法详解

cube / rollup / grouping sets/GROUPING__ID，经常会被问到这几个函数的区别，今天就好好整理一下。

GROUPING SETS

grouping sets用法是给group by做辅助作用的函数，在SQL中的位置是跟在group by后面。grouping sets括号中的参数必须是group by后面的字段：group by 字段1，字段2 grouping sets（字段1，字段2）。使用grouping sets的区别在于，group by会根据括号中的字段进行分组去重，然后根据select中的聚合函数对字段聚合，而加上grouping sets相当于把括号中的字段分别进行上述group by操作，然后进行union all聚合。我们来实际操作举例说明一下：

• 准备数据

  select * from tab1;
  

结果：

month	day	values
2015-03	2015-03-10	value1
2015-03	2015-03-10	value5
2015-03	2015-03-12	value7
2015-04	2015-04-12	value3
2015-04	2015-04-13	value2
2015-04	2015-04-13	value4
2015-04	2015-04-16	value4
2015-03	2015-03-10	value2
2015-03	2015-03-10	value3
2015-04	2015-04-12	value5
2015-04	2015-04-13	value4
2015-04	2015-04-15	value3
2015-04	2015-04-15	value2
2015-04	2015-04-16	value1

使用grouping sets

SELECT 
	month,
	day,
	COUNT(DISTINCT values) AS uv,
FROM tab1
GROUP BY month,day 
GROUPING SETS (month,day) ;

等价于

SELECT month,NULL,COUNT(DISTINCT values) AS uv FROM tab1 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT values) AS uv FROM tab1 GROUP BY day;

输出结果都是一样的

<

month	day	uv