hive group by 的高级用法

group by 作为一种分组查询，在sql中用处十分广泛。

一、GROUPING SETS

grouping sets相当于多个group by分组统计后再union的逻辑
例子：

select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, dishi)
-- cluster by sum_xinzeng 
distribute by sum_xinzeng sort by sum_xinzeng desc;

等价

select
shengfen,
null,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen
union all
select
null,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by dishi
distribute by sum_xinzeng sort by sum_xinzeng desc;

二、Grouping__ID（两个下划线_）

这个函数返回一个位向量，该位向量对应于每一列是否存在。对于每一列，如果结果集中的某一行已经聚合了该列，则结果集中的某一行的值为“1”，否则该值为“0”。这可以用于在数据中有空值时进行区分。注意：hive 不同版本Grouping__ID的生成规则不同 hive 2.3前后 注意
例子
select

shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, (shengfen, dishi))
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;

等价于

select
shengfen,
null,
sum(xinzeng) as sum_xinzeng,
1 as grouping__id
from xinzeng_tb
group by shengfen
union all
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
0 as grouping__id
from xinzeng_tb
group by shengfen, dishi
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;

三、CUBE

cube 必须和Group By 一起使用，（使用cube 可以得到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), (a), (b), （c）, ( )）

四、rollup

RoLL UP 子句用于与 GROUP BY 一起计算维度层次结构级别上的聚合。使用ROLL UP将 a，b，c 分组，假设层次结构是 a 向下钻取到 b ,然后b 向下 钻取到c 。
Group By a,b，c with RollUP 等价于 Group by a，b ,c GroupIng Sets ((),(a),(a,b),(a,b,c))

具体例子为：

select
shengfen,
dishi,
riqi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi, riqi
--grouping sets (shengfen, dishi, riqi)
with rollup
OK
shengfen	dishi	riqi	sum_xinzeng	grouping__id
NULL	NULL	NULL	8437	7
广东省	NULL	NULL	4345	3
上海市	NULL	NULL	4092	3
广东省	深圳市	NULL	1518	1
广东省	广州市	NULL	1438	1
上海市	嘉定区	NULL	1434	1
广东省	珠海市	NULL	1389	1
上海市	普陀区	NULL	1369	1
上海市	浦东新区	NULL	1289	1
广东省	深圳市	2019-01-02	770	0
广东省	深圳市	2019-01-03	748	0
广东省	广州市	2019-01-02	726	0
上海市	嘉定区	2019-01-03	721	0
上海市	嘉定区	2019-01-02	713	0
广东省	广州市	2019-01-03	712	0
广东省	珠海市	2019-01-02	708	0
上海市	普陀区	2019-01-03	691	0
广东省	珠海市	2019-01-03	681	0
上海市	普陀区	2019-01-02	678	0
上海市	浦东新区	2019-01-03	664	0
上海市	浦东新区	2019-01-02	625	0

五、Grouping

分组函数指示GROUP BY子句中的表达式是否对给定行进行聚合。值0表示属于分组集的列，而值1表示不属于分组集的列。自己应用较少。 tado 理解