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

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

目录

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-对照组:

相关文章:

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

目录 1、什么是增强聚合和多维分析函数? 2、grouping sets - 指定维度组合 3、with rollup - 上卷维度组合 4、with cube - 全维度组合 5、Grouping__ID、grouping() 的使用场景 6、使用 增强聚合 会不会对查询性能有提升呢? 7、对grouping sets、…...

elasticsearch bulk 批量操作

1:bulk 是 elasticsearch 提供的一种批量增删改的操作API bulk 对 JSON串 有着严格的要求。每个JSON串 不能换行 ,只能放在同一行,同时, 相邻的JSON串之间必须要有换行 (Linux下是\n;Window下是\r\n&#…...

力扣11、 盛最多水的容器

方法一:双指针 考察: 贪心、数组、双指针 说明 本题是一道经典的面试题,最优的做法是使用「双指针」。如果读者第一次看到这题,不一定能想出双指针的做法。 复杂度分析 时间复杂度:O(N),双指针总计最多…...

IIC协议详解

目录 1.IIC协议概述 2.IIC总线传输 3.IIC-51单片机应用 1.起始信号 2.终止信号 3.应答信号 4.数据发送 4.IIC-32单片机应用 用到的库函数: 1.IIC协议概述 IIC全称Inter-Integrated Circuit (集成电路总线)是由PHILIPS公司在80年代开发的两线式串行总线&…...

element ui-表头自定义提示框

版本 “element-ui”: “^2.15.5”,需求&#xff1a;鼠标悬浮到该列表头&#xff0c;显示提示框代码 <el-table:data"xxxx"><el-table-column label"序号" width"40" type"index" /><el-table-columnv-for"(ite…...

Python 图形化界面基础篇:创建顶部菜单

Python 图形化界面基础篇&#xff1a;创建顶部菜单 引言 Tkinter 库简介步骤1&#xff1a;导入 Tkinter 模块步骤2&#xff1a;创建 Tkinter 窗口步骤3&#xff1a;创建顶部菜单栏步骤4&#xff1a;处理菜单项的点击事件步骤5&#xff1a;启动 Tkinter 主事件循环 完整示例代码…...

java实现十大排序算法

文章目录 冒泡排序选择排序插入排序希尔排序归并排序快速排序堆排序桶排序基数排序计数排序验证各个排序的时间复杂度和空间复杂度 冒泡排序 冒泡排序&#xff08;Bubble Sort&#xff09;是一种简单的比较排序算法&#xff0c;它的基本思想是重复地交换相邻的两个元素&#x…...

Linux日志管理-logrotate(crontab定时任务、Ceph日志转储)

文章目录 一、logrotate概述二、logrotate基本用法三、logrotate运行机制logrotate参数 四、logrotate是怎么做到滚动日志时不影响程序正常的日志输出呢&#xff1f;Linux文件操作机制方案一方案二 五、logrotate实战--Ceph日志转储参考 一、logrotate概述 logrotate是一个用于…...

用PHP异步协程控制python爬虫脚本,实现多协程分布式爬取

背景 公司需要爬取指定网站的产品数据。但是个人对python的多进程和协程不是特别熟悉。所以&#xff0c;想通过php异步协程&#xff0c;发起爬取url请求控制python爬虫脚本&#xff0c;达到分布式爬取的效果。 准备 1.准备一个mongodb数据库用于存放爬取数据2.引入flask包&a…...

VUE3写后台管理(3)

VUE3写后台管理&#xff08;3&#xff09; 1.环境1.node2.vite3.Element-plus4.vue-router5.element icon6.less7.vuex8.vue-demi9.mockjs10.axios11.echarts 2.首页1.布局Main2.头部导航栏CommonHeader3.左侧菜单栏CommonLeft4.首页Home1.从后端获取数据显示到前端table的三种…...

机器学习笔记之最优化理论与算法(十二)无约束优化问题——共轭梯度法

机器学习笔记之最优化理论与方法——共轭梯度法 引言回顾&#xff1a;共轭方向法的重要特征线性共轭梯度法共轭方向公式的证明过程 关于线搜索公式中参数的化简关于线搜索公式中步长部分的化简关于线搜索公式中共轭方向系数的化简参数化简的目的 非线性共轭梯度法(FR,PRP方法)关…...

JVM中的java同步互斥工具应用演示及设计分析

1.火车站售票系统仿真 某火车站目前正在出售火车票&#xff0c;共有50张票&#xff0c;而它有3个售票窗口同时售票&#xff0c;下面设计了一个程序模拟该火车站售票&#xff0c;通过实现Runnable接口实现&#xff08;模拟网络延迟&#xff09;。 伪代码&#xff1a; Ticket类…...

数据治理-数据质量

实现数据质量的前提就是数据本身是可靠和可信的。 导致数据质量低下的因素 组织缺乏对低质量数据影响的理解&#xff0c;缺乏规划、孤岛式系统设计、不一致的开发过程、不完整的文档、缺乏标准或缺乏治理等。 所有组织都会遇到与数据质量有关的问题。数据质量需要跨职能的承诺…...

[sqoop]hive3.1.2 hadoop3.1.1安装sqoop1.4.7

参考: Hadoop3.2.4Hive3.1.2sqoop1.4.7安装部署_hadoop sqoop安装_alicely07的博客-CSDN博客 一、安装 1、解压 tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /home/data_warehouse/module mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.72、配置文件 sqoop-env.s…...

js事件的详细介绍

11.事件 1.什么是事件 js属于事件驱动编程,把驱动,执行,调用通过一些交互,触发一些函数事件:发起-->执行绑定事件-->触发事件on 绑定 emit触发 off解绑2.事件分类 鼠标事件 点击事件 onclick 双击事件 ondblclick 按下事件 onmousedown 抬起事件 onmouseup 鼠标进…...

虚幻4学习笔记(12)操控导入的角色、动画蓝图、播放蒙太奇和打包、角色重定向

虚幻4学习笔记 操控导入的角色设置鼠标旋转关掉动态模糊 动画蓝图、播放蒙太奇和打包角色走路奔跑动画shift 奔跑F 跳舞移动打断 跳舞 打包角色重定向姿势调整解决跑步 腿分太开隐藏剑 B站UP谌嘉诚课程&#xff1a;https://www.bilibili.com/video/BV164411Y732 操控导入的角色…...

hive with tez:无法从链中的任何提供者加载aws凭据

环境信息 hadoop 3.1.0 hive-3.1.3 tez 0.9.1 问题描述 可以从hadoop命令行正确地访问s3a uri。我可以创建外部表和如下命令&#xff1a; create external table mytable(a string, b string) location s3a://mybucket/myfolder/; select * from mytable limit 20; 执行正…...

Ubuntu修改静态IP、网关和DNS的方法总结

Ubuntu修改静态IP、网关和DNS的方法总结 ubuntu系统&#xff08;其他debian的衍生版本好像也可以&#xff09;修改静态IP有以下几种方法。&#xff08;搜索总结&#xff0c;可能也不太对&#xff09; /etc/netplan (use) Ubuntu 18.04开始可以使用netplan配置网络&#xff0…...

Eureka服务器注册

一。Eureka服务器注册 1.pom.xml <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0"xmlns:xsi"http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation"http://mav…...

Windows安装GPU版本的pytorch详细教程

文章目录 chatGLM2-6B安装教程正式安装 chatGLM2-6B ChatGLM2-6B版本要装pytorch2.0&#xff0c;而且要2.0.1 &#xff0c;因此CUDA不能用12.0 &#xff0c;也不能用10.0&#xff0c;只能用11.x 版本。 安装教程 pip install直接下载安装 官网&#xff1a; https://pytorch.…...

[2025CVPR]DeepVideo-R1:基于难度感知回归GRPO的视频强化微调框架详解

突破视频大语言模型推理瓶颈,在多个视频基准上实现SOTA性能 一、核心问题与创新亮点 1.1 GRPO在视频任务中的两大挑战 ​安全措施依赖问题​ GRPO使用min和clip函数限制策略更新幅度,导致: 梯度抑制:当新旧策略差异过大时梯度消失收敛困难:策略无法充分优化# 传统GRPO的梯…...

springboot 百货中心供应链管理系统小程序

一、前言 随着我国经济迅速发展&#xff0c;人们对手机的需求越来越大&#xff0c;各种手机软件也都在被广泛应用&#xff0c;但是对于手机进行数据信息管理&#xff0c;对于手机的各种软件也是备受用户的喜爱&#xff0c;百货中心供应链管理系统被用户普遍使用&#xff0c;为方…...

Java如何权衡是使用无序的数组还是有序的数组

在 Java 中,选择有序数组还是无序数组取决于具体场景的性能需求与操作特点。以下是关键权衡因素及决策指南: ⚖️ 核心权衡维度 维度有序数组无序数组查询性能二分查找 O(log n) ✅线性扫描 O(n) ❌插入/删除需移位维护顺序 O(n) ❌直接操作尾部 O(1) ✅内存开销与无序数组相…...

Rust 异步编程

Rust 异步编程 引言 Rust 是一种系统编程语言,以其高性能、安全性以及零成本抽象而著称。在多核处理器成为主流的今天,异步编程成为了一种提高应用性能、优化资源利用的有效手段。本文将深入探讨 Rust 异步编程的核心概念、常用库以及最佳实践。 异步编程基础 什么是异步…...

【python异步多线程】异步多线程爬虫代码示例

claude生成的python多线程、异步代码示例&#xff0c;模拟20个网页的爬取&#xff0c;每个网页假设要0.5-2秒完成。 代码 Python多线程爬虫教程 核心概念 多线程&#xff1a;允许程序同时执行多个任务&#xff0c;提高IO密集型任务&#xff08;如网络请求&#xff09;的效率…...

css3笔记 (1) 自用

outline: none 用于移除元素获得焦点时默认的轮廓线 broder:0 用于移除边框 font-size&#xff1a;0 用于设置字体不显示 list-style: none 消除<li> 标签默认样式 margin: xx auto 版心居中 width:100% 通栏 vertical-align 作用于行内元素 / 表格单元格&#xff…...

.Net Framework 4/C# 关键字(非常用,持续更新...)

一、is 关键字 is 关键字用于检查对象是否于给定类型兼容,如果兼容将返回 true,如果不兼容则返回 false,在进行类型转换前,可以先使用 is 关键字判断对象是否与指定类型兼容,如果兼容才进行转换,这样的转换是安全的。 例如有:首先创建一个字符串对象,然后将字符串对象隐…...

C# 求圆面积的程序(Program to find area of a circle)

给定半径r&#xff0c;求圆的面积。圆的面积应精确到小数点后5位。 例子&#xff1a; 输入&#xff1a;r 5 输出&#xff1a;78.53982 解释&#xff1a;由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982&#xff0c;因为我们只保留小数点后 5 位数字。 输…...

DeepSeek源码深度解析 × 华为仓颉语言编程精粹——从MoE架构到全场景开发生态

前言 在人工智能技术飞速发展的今天&#xff0c;深度学习与大模型技术已成为推动行业变革的核心驱动力&#xff0c;而高效、灵活的开发工具与编程语言则为技术创新提供了重要支撑。本书以两大前沿技术领域为核心&#xff0c;系统性地呈现了两部深度技术著作的精华&#xff1a;…...

Python实现简单音频数据压缩与解压算法

Python实现简单音频数据压缩与解压算法 引言 在音频数据处理中&#xff0c;压缩算法是降低存储成本和传输效率的关键技术。Python作为一门灵活且功能强大的编程语言&#xff0c;提供了丰富的库和工具来实现音频数据的压缩与解压。本文将通过一个简单的音频数据压缩与解压算法…...