Hive cube / rollup / grouping sets/GROUPING

• GROUPING SETS
• GROUPING__ID（注意这里是两个下划线）
• CUBE
• ROLLUP

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;
  

结果：

使用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;

输出结果都是一样的

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;

查询结果：

也可以这样查询

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

查询结果

根据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

查询结果

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

是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

从查询结果我们可以看出，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

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