|
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 '|';
然后加载测试数据：
load data local inpath 'text.txt' into table tmp_student;
其中，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
查看是否加载成功：
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)
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个sql使用grouping sets的方案为：
select
grouping__id,
class,
sex,
avg(score) as avg_score
from
tmp.tmp_student
group by
class,
sex
grouping sets(
class,
sex,
(class, sex)
) order by
grouping__id
此处结果和原文章不同(https://blog.csdn.net/HappyRocking/article/details/106545559)
结果为：
OK
0 3 男 78.0
0 3 女 77.0
0 2 女 83.0
0 1 男 87.5
0 1 女 95.0
1 3 NULL 77.6
1 2 NULL 83.0
1 1 NULL 90.0
2 NULL 男 81.8
2 NULL 女 83.0
可以看到，使用 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 进行区分。
本文重点：
3、grouping_id计算方法
grouping sets 中的每一种粒度，都对应唯一的 grouping__id 值，其计算公式与 group by 的顺序、当前粒度的字段有关。
具体计算方法如下：
① 将 group by 的所有字段 倒序 排列。
② 对于每个字段，如果该字段出现在了当前粒度中，则该字段位置赋值为0，否则为1。
③ 这样就形成了一个二进制数，这个二进制数转为十进制，即为当前粒度对应的 grouping__id。
以上述对 tmp_student 的3种粒度的统计结果为例：
| 序号 | grouping set | 给排列的字段（sex class）为null的赋值 | 对应的十进制（grouping__id 的值） |
|---|
| 1 | class | 01 | 1 | | 2 | sex | 10 | 2 | | 3 | class,sex | 00 | 0 |
4、再验证
新增name 维度
select
grouping__id,
name ,
class,
sex,
avg(score) as avg_score
from
tmp.tmp_student
group by
name ,
sex ,
class
grouping sets(
name ,
class,
sex,
(class, sex),
(name, sex)
) order by
grouping__id
;
结果:
1 E NULL 男 80.0
1 G NULL 女 95.0
1 D NULL 男 90.0
1 H NULL 男 95.0
1 C NULL 女 74.0
1 J NULL 男 99.0
1 B NULL 女 55.0
1 F NULL 女 92.0
1 A NULL 男 45.0
1 I NULL 女 99.0
3 C NULL NULL 74.0
3 J NULL NULL 99.0
3 I NULL NULL 99.0
3 H NULL NULL 95.0
3 G NULL NULL 95.0
3 F NULL NULL 92.0
3 E NULL NULL 80.0
3 D NULL NULL 90.0
3 B NULL NULL 55.0
3 A NULL NULL 45.0
4 NULL 3 男 78.0
4 NULL 1 男 87.5
4 NULL 3 女 77.0
4 NULL 2 女 83.0
4 NULL 1 女 95.0
5 NULL NULL 男 81.8
5 NULL NULL 女 83.0
6 NULL 3 NULL 77.6
6 NULL 2 NULL 83.0
6 NULL 1 NULL 90.0
|