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:按部门分区
技术部窗口:
张三 9000
李四 8000
王五 7500销售部窗口:
钱七 8500
赵六 7000
步骤2:部门内部按工资降序排序
技术部窗口(排序后):
张三 9000 ← 第1名
李四 8000 ← 第2名
王五 7500 ← 第3名销售部窗口(排序后):
钱七 8500 ← 第1名
赵六 7000 ← 第2名
步骤3:分配行号
最终结果:
张三 技术部 9000 rn=1
李四 技术部 8000 rn=2
王五 技术部 7500 rn=3
钱七 销售部 8500 rn=1
赵六 销售部 7000 rn=2
完整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)
有多个版本,需要取最新版本。
解决方案
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条记录(即最新记录)
与其他排名函数的区别
-- 数据示例:工资有相同的情况
技术部: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:获取每个部门工资最高的员工
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rnFROM employees
) WHERE rn = 1;
场景2:分页查询(按部门分组分页)
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) as rnFROM employees
) WHERE rn BETWEEN 1 AND 10; -- 每个部门的前10条
场景3:删除重复数据
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 ...)
的核心价值:
-
分组排序:在保持分组的同时进行排序
-
精确选择:可以明确选择每个分组的第N条记录
-
数据去重:轻松实现"保留每个分组最新/最大的一条"
-
分页支持:支持复杂的分组分页需求
这个语法组合是现代SQL中极其重要的功能,特别适合处理"分组取Top N"这类经典问题。