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 rn
    FROM 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 rn
    FROM employees
) WHERE rn = 1;

场景2：分页查询（按部门分组分页）

sql

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) as rn
    FROM 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 rn
        FROM employees
    ) WHERE rn > 1  -- 保留每个email的第一条，删除重复
);

总结

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

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

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

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

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

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