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

Hive CUBE / ROLLUP / GROUPING SETS / GROUPING__ID用法详解

1. GROUPING SETS
2. GROUPING__ID（注意这里是两个下划线）
3. CUBE
4. ROLLUP

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	value
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

1.使用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
2015-03	NULL	5
2015-04	NULL	6
NULL	2015-03-10	4
NULL	2015-03-12	1
NULL	2015-04-12	2
NULL	2015-04-13	3
NULL	2015-04-15	2
NULL	2015-04-16	2

2.GROUPING__ID（注意这里是两个下划线）

GROUPING__ID，表示结果属于哪一个分组集合，一般和GROUPING SETS一起使用。
例如：

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

查询结果：

month	day	uv	GROUPING__ID
2015-03	NULL	5	1
2015-04	NULL	6	1
NULL	2015-03-10	4	2
NULL	2015-03-12	1	2
NULL	2015-04-12	2	2
NULL	2015-04-13	3	2
NULL	2015-04-15	2	2
NULL	2015-04-16	2	2

也可以这样查询

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

--等价于

SELECT month
,NULL
,COUNT(DISTINCT values) AS uv
,1 AS GROUPING__ID FROM tab1 
GROUP BY month 
UNION ALL 
SELECT NULL
,day
,COUNT(DISTINCT values) AS uv
,2 AS GROUPING__ID 
FROM tab1 
GROUP BY day
UNION ALL 
SELECT month
,day
,COUNT(DISTINCT values) AS uv
,3 AS GROUPING__ID 
FROM tab1 
GROUP BY month,day

查询结果

month	day	uv	GROUPING__ID
2015-03	NULL	5	1
2015-04	NULL	6	1
NULL	2015-03-10	4	2
NULL	2015-03-12	1	2
NULL	2015-04-12	2	2
NULL	2015-04-13	3	2
NULL	2015-04-15	2	2
NULL	2015-04-16	2	2
2015-03	2015-03-10	4	3
2015-03	2015-03-12	1	3
2015-04	2015-04-12	2	3
2015-04	2015-04-13	3	3
2015-04	2015-04-15	2	3
2015-04	2015-04-16	2	3

3.CUBE 根据GROUP BY的维度的所有组合进行聚合。

SELECT 
	month,
	day,
	COUNT(DISTINCT values) AS uv,
	GROUPING__ID 
FROM tab1
GROUP BY month,day 
WITH CUBE 
ORDER BY GROUPING__ID;

-- 等价于

SELECT NULL
,NULL
,COUNT(DISTINCT values) AS uv
,0 AS GROUPING__ID 
FROM tab1
UNION ALL 
SELECT month
,NULL
,COUNT(DISTINCT values) AS uv
,1 AS GROUPING__ID 
FROM tab1 
GROUP BY month 
UNION ALL 
SELECT NULL
,day
,COUNT(DISTINCT values) AS uv
,2 AS GROUPING__ID 
FROM tab1 
GROUP BY day
UNION ALL 
SELECT month
,day
,COUNT(DISTINCT values) AS uv
,3 AS GROUPING__ID 
FROM tab1 
GROUP BY month,day

查询结果

month	day	uv	GROUPING__ID
NULL	NULL	7	0
2015-03	NULL	5	1
2015-04	NULL	6	1
NULL	2015-04-12	2	2
NULL	2015-04-13	3	2
NULL	2015-04-15	2	2
NULL	2015-04-16	2	2
NULL	2015-03-10	4	2
NULL	2015-03-12	1	2
2015-03	2015-03-10	4	3
2015-03	2015-03-12	1	3
2015-04	2015-04-16	2	3
2015-04	2015-04-12	2	3
2015-04	2015-04-13	3	3
2015-04	2015-04-15	2	3

这里的第一条数据就相当于没有group by任何字段，直接进行了聚合操作的结果。

4.ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合

SELECT 
	month,
	day,
	COUNT(DISTINCT values) AS uv,
	GROUPING__ID  
FROM tab1
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;

可以实现这样的上钻过程：总UV->月的UV->月天的UV

month	day	uv	GROUPING__ID
NULL	NULL	7	0
2015-03	NULL	5	1
2015-04	NULL	6	1
2015-03	2015-03-10	4	3
2015-03	2015-03-12	1	3
2015-04	2015-04-12	2	3
2015-04	2015-04-13	3	3
2015-04	2015-04-15	2	3
2015-04	2015-04-16	2	3

从查询结果我们可以看出，GROUPING__ID为0的就是总UV；GROUPING__ID为1的为月的uv；GROUPING__ID为3的是月+天uv

-- 把month和day调换顺序，则以day维度进行层级聚合

SELECT 
	day,
	month,
	COUNT(DISTINCT values) AS uv,
	GROUPING__ID  
FROM tab1
GROUP BY day,month 
WITH ROLLUP 
ORDER BY GROUPING__ID;

可以实现这样的上钻过程：总UV->天的UV->天月的UV

day	month	uv	GROUPING__ID
NULL	NULL	7	0
2015-04-13	NULL	3	1
2015-03-12	NULL	1	1
2015-04-15	NULL	2	1
2015-03-10	NULL	4	1
2015-04-16	NULL	2	1
2015-04-12	NULL	2	1
2015-04-12	2015-04	2	3
2015-03-10	2015-03	4	3
2015-03-12	2015-03	1	3
2015-04-13	2015-04	3	3
2015-04-15	2015-04	2	3
2015-04-16	2015-04	2	3

从查询结果我们可以看出，GROUPING__ID为0的就是总UV；GROUPING__ID为1的为天的uv；GROUPING__ID为3的是天+月uv