当前位置： 首页 > news >正文

hive、spark、presto 中的增强聚合-grouping sets、rollup、cube

news 2025/7/19 1:08:14

目录

1、什么是增强聚合和多维分析函数？

2、grouping sets - 指定维度组合

3、with rollup - 上卷维度组合

4、with cube - 全维度组合

5、Grouping__ID、grouping() 的使用场景

6、使用 增强聚合 会不会对查询性能有提升呢？

7、对grouping sets、with cube、with rollup 的优化

1、什么是增强聚合和多维分析函数？

        增强聚合指的是：

                在SQL中使用分组聚合查询时，使用 grouping sets、rollup、cube 语句进行操作

                在常见的数据引擎中都支持这种语法，比如hive、spark、presto、ck、flinkSQL

                使用增强聚合不仅可以简化SQL代码，而且还能对SQL语句的性能有所提升

        多维分析指的是：

                SQL语法中的多维分析指的是 多种维度组合的分析，而不是多种维度的分析

         hive官网链接：hive官网

                

2、grouping sets - 指定维度组合

功能说明：

        对指定的分组字段进行多种维度组合的聚合计算

hive-语法：

-- TODO 必须开区map端合并
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C 
grouping sets( (A),(A,B),(A,B,C),..维度组合 )

presto、FlinkSQL、SparkSQL-语法：
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
grouping sets( (A),(A,B),(A,B,C),..维度组合 )

语法区别：

        1、hiveSQL中 group by 后面必须添加分组的字段

             presto、flinksql、sparksql group by 后面不需要指定分组字段

        2、hiveSQL中 可以使用 grouping__id字段

              presto、flinksql、sparksql 中并没有提供 grouping__id字段，需要使用grouping(a,b,c) 函数来计算

代码示例(HiveSQL):

-- TODO 必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','郑州市','高开区','张3' union all select '河南省','郑州市','高开区','张4' union all select '河南省','郑州市','高开区','张5' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area grouping sets ((prov,city,area),(prov)
)
;

代码示例(presto、flinkSQL、sparkSQL):

SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES  ('河北省','石家庄市','新华区','张1'),('河北省','石家庄市','新华区','张2'),('河南省','郑州市','高开区','张3'),('河南省','郑州市','高开区','张4'),('河南省','郑州市','高开区','张5'),('河南省','新乡市','中华区','张6')) AS person_info_df (prov,city,area,name)
group by grouping sets ((prov,city,area),(prov)
)
;

3、with rollup - 上卷维度组合

功能说明：

        上卷维度组合，较grouping sets相比，不需要指定维度组合

         GROUP BY a, b, c, WITH ROLLUP  等价于  

         GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))

hive-语法：

-- TODO 必须开区map端合并
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C 
with rollup

presto、FlinkSQL、SparkSQL-语法：

select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
rollup(A,B,C)

代码示例(HiveSQL):

-- 1.必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','郑州市','高开区','张3' union all select '河南省','郑州市','高开区','张4' union all select '河南省','郑州市','高开区','张5' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area with rollup
;

代码示例(presto、flinkSQL、sparkSQL):

SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES  ('河北省','石家庄市','新华区','张1'),('河北省','石家庄市','新华区','张2'),('河南省','郑州市','高开区','张3'),('河南省','郑州市','高开区','张4'),('河南省','郑州市','高开区','张5'),('河南省','新乡市','中华区','张6')) AS person_info_df (prov,city,area,name)
group by rollup(prov,city,area) 
;

4、with cube - 全维度组合

功能说明：

        多维度组合，会计算所有分组字段的维度组合，较grouping sets相比，不需要指定维度组合

         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), ( ))

cube(key1,key2...) 维度组合数：

hive-语法：

-- TODO 必须开区map端合并
select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping__id
from 表名 [where ]
group by A,B,C 
with cube

presto、FlinkSQL、SparkSQL-语法：

select 维度A,维度B,维度C,聚合函数(度量字段) ,grouping(A,B,C) as grouping_id
from 表名 [where ]
group by
cube(A,B,C)

代码示例(HiveSQL):

-- 1.必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','郑州市','高开区','张3' union all select '河南省','郑州市','高开区','张4' union all select '河南省','郑州市','高开区','张5' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area with cube
;

代码示例(presto、flinkSQL、sparkSQL):

