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

ClickHouse 窗口函数使用详解（一） - 若

news 2026/5/12 6:45:01

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的第一条，删除重复
);