请选择 进入手机版 | 继续访问电脑版

数仓工具—Hive语法之grouping sets(5)

[复制链接]
黎平 发表于 2021-1-2 17:37:22 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
GROUPING SETS

先说一下配景,我么知道GROUP BY 可以举行数据分组统计,我们将分组称之为统计的维度,比方 GROUP BY school 我们认为维度是学校,GROUP BY 也支持多个字段举行分组统计,比方GROUP BY school,grade 我们的维度就是 学校+年级 的组合,虽然是组合照旧单维度的,组合在一起就一个依然是单个维度,因为统计出来的数据你只能得到每个学校的每个年级的信息,你得不到学校的单独统计信息也得不到年级的单独统计信息
  1. 学校名称        年级        学生数清华小学  6                        1000北巨细学  6                        1300北巨细学  5                        1600南京小学  5     500        ............
复制代码
就像上面的统计信息,如果你不做二次统计的话,你不能得到整个清华小学有多少人,你也得不到整个6年级有多少人
这就是我们说的维度,因为GROUP BY的维度是单一的,就是它只能盘算某个维度的信息,而不能同时盘算多个维度,在一个GROUPING SETS 查询中,根据差别的单维度组合举行聚合,等价于将差别维度的GROUP BY效果举行UNION ALL操纵。
GROUPING SETS就是一种将多个GROUP BY逻辑UNION写在一个HIVE SQL语句中的便利写法。
GROUPING SETS会把在单个GROUP BY逻辑中没有到场GROUP BY的那一列置为NULL值,这样聚合出来的效果,未被GROUP BY的列将显示为NULL。
演示

我们先准备这样一份数据
  1. school        grade        user第一中学        一年级        张三1第一中学        二年级        张三2第一中学        三年级        张三3第一中学        四年级        张三4第一中学        五年级        张三5第一中学        六年级        张三6第二中学        一年级        李四1第二中学        二年级        李四2第二中学        三年级        李四3第二中学        四年级        李四4第二中学        五年级        李四5第二中学        六年级        李四6第三中学        一年级        王二1第三中学        二年级        王二2第三中学        三年级        王二3第三中学        四年级        王二4第三中学        五年级        王二5第三中学        六年级        王二6
复制代码
  1. CREATE TABLE ods.ods_student (  school string,  grade string,  `user` string)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'STORED AS TEXTFILE;load data local inpath '/Users/liuwenqiang/workspace/hive/students.data' overwrite into table ods.ods_student;
复制代码
首先我们查询一下每个学校每个年级有多少人,返回效果的格式是 学校,年级,人数 这样的格式
  1. select school,grade,count(1) as userCnt from ods.ods_student group by school,grade;
复制代码
接下来我们希望同时查询出每个学校有多少人,然后和上面的查询效果一起返回,年级的位置是null
  1. select school,grade,count(1) as userCnt from ods.ods_student group by school,gradeunion allselect school,null,count(1) as userCnt from ods.ods_student group by school;
复制代码

在上面的底子上,我们希望同时查询出每个年级有多少人,学校的位置设置为null
  1. select school,grade,count(1) as userCnt from ods.ods_student group by school,gradeunion allselect school,null,count(1) as userCnt from ods.ods_student group by schoolunion allselect null,grade,count(1) as userCnt from ods.ods_student group by grade;
复制代码
实在看到这里我们也大概明白了,只要我们添加维度我们就在原来SQL底子上添加新的统计SQL,然后不到场盘算的维度设置为NULL 就可以了

针对上面这种情况,Hive 为我们提供了 GROUPING SETS 函数,sets 你可以认为是group by 维度的聚集,然后按照聚集里的维度将每个维度的 group by 的效果归并起来
  1. select school,grade,count(1) as userCnt from ods.ods_student group by school,grade grouping sets((school,grade),school,grade);
复制代码

除了返回的顺序和上面实现的返回顺序不一样,效果值都是一样的,比方第一行的寄义就是全部一年级的学生数量(所以学校的),实在这我我们可以对NULL 值举行处置处罚一下
  1. select nvl(school,'全年级'),nvl(grade,'全学校'),count(1) as userCnt from ods.ods_student group by school,grade grouping sets((school,grade),school,grade);
复制代码

这下输出就悦目多了,也方便明白
语法规则

实在前面我们说过了,grouping sets 实在就是就将多个group by 的分组效果举行归并,虽然这个意义很显着个了,但是我们这里需要注意一些地方

  • grouping sets(dim1,dim2,dim3 …) 每个维度都要么是在group by 中出现,要么是group by中的字端组合,比方 group by school,grade 然后grouping sets 总出现了(school,grade) 这样的组合
  • grouping sets(dim1,dim2,dim3 …()) 中可以出现一个空的分组组合,这个的寄义是全部的汇总,即不按照任何分组字段,也就是所有的分组字段的位置都是NULL
  1. select nvl(school,'全年级'),nvl(grade,'全学校'),count(1) as userCnt from ods.ods_student group by school,grade grouping sets((school,grade),school,grade,());
复制代码

在这里就是全年级全学校
实现原理

看了hive 中 grouping sets 实现(从源码及执行操持都可以看出与kylin实现不一样),并没有像kylin一样先按照group by 全字段聚合再上卷。
hive实现就是无脑复制,可以明白成是 group by grouping sets 所有组合 然后 在union 起来
grouping__id 字段

在grouping sets做维度上卷的时候,实在就是有小维度到大维度的时候,每个维度都会分配一个ID,需要强调的时候每个维度都会分配一个ID,而不是每条纪录,维度ID 从0 开始
  1. select grouping__id, nvl(school,'全年级'),nvl(grade,'全学校'),count(1) as userCnt from ods.ods_student group by school,grade grouping sets((school,grade),school,grade,());
复制代码
比方上面grouping sets((school,grade),school,grade,());总共有四个维度,分别是(school,grade)、,school、grade和(),所以这个四个维度的ID 依次是0 1 2 3

我们看到同一个维度的纪录并没有全部放在一下,这个时候我们就可以使用这个字段举行排序
  1. select grouping__id, nvl(school,'全年级'),nvl(grade,'全学校'),count(1) as userCm ods.ods_student group by school,grade grouping sets((school,grade),school,grade,()) order by grouping__id ;
复制代码

总结


  • grouping sets 可以简化我们SQL 的写法,但是现在的实现并不能提高SQL 的查询性能,大概以后新版本的实现会优化这一点
  • grouping sets 在一个group by 的聚合查询中,根据差别的维度举行组合,我们可以选择性的去对维度聚合,也可以聚合所有维度,当维度上卷聚适时,维度值为 null,可用nvl函数去赋值,得到一个有意义的维度名称
  • grouping__id 是一个维度ID 字段,有时候我们可以使用其举行过滤和排序,这个字段的取值个grouping sets() 函数中参数的顺序保持一致

来源:https://blog.csdn.net/king14bhhb/article/details/112063657
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )