grouping_id&grouping sets

最新推荐文章于 2023-11-08 14:45:40 发布

请输入_HELLO

最新推荐文章于 2023-11-08 14:45:40 发布

阅读量691

点赞数 1

文章标签： hive sql 大数据

本文链接：https://blog.csdn.net/weixin_46680124/article/details/126712929

版权

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, -- 内置变量，只要使用grouping sets就可以调用
    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, -- 内置变量，只要使用grouping sets就可以调用
    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