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维光栅的点&#…...
超短脉冲激光自聚焦效应
前言与目录 强激光引起自聚焦效应机理 超短脉冲激光在脆性材料内部加工时引起的自聚焦效应,这是一种非线性光学现象,主要涉及光学克尔效应和材料的非线性光学特性。 自聚焦效应可以产生局部的强光场,对材料产生非线性响应,可能…...
k8s从入门到放弃之Ingress七层负载
k8s从入门到放弃之Ingress七层负载 在Kubernetes(简称K8s)中,Ingress是一个API对象,它允许你定义如何从集群外部访问集群内部的服务。Ingress可以提供负载均衡、SSL终结和基于名称的虚拟主机等功能。通过Ingress,你可…...
HBuilderX安装(uni-app和小程序开发)
下载HBuilderX 访问官方网站:https://www.dcloud.io/hbuilderx.html 根据您的操作系统选择合适版本: Windows版(推荐下载标准版) Windows系统安装步骤 运行安装程序: 双击下载的.exe安装文件 如果出现安全提示&…...
WordPress插件:AI多语言写作与智能配图、免费AI模型、SEO文章生成
厌倦手动写WordPress文章?AI自动生成,效率提升10倍! 支持多语言、自动配图、定时发布,让内容创作更轻松! AI内容生成 → 不想每天写文章?AI一键生成高质量内容!多语言支持 → 跨境电商必备&am…...
Redis数据倾斜问题解决
Redis 数据倾斜问题解析与解决方案 什么是 Redis 数据倾斜 Redis 数据倾斜指的是在 Redis 集群中,部分节点存储的数据量或访问量远高于其他节点,导致这些节点负载过高,影响整体性能。 数据倾斜的主要表现 部分节点内存使用率远高于其他节…...
C++使用 new 来创建动态数组
问题: 不能使用变量定义数组大小 原因: 这是因为数组在内存中是连续存储的,编译器需要在编译阶段就确定数组的大小,以便正确地分配内存空间。如果允许使用变量来定义数组的大小,那么编译器就无法在编译时确定数组的大…...
Go 并发编程基础:通道(Channel)的使用
在 Go 中,Channel 是 Goroutine 之间通信的核心机制。它提供了一个线程安全的通信方式,用于在多个 Goroutine 之间传递数据,从而实现高效的并发编程。 本章将介绍 Channel 的基本概念、用法、缓冲、关闭机制以及 select 的使用。 一、Channel…...
[ACTF2020 新生赛]Include 1(php://filter伪协议)
题目 做法 启动靶机,点进去 点进去 查看URL,有 ?fileflag.php说明存在文件包含,原理是php://filter 协议 当它与包含函数结合时,php://filter流会被当作php文件执行。 用php://filter加编码,能让PHP把文件内容…...
sshd代码修改banner
sshd服务连接之后会收到字符串: SSH-2.0-OpenSSH_9.5 容易被hacker识别此服务为sshd服务。 是否可以通过修改此banner达到让人无法识别此服务的目的呢? 不能。因为这是写的SSH的协议中的。 也就是协议规定了banner必须这么写。 SSH- 开头,…...
aardio 自动识别验证码输入
技术尝试 上周在发学习日志时有网友提议“在网页上识别验证码”,于是尝试整合图像识别与网页自动化技术,完成了这套模拟登录流程。核心思路是:截图验证码→OCR识别→自动填充表单→提交并验证结果。 代码在这里 import soImage; import we…...
