浅谈 SQL 窗口函数：ROW_NUMBER() 与聚合函数的妙用
在日常开发中，我们经常会遇到这样的需求：既要保留明细数据，又要对数据进行排名、累计、分区统计。如果仅依赖传统的 GROUP BY，往往需要做多次子查询或者复杂的 JOIN，既繁琐又低效。
而 窗口函数（Window Function） 就是为了解决这类问题而生的。它的最大特点是：保留所有行，并在每一行的基础上增加一个“运算结果列”。
窗口函数的执行过程
可以把窗口函数的执行理解为以下三个步骤：
-
得到基础结果集
先执行FROM、WHERE、GROUP BY、HAVING等，形成基础结果集。此时还没有窗口函数的列。 -
在结果集上运算
根据OVER (PARTITION BY ... ORDER BY ...)指定的分区规则和排序规则，对结果集的一组行进行计算。 -
合并结果
把窗口函数的计算值作为新列，附加到结果集的每一行。最终输出就是“原始列 + 窗口函数列”。
⚡ 和 GROUP BY 的最大区别在于：
GROUP BY会压缩行，一组只保留一行。- 窗口函数会保留所有行，只是多了一列运算结果。
实验准备
首先，我们创建两张用于实验的数据库表。
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,score INT NOT NULL
);-- 插入示例数据
INSERT INTO students (id, name, score) VALUES
(1, '张三', 95),
(2, '李四', 88),
(3, '王五', 88),
(4, '赵六', 72);CREATE TABLE sales (id INT PRIMARY KEY,region VARCHAR(20) NOT NULL,salesman VARCHAR(50) NOT NULL,amount INT NOT NULL
);-- 插入示例数据
INSERT INTO sales (id, region, salesman, amount) VALUES
(1, '东区', '张三', 1000),
(2, '东区', '李四', 1200),
(3, '西区', '王五', 800),
(4, '西区', '赵六', 950),
(5, '西区', '孙七', 700);
例子一：ROW_NUMBER() 全局排序
students 表信息如下：
| id | name | score |
|---|---|---|
| 1 | 张三 | 95 |
| 2 | 李四 | 88 |
| 3 | 王五 | 88 |
| 4 | 赵六 | 72 |
我们想给所有学生按成绩排名：
SELECT id, name, score,ROW_NUMBER() OVER (ORDER BY score DESC, id ASC) AS rn
FROM students;
结果：
| id | name | score | rn |
|---|---|---|---|
| 1 | 张三 | 95 | 1 |
| 2 | 李四 | 88 | 2 |
| 3 | 王五 | 88 | 3 |
| 4 | 赵六 | 72 | 4 |
其中：
ROW_NUMBER()→ 为每行分配一个顺序号；ORDER BY score DESC, id ASC→ 排序规则，分数高的排前面；- 最终每一行都多了一个
rn列，表示它的名次。
例子二：ROW_NUMBER() 分区内排序
再来看sales 表中的信息：
| id | region | salesman | amount |
|---|---|---|---|
| 1 | 东区 | 张三 | 1000 |
| 2 | 东区 | 李四 | 1200 |
| 3 | 西区 | 王五 | 800 |
| 4 | 西区 | 赵六 | 950 |
| 5 | 西区 | 孙七 | 700 |
需求：在每个区域内，按销售额从高到低排名。
SELECTid,region,salesman,amount,ROW_NUMBER() OVER (PARTITION BY regionORDER BY amount DESC) AS rn
FROM sales;
结果：
| id | region | salesman | amount | rn |
|---|---|---|---|---|
| 2 | 东区 | 李四 | 1200 | 1 |
| 1 | 东区 | 张三 | 1000 | 2 |
| 4 | 西区 | 赵六 | 950 | 1 |
| 3 | 西区 | 王五 | 800 | 2 |
| 5 | 西区 | 孙七 | 700 | 3 |
其中：
PARTITION BY region→ 按区域分区，每个区域单独排名。ROW_NUMBER()→ 每个区域内部从 1 开始编号。- 最终结果集仍然包含所有行，只是多了一个“区域内排名”的列。
例子三：SUM() OVER 分区聚合
继续使用 sales 表。
需求：在保留明细行的同时，显示该销售人员所在区域的总销售额。
SELECTid,region,salesman,amount,SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
结果：
| id | region | salesman | amount | region_total |
|---|---|---|---|---|
| 1 | 东区 | 张三 | 1000 | 2200 |
| 2 | 东区 | 李四 | 1200 | 2200 |
| 3 | 西区 | 王五 | 800 | 2450 |
| 4 | 西区 | 赵六 | 950 | 2450 |
| 5 | 西区 | 孙七 | 700 | 2450 |
其中：
SUM(amount) OVER (PARTITION BY region)→ 在每个区域内计算总额；- 结果仍然返回 5 行，只是每行多了一个“区域总额”的列。
窗口函数 vs GROUP BY
| 特性 | 窗口函数 (Window Functions) | GROUP BY 聚合 (Aggregate) |
|---|---|---|
| 是否保留明细行 | ✅ 保留所有行，只是在每行后面加一个新列 | ❌ 会压缩行，每个分组只保留一行 |
| 典型用途 | 排名 (ROW_NUMBER)、分区统计 (SUM OVER)、累计值、移动平均等 | 分组统计 (SUM、AVG、COUNT、MAX、MIN 等) |
| 是否依赖 PARTITION BY | 可选。PARTITION BY 决定分区范围，不写则针对全表计算 | 必须分组，语义上天然就是“按分组聚合” |
| ORDER BY 的作用 | 在窗口内排序，影响计算结果（如行号、累计和） | 在最终结果集中排序，对聚合计算无影响 |
| 返回结果行数 | 与原始结果集相同 | 行数 = 分组数（通常远少于原始行数） |
| 复杂度 | 一般更直观，避免子查询和 JOIN，常用于分析型 SQL | 用于汇总报表，逻辑简单，但不能同时保留明细数据 |
| 举例 | SUM(amount) OVER (PARTITION BY region) | SELECT region, SUM(amount) FROM sales GROUP BY region; |
对比一下 GROUP BY：
SELECT region, SUM(amount)
FROM sales
GROUP BY region;
结果只有 2 行：
| region | sum |
|---|---|
| 东区 | 2200 |
| 西区 | 2450 |
可以看到：
GROUP BY会把多行压缩成一行。- 窗口函数则保留明细，把聚合结果“合并回去”。
所以：
- GROUP BY：适合做汇总统计，结果行数减少。
- 窗口函数：适合做分析计算，保留明细又能展示分组/累计/排名结果。
总结
-
窗口函数执行顺序：先生成基础结果集，再对结果集进行分区/排序运算，最后把结果加到每行。
-
保留所有行：窗口函数不会减少行数，只会增加新列。
-
典型应用场景：
- 排名：
ROW_NUMBER()、RANK()、DENSE_RANK() - 分区统计：
SUM() OVER (PARTITION BY ...) - 累计计算：
SUM() OVER (ORDER BY ...) - 移动平均、窗口滑动分析
- 排名：
掌握窗口函数，可以极大简化 SQL 写法，让我们的代码更直观、更高效。