SELECT prov,city,area,count(1),grouping(prov,city,area) as grouping_id
FROM (VALUES  ('河北省','石家庄市','新华区','张1'),('河北省','石家庄市','新华区','张2'),('河南省','郑州市','高开区','张3'),('河南省','郑州市','高开区','张4'),('河南省','郑州市','高开区','张5'),('河南省','新乡市','中华区','张6')) AS person_info_df (prov,city,area,name)
group by cube(prov,city,area) 
;

5、Grouping__ID、grouping() 的使用场景

功能说明：

        可以用来判断分组字段是否参与聚合，下面为 Grouping__ID 、grouping() 计算逻辑

使用场景：

        当使用 grouping sets、with rollup、with cube进行聚合时，对不参与聚合的字段会使用null进行填充，这就导致查询结果中分组字段为null时，无法区分是填充的null还是分组字段本身的null

        遇到上述情况，可以使用下面两种解决方式

                1、将分组字段中的null进行替换处理，比如9999、other、其他

                2、使用 Grouping__ID 或者 grouping() 进行区分

6、使用 增强聚合 会不会对查询性能有提升呢？

测试用例-grouping sets：

-- TODO 必须开区map端合并
set hive.map.aggr=true;
SELECT prov,city,area,count(1),grouping__id
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,city,area grouping sets ((prov,city,area),(prov,city),(prov)
)
;

测试用例-group by + union all：

set hive.map.aggr=true;
SELECT prov,city,area,count(1)
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6'
) AS person_info_df
group by prov,city,areaunion all SELECT prov,city,null as area,count(1)
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov,cityunion all SELECT prov,null as city,null as area,count(1)
FROM (select '河北省' as prov,'石家庄市' as city,'新华区' as area,'张1' as name union all select '河北省','石家庄市','新华区','张2' union all select '河南省','新乡市','中华区','张6') AS person_info_df
group by prov

对比执行计划：

对比运行时长：

结论：

通过上面执行计划和运行时长的对比，使用 grouping sets、with cube、with rollup 确实比

group by + union all 方式的性能要好，因为 增强group by避免了多次读取底表，降低生成

job的个数，从而减轻了磁盘和网络I/O时的压力。

7、对grouping sets、with cube、with rollup 的优化

由于在使用增强group by时，会在同一个job中完成多种维度组合的聚合(2的N次方)，当底表数据量太大 或 维度过多时，可能造成计算资源不够而导致任务失败。

在 Hive中可以使用 set hive.new.job.grouping.set.cardinality=30 来对job进行拆分。

参数说明：

验证SQL-实验组：

验证SQL-对照组：

http://www.lryc.cn/news/170577.html

相关文章：

  • elasticsearch bulk 批量操作
  • 力扣11、 盛最多水的容器
  • IIC协议详解
  • element ui-表头自定义提示框
  • Python 图形化界面基础篇：创建顶部菜单
  • java实现十大排序算法
  • Linux日志管理-logrotate（crontab定时任务、Ceph日志转储）
  • 用PHP异步协程控制python爬虫脚本，实现多协程分布式爬取
  • VUE3写后台管理(3)
  • 机器学习笔记之最优化理论与算法(十二)无约束优化问题——共轭梯度法
  • JVM中的java同步互斥工具应用演示及设计分析
  • 数据治理-数据质量
  • [sqoop]hive3.1.2 hadoop3.1.1安装sqoop1.4.7
  • js事件的详细介绍
  • 虚幻4学习笔记（12）操控导入的角色、动画蓝图、播放蒙太奇和打包、角色重定向
  • hive with tez:无法从链中的任何提供者加载aws凭据
  • Ubuntu修改静态IP、网关和DNS的方法总结
  • Eureka服务器注册
  • Windows安装GPU版本的pytorch详细教程
  • 理解Kruskal算法的前提----深入理解并查集【超简单~】
  • Jenkins+Gitee+Docker+Ruoyi项目前后端分离部署
  • 笙默考试管理系统-MyExamTest----codemirror（23）
  • 重学Java (一) 泛型
  • Docker 部署 Redis 服务
  • 阿里云产品试用系列-负载均衡 SLB
  • drf 对象级权限
  • 八大排序（二）--------冒泡排序
  • SmartSQL 一款开源的数据库文档管理工具
  • 代码随想录算法训练营第56天 | ● 583. 两个字符串的删除操作 ● 72. 编辑距离 ● 动态规划之编辑距离总结篇
  • 矩阵 m * M = c