Hive SQL 窗口函数 `ROW_NUMBER() ` 案例分析
一文彻底搞懂 ROW_NUMBER() 和 PARTITION BY
1. 引言
在处理大规模数据集时,Hive SQL 提供了强大的窗口函数(Window Function),如 ROW_NUMBER(),用于为结果集中的每一行分配唯一的行号。当与 PARTITION BY 和 ORDER BY 结合使用时,ROW_NUMBER() 可以帮助解决许多复杂的分析任务,例如去重、排名和分页查询等。本文将详细介绍如何结合这三个元素来实现高效的SQL查询。
2. 语法结构
2.1 ROW_NUMBER()
- 定义:为分区内的每一行分配一个唯一的行号。
- 用法:
ROW_NUMBER() OVER ([PARTITION BY <expr_list>] ORDER BY <expr_list>)
2.2 PARTITION BY
- 作用:定义了窗口函数应用于哪些逻辑分组或分区。
- 字段意义:指定用来分组的列,所有具有相同值的行会被归入同一组。
- 示例:
PARTITION BY department_id表示按部门ID分组。
2.3 ORDER BY
- 作用:确定行号分配的顺序。
- 字段意义:定义排序规则,可以是一个或多个字段,并可指定升序 (
ASC) 或降序 (DESC)。 - 示例:
ORDER BY salary DESC按薪资从高到低排序。
3. 使用场景与实际案例
为了更好地展示 ROW_NUMBER() 结合 PARTITION BY 和 ORDER BY 的使用方法,还是要 show case 的,下面通过具体 🌰 来解释如何在不同的业务需求下应用这些功能。
3.1 数据去重
在某些情况下,数据集中可能存在重复记录,而我们只希望保留特定条件下的一条记录(如最新的记录)。这时可以使用 ROW_NUMBER() 来为每组记录分配行号,并选择行号为1的记录以达到去重的目的。
案例1:最新交易记录(按客户)
考虑一张名为
transactions的表,包含以下字段:
trans_id: 交易IDcustomer_id: 客户IDamount: 交易金额transaction_date: 交易日期
需求:找出每位客户的最近一次交易记录。
具体操作如下:
WITH LatestTransactions AS (SELECT trans_id,customer_id,amount,transaction_date,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date DESC) as rnFROM transactions
)
SELECT trans_id,customer_id,amount,transaction_date
FROM LatestTransactions
WHERE rn = 1;
- 解释:
PARTITION BY customer_id把所有交易按照客户分组;ORDER BY transaction_date DESC确保每组内的交易按时间降序排列,因此最新的交易会获得行号1。
3.2 获取排名
当需要根据某个标准对数据进行排序并计算相对排名时,比如找出每个月销售额最高的前N名销售员或每个部门内薪资最高的员工,可以利用 ROW_NUMBER() 函数结合 PARTITION BY 和 ORDER BY 来实现。
案例2:员工薪资排名(按部门)
假设有一个名为
employees的表,包含以下字段:
emp_id: 员工IDname: 员工姓名department_id: 部门IDsalary: 薪资
需求:为每个部门的员工按照薪资从高到低排序,并给出他们的排名。
具体操作如下:
WITH EmployeeRank AS (SELECT emp_id,name,department_id,salary,ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rankFROM employees
)
SELECT emp_id,name,department_id,salary,rank
FROM EmployeeRank;
- 解释:
PARTITION BY department_id将数据分为不同部门的组;ORDER BY salary DESC在每个部门内根据薪资从高到低排序。
案例3:月度销售冠军(按产品类别)
假设有如下表格
sales,包含以下字段:
sale_id: 销售记录IDproduct_category: 产品类别salesperson: 销售人员名字sales_amount: 销售金额month: 月份
需求:计算每个月每个产品类别的销售冠军。
具体操作如下:
WITH MonthlySalesLeaders AS (SELECT product_category,salesperson,month,sales_amount,ROW_NUMBER() OVER (PARTITION BY product_category, month ORDER BY sales_amount DESC) as rankFROM sales
)
SELECT product_category,salesperson,month,sales_amount
FROM MonthlySalesLeaders
WHERE rank = 1;
- 解释:
PARTITION BY product_category, month创建了基于产品类别和月份的分区;ORDER BY sales_amount DESC确保了每个分区内销售额最高的销售人员会被排在最前面。
3.3 分页查询
当处理大量数据时,可能需要分批次地展示结果集。例如,在网页上显示搜索结果时,通常每次只加载一部分数据。此时,可以通过 ROW_NUMBER() 来实现分页效果。
案例4:获取第101到200条记录
- 假设你有一个大表
large_table,并且想要获取该表中第101到200条记录(假设表中有一列id可以用来排序)。
具体操作如下:
WITH PaginatedData AS (SELECT *,ROW_NUMBER() OVER (ORDER BY id) as row_numFROM large_table
)
SELECT *
FROM PaginatedData
WHERE row_num BETWEEN 101 AND 200;
- 解释:这里使用
ROW_NUMBER()为每一行分配一个唯一的行号,并通过WHERE子句筛选出所需的分页范围。
通过上述场景和对应的案例,可以看到 ROW_NUMBER() 结合 PARTITION BY 和 ORDER BY 是多么强大且灵活。它不仅能够解决常见的数据分析问题,还能提高查询效率,使得复杂的数据处理变得更加直观和简便。
相关文章:
Hive SQL 窗口函数 `ROW_NUMBER() ` 案例分析
一文彻底搞懂 ROW_NUMBER() 和 PARTITION BY 1. 引言 在处理大规模数据集时,Hive SQL 提供了强大的窗口函数(Window Function),如 ROW_NUMBER(),用于为结果集中的每一行分配唯一的行号。当与 PARTITION BY 和 ORDER …...
前端mock数据 —— 使用Apifox mock页面所需数据
前端mock数据 —— 使用Apifox 一、使用教程二、本地请求Apifox所mock的接口 一、使用教程 在首页进行新建项目: 新建项目名称: 新建接口: 创建json: 请求方法: GET。URL: api/basis。响应类型…...
车载U盘制作教程:轻松享受个性化音乐
车载U盘播放音乐相较于蓝牙播放具有一些明显的优势,这些优势主要体现在音质、稳定性、音乐管理以及兼容性等方面。以下是车载U盘播放音乐的一些优势: 音质更佳:车载U盘播放音乐时,音乐文件是直接被解码并播放的,这意味…...
springboot 3 websocket react 系统提示,选手实时数据更新监控
构建一个基于 Spring Boot 3 和 WebSocket 的实时数据监控系统,并在前端使用 React,可以实现选手实时数据的更新和展示功能。以下是该系统的核心设计和实现思路: 1. 系统架构 后端 (Spring Boot 3): 提供 WebSocket 服务端,处理…...
现代图形API综合比较:Vulkan DirectX Metal WebGPU
Vulkan、DirectX、Metal 和 WebGPU 等低级图形 API 正在融合为类似于当前 GPU 构建方式的模型。 图形处理单元 (GPU) 是异步计算单元,可以处理大量数据,例如复杂的网格几何形状、图像纹理、输出帧缓冲区、变换矩阵或你想要计算的任何数据。 NSDT工具推荐…...
【Hot100刷题计划】Day04 栈专题 1~3天回顾(持续更新)
LeetCode Hot 100 是最常被考察的题目集合,涵盖了面试中常见的算法和数据结构问题。刷 Hot100可以让你在有限的时间内集中精力解决最常考的问题。鼓励大家不仅要写出代码,最好理解问题的本质、优化解法和复杂度分析。遇到问题要多交流多求问多分享&#…...
用VBA将word文档处理成支持弹出式注释的epub文档可用的html内容
有一种epub文件,其中的注释以弹窗形式显示,如下图: 点击注释引用后,对应的注释内容会弹出在页面中显示,再次点击弹窗外的任意位置该弹窗即关闭,关闭后点击任意注释引用,对应的注释内容会弹窗显示…...
舵机原理介绍 简洁讲解面向实战 非阻塞式驱动代码, arduino
目录 1.舵机简介 2.舵机转动角度的PWM条件(以180度的SG90舵机为例) 2.1 控制关系 2.2arduino产生PWM 3.0 附代码 循环0度到180度开关舵机(非阻塞版本) 4.0 Servo.h 舵机代码 1.舵机简介 舵机也叫伺服电机,是控制输入PWM信号来精确控制转动角度.所以想要驱动舵机就是让ard…...
Oracle Database 23ai 中的DBMS_HCHECK
在 Oracle 23ai 中,DBMS_HCHECK 包允许我们检查数据库中已知的数据字典问题。 几年前,Oracle 发布了 hcheck.sql 脚本(文档 ID 136697.1)来检查数据库中已知的数据字典问题。 DBMS_HCHECK 包意味着我们不再需要下载 hcheck.sql…...
如何利用AWS监听存储桶并上传到tg bot
业务描述: 需要监听aws的存储中的最新消息,发送新的消息推送到指定tg的频道。 主要流程: 1.上传消息到s3存储桶(不做具体描述) 2.通过aws的lambda监听s3存储桶的最新消息(txt文件) 3.将txt文件…...
STM32 SPI读取SD卡
七个响应类型: R1 Response (Normal Response): R1响应是最基本的响应,包含一个字节的状态位,用于指示命令是否成功执行。常用。最高位为0。最低位为1表示是空闲状态。其他位是各种错误提示。 R1b Response (Normal with Busy): 类似于R1&a…...
TANGO与LabVIEW控制系统集成
TANGO 是一个开源的设备控制和数据采集框架,主要用于管理实验室设备、自动化系统和工业设备。它为不同类型的硬件提供统一的控制接口,并支持设备之间的通信,广泛应用于粒子加速器、同步辐射光源、实验室自动化和工业控制等领域。 1. TANGO的核…...
eth_type_trans 函数
eth_type_trans 是 Linux 内核网络子系统中的一个函数,它主要用于确定接收到的以太网数据包(Ethernet frame)的协议类型,并设置相应的 sk_buff 结构体的协议字段。以下是关于 eth_type_trans 的详细解释: 功能 eth_type_trans 函数的主要功能是根据以太网数据包的目的 M…...
派克汉尼汾推出新的快换接头产品系列,扩展热管理解决方案
近期,运动与控制技术领域的先行者——派克汉尼汾宣布推出四个具有开创性的热管理解决方案——NSAC、NSEC和NSIC系列盲插式快换接头以及NSSC螺纹连接快换接头。这些创新产品旨在满足电子冷却、电池制造、信息技术、能源管理、工程机械和运输等行业复杂的热管理需求。…...
uniapp 前端解决精度丢失的问题 (后端返回分布式id)
原因: 后端使用分布式id, id为19位数,导致精度丢失 ,前端解决方法 这个是通过浏览器请求回来的数据,这个时候id 数据已经丢失了,在数据库查询不到,在调获详情接口的时候会有问题 实际的: 解决…...
C语言:指针4(常量指针和指针常量及动态内存分配)
常量指针与指针常量 常量:分为字面量和只读常量,字面量就是我们平时直接操作的量: printf("%d\n",12);/printf("%s\n","hello");只读常量使用关键字 const 修饰,凡是被这个关键字修饰 的变量&…...
Win11提示fveapi.dll丢失是什么原因?fveapi.dll丢失怎么办?
一、fveapi.dll丢失的成因与影响 成因: 系统更新不完整:Win11系统在更新过程中,如果某个环节出现问题,可能会导致fveapi.dll等系统文件未能正确更新或安装。软件冲突:某些第三方软件可能与系统文件发生冲突ÿ…...
台球助教平台系统开发APP和小程序信息收藏功能需求解析(第十二章)
以下是开发台球助教系统客户端(APP,小程序,H5)几端的信息收藏功能的详细需求和功能说明,内容比较详细,可以说是一个教科书式的详细说明了,这套需求说明不仅仅用在我们的台球助教系统程序上&…...
如何设计 Vue 3 组件库:高效的组件化开发方法
如何设计 Vue 3 组件库:高效的组件化开发方法 📖 前言 随着前端技术的不断发展,Vue.js 已成为现代化 Web 应用开发的主流框架之一。Vue 3 引入了诸多改进,尤其是组合式 API,使得 Vue 在开发大型项目时,能够…...
第八节、Bresenham直线插补运动【51单片机-L298N-步进电机教程】
摘要:前面章节主要介绍单个电机控制,本节内容介绍两个电机完成直线插补运动 一、 Bresenham直线算法介绍 Bresenham直线算法由Jack Elton Bresenham于1962年在IBM开发,最初用于计算机显示直线,它确定应该选择的n维光栅的点&#…...
PPT|230页| 制造集团企业供应链端到端的数字化解决方案:从需求到结算的全链路业务闭环构建
制造业采购供应链管理是企业运营的核心环节,供应链协同管理在供应链上下游企业之间建立紧密的合作关系,通过信息共享、资源整合、业务协同等方式,实现供应链的全面管理和优化,提高供应链的效率和透明度,降低供应链的成…...
转转集团旗下首家二手多品类循环仓店“超级转转”开业
6月9日,国内领先的循环经济企业转转集团旗下首家二手多品类循环仓店“超级转转”正式开业。 转转集团创始人兼CEO黄炜、转转循环时尚发起人朱珠、转转集团COO兼红布林CEO胡伟琨、王府井集团副总裁祝捷等出席了开业剪彩仪式。 据「TMT星球」了解,“超级…...
04-初识css
一、css样式引入 1.1.内部样式 <div style"width: 100px;"></div>1.2.外部样式 1.2.1.外部样式1 <style>.aa {width: 100px;} </style> <div class"aa"></div>1.2.2.外部样式2 <!-- rel内表面引入的是style样…...
实现弹窗随键盘上移居中
实现弹窗随键盘上移的核心思路 在Android中,可以通过监听键盘的显示和隐藏事件,动态调整弹窗的位置。关键点在于获取键盘高度,并计算剩余屏幕空间以重新定位弹窗。 // 在Activity或Fragment中设置键盘监听 val rootView findViewById<V…...
使用 SymPy 进行向量和矩阵的高级操作
在科学计算和工程领域,向量和矩阵操作是解决问题的核心技能之一。Python 的 SymPy 库提供了强大的符号计算功能,能够高效地处理向量和矩阵的各种操作。本文将深入探讨如何使用 SymPy 进行向量和矩阵的创建、合并以及维度拓展等操作,并通过具体…...
全面解析数据库:从基础概念到前沿应用
在数字化时代,数据已成为企业和社会发展的核心资产,而数据库作为存储、管理和处理数据的关键工具,在各个领域发挥着举足轻重的作用。从电商平台的商品信息管理,到社交网络的用户数据存储,再到金融行业的交易记录处理&a…...
高端性能封装正在突破性能壁垒,其芯片集成技术助力人工智能革命。
2024 年,高端封装市场规模为 80 亿美元,预计到 2030 年将超过 280 亿美元,2024-2030 年复合年增长率为 23%。 细分到各个终端市场,最大的高端性能封装市场是“电信和基础设施”,2024 年该市场创造了超过 67% 的收入。…...
41道Django高频题整理(附答案背诵版)
解释一下 Django 和 Tornado 的关系? Django和Tornado都是Python的web框架,但它们的设计哲学和应用场景有所不同。 Django是一个高级的Python Web框架,鼓励快速开发和干净、实用的设计。它遵循MVC设计,并强调代码复用。Django有…...
RushDB开源程序 是现代应用程序和 AI 的即时数据库。建立在 Neo4j 之上
一、软件介绍 文末提供程序和源码下载 RushDB 改变了您处理图形数据的方式 — 不需要 Schema,不需要复杂的查询,只需推送数据即可。 二、Key Features ✨ 主要特点 Instant Setup: Be productive in seconds, not days 即时设置 :在几秒钟…...
02-性能方案设计
需求分析与测试设计 根据具体的性能测试需求,确定测试类型,以及压测的模块(web/mysql/redis/系统整体)前期要与相关人员充分沟通,初步确定压测方案及具体的性能指标QA完成性能测试设计后,需产出测试方案文档发送邮件到项目组&…...
