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

ClickHouse 窗口函数使用详解(一) - 若

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) 详解

一句话概括

"在每个部门内部,按工资从高到低给员工编号"

逐词解析

1. ROW_NUMBER()

  • 作用:生成顺序编号

  • 特点:从1开始,连续不重复(1, 2, 3, 4...)

  • 类似功能RANK()DENSE_RANK()

2. OVER

  • 作用:定义"窗口"的范围

  • 含义:表示接下来的括号内描述如何划分数据窗口

3. PARTITION BY department

  • 作用:按部门分组

  • 含义:为每个独立的部门创建单独的编号序列

  • 效果:每个部门都从1开始重新编号

4. ORDER BY salary DESC

  • 作用:在部门内部按工资降序排列

  • 含义:工资最高的排在第1位,依次类推

实际示例

假设有员工数据:

 
 
姓名 部门 工资
张三 技术部 9000
李四 技术部 8000
王五 技术部 7500
赵六 销售部 7000
钱七 销售部 8500

执行过程分解

步骤1:按部门分区

text
技术部窗口:
张三 9000
李四 8000  
王五 7500销售部窗口:
钱七 8500
赵六 7000

步骤2:部门内部按工资降序排序

text
技术部窗口(排序后):
张三 9000  ← 第1名
李四 8000  ← 第2名  
王五 7500  ← 第3名销售部窗口(排序后):
钱七 8500  ← 第1名
赵六 7000  ← 第2名

步骤3:分配行号

text
最终结果:
张三 技术部 9000  rn=1
李四 技术部 8000  rn=2
王五 技术部 7500  rn=3
钱七 销售部 8500  rn=1
赵六 销售部 7000  rn=2

完整SQL示例

sql
SELECT name,department, salary,ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employees;

输出结果:

 
 
name department salary rn
张三 技术部 9000 1
李四 技术部 8000 2
王五 技术部 7500 3
钱七 销售部 8500 1
赵六 销售部 7000 2

在数据去重中的应用

原始问题

在区块链任务表中,每个 (start_block, end_block) 有多个版本,需要取最新版本。

解决方案

sql
SELECT * FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY start_block, end_block ORDER BY created_at DESC) as rnFROM block_tasks WHERE status = 'init'
) WHERE rn = 1;

解读

  • PARTITION BY start_block, end_block:为每个唯一的区块范围创建独立窗口

  • ORDER BY created_at DESC:在每个窗口内,按创建时间降序排列(最新的在前)

  • rn = 1:只取每个窗口的第1条记录(即最新记录)

与其他排名函数的区别

sql
-- 数据示例:工资有相同的情况
技术部:9000, 8000, 8000, 7500-- ROW_NUMBER(): 连续唯一编号
9000 → 1
8000 → 2  
8000 → 3  ← 相同工资也分配不同编号
7500 → 4-- RANK(): 排名,相同值并列
9000 → 1
8000 → 2  ← 并列第2名
8000 → 2  ← 并列第2名  
7500 → 4  ← 跳过第3名-- DENSE_RANK(): 密集排名,无间隔
9000 → 1
8000 → 2  ← 并列第2名
8000 → 2  ← 并列第2名
7500 → 3  ← 不跳过名次

实际应用场景

场景1:获取每个部门工资最高的员工

sql
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rnFROM employees
) WHERE rn = 1;

场景2:分页查询(按部门分组分页)

sql
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) as rnFROM employees
) WHERE rn BETWEEN 1 AND 10;  -- 每个部门的前10条

场景3:删除重复数据

sql
DELETE FROM employees 
WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rnFROM employees) WHERE rn > 1  -- 保留每个email的第一条,删除重复
);

总结

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 的核心价值:

  1. 分组排序:在保持分组的同时进行排序

  2. 精确选择:可以明确选择每个分组的第N条记录

  3. 数据去重:轻松实现"保留每个分组最新/最大的一条"

  4. 分页支持:支持复杂的分组分页需求

这个语法组合是现代SQL中极其重要的功能,特别适合处理"分组取Top N"这类经典问题。

http://www.hskmm.com/?act=detail&tid=21551

相关文章:

  • ClickHouse 窗口函数详解:告别 GROUP BY 的局限性,实现灵活数据分析 - 若
  • 简单WEB网站
  • AtCoder AGC044 总结
  • UOJ#32【UR #2】跳蚤公路 题解
  • 2025 年窗帘杆源头厂家最新推荐榜单:包含支架 / 环 / 全自动 / 可伸缩等多类产品及配件,帮助选到品质与交期双优的优质厂家
  • 2025 年电动窗帘厂家推荐榜单:聚焦国内优质企业定制实力与口碑,为采购者提供最新选择参考电动窗帘系统/电机/轨道/配件/智能电动窗帘厂家推荐
  • Vue3 使用注意事项
  • ClickHouse ReplacingMergeTree 去重陷阱:为什么你的 FINAL 查询无效? - 若
  • js中?? 和 || 的区别详解
  • 微信机器人API接口| 个人开发者必备
  • 直击现场! “ 直通乌镇 ”开源赛复赛收官,OpenCSG担任评委,十强藏着哪些产业机会?
  • Python 列表生成式、字典生成式与生成器表达式
  • java 解析json字符串,获取特定的字段值,JsonObject
  • python 批量提取txt数据中的值写入csv
  • 回忆中学的函数
  • Java 一行一行的读取文本,小Demo 大学问
  • 家里wifi电信出口ip如何控制不变,解决访问云服务器上面的资源
  • 2025 年挤压造粒机源头厂家最新推荐榜单:前五企业技术实力、服务能力及口碑测评指南对辊挤压/化肥挤压/干粉挤压造粒机厂家推荐
  • MYSQL数据库取消表的约束
  • 2025 年京东 e 卡回收平台最新推荐排行榜:权威测评实时结算平台,助力用户安全高效转让京东 e 卡
  • 2025 年支付宝消费券回收平台最新推荐榜单:优质平台权威测评,助您高效安全处理闲置消费券支付宝消费券回收/闲置支付宝消费券回收/支付宝消费券快速回收平台推荐
  • ICP备案查询网站 域名备案查询
  • 2025 年注浆管厂家最新权威推荐排行榜:聚焦 R780/108 / 隧道 / 预埋 / 桩基等专用产品,精选 TOP5 优质企业
  • stable diffusion网络结构详解
  • 9.30
  • 网络与系统攻防技术实验一——逆向破解与Bof
  • 【python】解决grpcio.protoc生成的pb文件里面没有类和方法定义
  • 阙韩
  • “计算机配置\Windows 设置\安全设置\本地策略\审核策略” 配置后不生效
  • Spring Boot 事件发布与监听 观察者模式的实际应用 - 实践