hive--grouping sets用法及grouping_id计算方法
grouping sets 用法
在Hive中，会出现对同一个数据表进行不同粒度的汇总，这时可以有两种方案：
用多个sql，对不同粒度使用不同的 group by 方法。
用1个sql，使用 grouping sets 方法一次性得到所有粒度的汇总。
以测试数据表 tmp_student 为例:
1、生成测试数据
在 hive 环境中创建临时表：
create table tmp.tmp_student
(
name string,
class int,
sex string,
score int
)
row format delimited fields terminated by '|';
1
2
3
4
5
6
7
8
然后加载测试数据：
load data local inpath 'text.txt' into table tmp_student;
1
其中，text.txt 内容如下：
A|3|男|45
B|3|女|55
C|2|女|74
D|3|男|90
E|1|男|80
F|2|女|92
G|1|女|95
H|1|男|95
I|3|女|99
J|3|男|99
1
2
3
4
5
6
7
8
9
10
查看是否加载成功：
hive> select * from tmp_student;
OK
A 3 男 45
B 3 女 55
C 2 女 74
D 3 男 90
E 1 男 80
F 2 女 92
G 1 女 95
H 1 男 95
I 3 女 99
J 3 男 99
Time taken: 0.377 seconds, Fetched: 10 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
2、grouping sets示例
数据表有4个字段：姓名、班级、性别、分数。
如果我想按照以下3个粒度汇总：
1、每个班级的平均分数
2、每个性别的平均分数
3、每个班级下不同性别的平均分数
则使用多个sql分别汇总的方案为：
select class, avg(score) from tmp.tmp_student group by class;
select sex, avg(score) from tmp.tmp_student group by sex;
select class, sex, avg(score) from tmp.tmp_student group by class, sex;
1
2
3
而使用1个sql使用grouping sets的方案为：
select
grouping__id, -- 内置变量，只要使用grouping sets就可以调用
class,
sex,
avg(score) as avg_score
from
tmp.tmp_student
group by
class,
sex
grouping sets(
class,
sex,
(class, sex)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
结果为：
在这里插入图片描述
可以看到，使用 grouping sets 同时汇总出了 3 种不同粒度的平均分数。
注意：
1、grouping sets 只能用于 group by 之后。
2、grouping sets 中可以包含多种粒度，粒度之间用逗号连接。
3、grouping sets 中的所有字段，都必须出现在 group by 中，相当于 group by 后面的字段是最细粒度。
4、如果 select 中的字段，没有包含在某个 grouping set 中，那么这个粒度下的这个字段值为 NULL。
4、不同的粒度，可以使用内置变量 grouping__id 进行区分。
grouping_id计算方法
grouping sets 中的每一种粒度，都对应唯一的 grouping__id 值，其计算公式与 group by 的顺序、当前粒度的字段有关。
具体计算方法如下：
将 group by 的所有字段 倒序 排列。
对于每个字段，如果该字段出现在了当前粒度中，则该字段位置赋值为1，否则为0。
这样就形成了一个二进制数，这个二进制数转为十进制，即为当前粒度对应的 grouping__id。
以上述对 tmp_student 的3种粒度的统计结果为例：
1、group by 的所有字段倒序排列为：sex class
2、对于 3 种 grouping sets，分别对应的二进制数为：
序号 grouping set 给倒序排列的字段（sex class）赋值 对应的十进制（grouping__id 的值）
1 class 01 1
2 sex 10 2
3 class,sex 11 3
