窗口函数实操：ROW_NUMBER / RANK / DENSE_RANK

一句话：窗口函数是SQL中用于复杂计算的函数，可以在不减少行数的情况下进行聚合、排序、排名等操作。

1. 窗口函数基础

1.1 什么是窗口函数？


graph LR

    A[窗口函数] --> B[聚合窗口函数]

    A --> C[排名窗口函数]

    A --> D[其他窗口函数]

    B --> B1[SUM/AVG/COUNT]

    C --> C1[ROW_NUMBER/RANK/DENSE_RANK]

    C --> C2[NTILE/PERCENT_RANK]

    D --> D1[LAG/LEAD/FIRST_VALUE]

    style C fill:#e1f5fe

窗口函数：在满足特定条件的结果集范围内，对数据进行分组、排序、聚合等操作，同时保留原有行。

与GROUP BY的区别：

GROUP BY：减少行数，每组一行
窗口函数：保留行数，在每行上计算

1.2 语法结构

 
函数名() OVER (
 
    [PARTITION BY 分组列]
 
    [ORDER BY 排序列 [ASC|DESC]]
 
    [窗口帧]
 
)

2. 排名窗口函数

2.1 ROW_NUMBER

 
-- 为每行分配唯一序号，无并列
 
SELECT
 
    name,
 
    score,
 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_num
 
FROM students;

特点：

唯一序号，无并列
即使分数相同，序号也不同
适用于需要唯一标识的场景

2.2 RANK

 
-- 排名，有并列，跳过后续排名
 
SELECT
 
    name,
 
    score,
 
    RANK() OVER (ORDER BY score DESC) AS rank_num
 
FROM students;

特点：

有并列排名
并列后跳过后续排名（如1,2,2,4）
适用于需要并列排名的场景

2.3 DENSE_RANK

 
-- 排名，有并列，不跳过后续排名
 
SELECT
 
    name,
 
    score,
 
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank_num
 
FROM students;

特点：

有并列排名
并列后不跳过后续排名（如1,2,2,3）
适用于需要连续排名的场景

2.4 三者对比

 
-- 示例数据
 
CREATE TABLE scores (
 
    id INT PRIMARY KEY,
 
    name VARCHAR(50),
 
    score INT
 
);
 
INSERT INTO scores VALUES
 
(1, 'Alice', 95),
 
(2, 'Bob', 90),
 
(3, 'Charlie', 90),
 
(4, 'David', 85);
 
-- 查询对比
 
SELECT
 
    name,
 
    score,
 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
 
    RANK() OVER (ORDER BY score DESC) AS rank_num,
 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
 
FROM scores;

结果：

name	score	row_num	rank_num	dense_rank_num
Alice	95	1	1	1
Bob	90	2	2	2
Charlie	90	3	2	2
David	85	4	4	3

3. 其他窗口函数

3.1 NTILE

 
-- 将数据分成N个桶
 
SELECT
 
    name,
 
    score,
 
    NTILE(3) OVER (ORDER BY score DESC) AS bucket
 
FROM students;

用途：数据分桶、分组抽样

3.2 LAG / LEAD

 
-- 获取前一行/后一行的值
 
SELECT
 
    date,
 
    sales,
 
    LAG(sales, 1) OVER (ORDER BY date) AS prev_sales,
 
    LEAD(sales, 1) OVER (ORDER BY date) AS next_sales
 
FROM daily_sales;

用途：计算环比、同比、时间序列分析

3.3 FIRST_VALUE / LAST_VALUE

 
-- 获取窗口内第一行/最后一行的值
 
SELECT
 
    name,
 
    department,
 
    salary,
 
    FIRST_VALUE(salary) OVER (
 
        PARTITION BY department
 
        ORDER BY salary DESC
 
    ) AS dept_max_salary
 
FROM employees;

用途：获取组内最大值、最小值

4. 窗口帧

4.1 什么是窗口帧？

 
-- 窗口帧定义
 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW

窗口帧类型：

ROWS：物理行
RANGE：逻辑范围

4.2 常见窗口帧

 
-- 累计求和
 
SUM(sales) OVER (
 
    ORDER BY date
 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
) AS cumulative_sales
 
-- 滑动平均
 
AVG(sales) OVER (
 
    ORDER BY date
 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 
) AS moving_avg_7d

6. 性能优化

6.1 索引优化

 
-- 窗口函数需要排序，确保排序列有索引
 
CREATE INDEX idx_department_salary ON employees(department, salary);

6.2 避免过度分区

 
-- 不要过度分区
 
SELECT
 
    name,
 
    department,
 
    salary,
 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rank  -- 全局排名
 
FROM employees;
 
-- 而不是
 
SELECT
 
    name,
 
    department,
 
    salary,
 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank  -- 每个部门单独排名
 
FROM employees;

7. 常见坑点

