浅谈 SQL 窗口函数：ROW_NUMBER() 与聚合函数的妙用

在日常开发中，我们经常会遇到这样的需求：既要保留明细数据，又要对数据进行排名、累计、分区统计。如果仅依赖传统的 GROUP BY，往往需要做多次子查询或者复杂的 JOIN，既繁琐又低效。

而 窗口函数（Window Function） 就是为了解决这类问题而生的。它的最大特点是：保留所有行，并在每一行的基础上增加一个“运算结果列”。

窗口函数的执行过程

可以把窗口函数的执行理解为以下三个步骤：

1. 得到基础结果集
   先执行 FROM、WHERE、GROUP BY、HAVING 等，形成基础结果集。此时还没有窗口函数的列。

2. 在结果集上运算
   根据 OVER (PARTITION BY ... ORDER BY ...) 指定的分区规则和排序规则，对结果集的一组行进行计算。

3. 合并结果
   把窗口函数的计算值作为新列，附加到结果集的每一行。最终输出就是“原始列 + 窗口函数列”。

⚡ 和 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 表信息如下：

我们想给所有学生按成绩排名：

SELECT id, name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, id ASC) AS rn
FROM students;

结果：

其中：

• ROW_NUMBER() → 为每行分配一个顺序号；
• ORDER BY score DESC, id ASC → 排序规则，分数高的排前面；
• 最终每一行都多了一个 rn 列，表示它的名次。

例子二：ROW_NUMBER() 分区内排序

再来看sales 表中的信息：

需求：在每个区域内，按销售额从高到低排名。

SELECT
  id,
  region,
  salesman,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY region
    ORDER BY amount DESC
  ) AS rn
FROM sales;

结果：

其中：

• PARTITION BY region → 按区域分区，每个区域单独排名。
• ROW_NUMBER() → 每个区域内部从 1 开始编号。
• 最终结果集仍然包含所有行，只是多了一个“区域内排名”的列。

例子三：SUM() OVER 分区聚合

继续使用 sales 表。
需求：在保留明细行的同时，显示该销售人员所在区域的总销售额。

SELECT
  id,
  region,
  salesman,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

结果：

其中：

• SUM(amount) OVER (PARTITION BY region) → 在每个区域内计算总额；
• 结果仍然返回 5 行，只是每行多了一个“区域总额”的列。

窗口函数 vs GROUP BY

对比一下 GROUP BY：

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

结果只有 2 行：

可以看到：

• GROUP BY 会把多行压缩成一行。
• 窗口函数则保留明细，把聚合结果“合并回去”。

所以：

• GROUP BY：适合做汇总统计，结果行数减少。
• 窗口函数：适合做分析计算，保留明细又能展示分组/累计/排名结果。

总结

1. 窗口函数执行顺序：先生成基础结果集，再对结果集进行分区/排序运算，最后把结果加到每行。

2. 保留所有行：窗口函数不会减少行数，只会增加新列。

3. 典型应用场景：

   • 排名：ROW_NUMBER()、RANK()、DENSE_RANK()
   • 分区统计：SUM() OVER (PARTITION BY ...)
   • 累计计算：SUM() OVER (ORDER BY ...)
   • 移动平均、窗口滑动分析

掌握窗口函数，可以极大简化 SQL 写法，让我们的代码更直观、更高效。