1. 窗口函数与GROUP BY顺序

 
-- 错误：窗口函数在GROUP BY之后执行
 
SELECT
 
    department,
 
    AVG(salary),
 
    ROW_NUMBER() OVER (ORDER BY AVG(salary) DESC)  -- ❌ 不能直接使用聚合函数
 
FROM employees
 
GROUP BY department;
 
-- 正确：使用子查询
 
SELECT *
 
FROM (
 
    SELECT
 
        department,
 
        AVG(salary) AS avg_salary,
 
        ROW_NUMBER() OVER (ORDER BY AVG(salary) DESC) AS rn
 
    FROM employees
 
    GROUP BY department
 
) t;

2. NULL值处理

 
-- NULL值会影响排序
 
SELECT
 
    name,
 
    score,
 
    RANK() OVER (ORDER BY score DESC) AS rank_num
 
FROM students;
 
-- NULL值会排在最后
 
-- 解决：使用COALESCE或NULLS FIRST/LAST
 
SELECT
 
    name,
 
    score,
 
    RANK() OVER (ORDER BY COALESCE(score, 0) DESC) AS rank_num
 
FROM students;

核心要点

 
-- 排名函数
 
ROW_NUMBER()  -- 唯一序号，无并列
 
RANK()        -- 有并列，跳过后续排名
 
DENSE_RANK()  -- 有并列，不跳过后续排名
 
-- 语法
 
函数名() OVER (
 
    PARTITION BY 分组列
 
    ORDER BY 排序列 [ASC|DESC]
 
    [ROWS/RANGE 窗口帧]
 
)
 
SELECT *
 
FROM (
 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 分组列 ORDER BY 排序列 DESC) AS rn
 
    FROM 表
 
) t
 
WHERE rn = 1;

速记卡（面试闪卡）

Q1：一句话讲清「窗口函数实操：ROW_NUMBER / RANK / DENSE_RANK」到底是什么？

A：窗口函数在保留每行的前提下做分组排序排名，不像GROUP BY那样压缩行数；ROW_NUMBER给唯一序号，RANK并列跳号，DENSE_RANK并列不跳号。

Q2：窗口函数 vs GROUP BY：不减行的聚合 —— 怎么理解？

A：窗口函数（Window Function）像给每个员工发工资条时顺便标排名，全员都在；GROUP BY 像把同部门员工合并成一行统计。窗口函数用 OVER() 框定”窗口”范围，行数不变。

Q3：ROW_NUMBER：严格发号 —— 怎么理解？

A：ROW_NUMBER() 给每行一个唯一序号，哪怕分数相同序号也不同（1,2,3,4），像排队领号机——绝对不重复，适合需要唯一标识、去重取第一条的场景。

Q4：RANK vs DENSE_RANK：并列怎么排 —— 怎么理解？

A：RANK() 并列后跳号（1,2,2,4），DENSE_RANK() 并列不跳号（1,2,2,3）。区别在于是否”占用”名次：RANK 把并列占的名次也算上，DENSE_RANK 紧挨着往下排，都不留空。

Q5：OVER 子句：框定窗口的三要素 —— 怎么理解？

A：OVER() 是窗口函数的灵魂：PARTITION BY 分组（类似 GROUP BY 但不压缩）、ORDER BY 排序、窗口帧（Frame）限定计算范围。三者组合决定”在哪一窗、按啥序、算哪些行”。

Q6：核心速记主线有哪些？

窗口函数保留行数，GROUP BY 压缩行数
ROW_NUMBER 唯一序号无并列
RANK 并列跳号，DENSE_RANK 并列不跳号
OVER(PARTITION BY … ORDER BY …) 三要素框窗口

口诀

A：窗口不砍行

发号各不同

并列跳不跳

OVER 定窗口

PorterYuan · 个人学习博客

探索

窗口函数实操：ROW_NUMBER / RANK / DENSE_RANK

窗口函数实操：ROW_NUMBER / RANK / DENSE_RANK

1. 窗口函数基础

1.1 什么是窗口函数？

1.2 语法结构

2. 排名窗口函数

2.1 ROW_NUMBER

2.2 RANK

2.3 DENSE_RANK

2.4 三者对比

3. 其他窗口函数

3.1 NTILE

3.2 LAG / LEAD

3.3 FIRST_VALUE / LAST_VALUE

4. 窗口帧

4.1 什么是窗口帧？

4.2 常见窗口帧

6. 性能优化

6.1 索引优化

6.2 避免过度分区

7. 常见坑点

1. 窗口函数与GROUP BY顺序

2. NULL值处理

核心要点

速记卡（面试闪卡）

相关链接

关系图谱

目录

反向链接

近期更新

页面走丢了

AI Agent 方法论与产品思维路线图

LeetCode 学习路线图（186 题）

八股文学习路线图

技术学习路线图（AI Agent 开发）

全局导航