窗口函数

窗口函数（Window Function）可以在保留每一行明细的同时，计算排名、累计值、移动平均、前后行差值等结果。

普通聚合函数配合 GROUP BY 后，会把多行压缩成一行；窗口函数则为当前行选出一组相关行进行计算，并把结果附加到当前行上。可以先记住最重要的区别：

GROUP BY：多行 -> 一行汇总
窗口函数：多行 -> 仍然是多行，每行多一个计算结果

本文使用常见 SQL 语法。MySQL 8.0 及以上、PostgreSQL、SQL Server、Oracle 和较新的 SQLite 都支持主要窗口函数，但函数名称、窗口帧和扩展语法可能存在差异。

准备示例数据

假设有一张销售记录表：

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  salesperson VARCHAR(50) NOT NULL,
  region VARCHAR(20) NOT NULL,
  sold_on DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

示例数据如下：

id	salesperson	region	sold_on	amount
1	Alice	华东	2026-01-01	100.00
2	Bob	华东	2026-01-01	80.00
3	Alice	华东	2026-01-02	120.00
4	Carol	华西	2026-01-02	150.00
5	Bob	华东	2026-01-03	120.00
6	Dave	华西	2026-01-03	90.00

第一个窗口函数

下面的查询计算所有销售记录按金额从高到低的行号：

SELECT
  id,
  salesperson,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS amount_order
FROM sales
ORDER BY amount DESC, id;

ROW_NUMBER() 是函数，OVER (...) 表示它是一个窗口函数。ORDER BY amount DESC, id 决定窗口内部的排列顺序。

结果仍然有 6 行：

id	salesperson	amount	amount_order
4	Carol	150.00	1
3	Alice	120.00	2
5	Bob	120.00	3
1	Alice	100.00	4
6	Dave	90.00	5
2	Bob	80.00	6

金额相同时加入唯一的 id 作为第二排序条件，可以让行号稳定可预测。否则相同金额的两行谁在前，数据库不一定保证。

OVER 子句的三个核心部分

窗口定义常见形式如下：

函数() OVER (
  PARTITION BY 分区列
  ORDER BY 排序列
  ROWS BETWEEN 窗口起点 AND 窗口终点
)

三个部分分别回答：

1. PARTITION BY：和当前行一起计算的是哪一组数据？
2. ORDER BY：这一组数据按什么顺序排列？
3. 窗口帧：排好序后，当前行具体看前后哪些行？

不是每个函数都需要写全这三部分。排名通常需要排序，但不需要显式窗口帧；计算全组总额时可能只需要分区。

PARTITION BY：把窗口分组

如果要分别计算每个地区内部的排名，可以按地区分区：

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

每遇到一个新地区，行号会重新从 1 开始。PARTITION BY 与 GROUP BY 都有“分组”含义，但它不会减少结果行数。

如果省略 PARTITION BY，所有查询结果行属于同一个窗口分区。

三种常用排名函数

金额相同时，不同排名函数的处理方式不同：

SELECT
  salesperson,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_number_value,
  RANK() OVER (ORDER BY amount DESC) AS rank_value,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_value
FROM sales
ORDER BY amount DESC;

金额顺序	ROW_NUMBER()	RANK()	DENSE_RANK()
150	1	1	1
120	2	2	2
120	3	2	2
100	4	4	3

• ROW_NUMBER()：每行编号都不同，不处理并列；
• RANK()：相同值并列，后续名次会跳号；
• DENSE_RANK()：相同值并列，后续名次不跳号。

选择哪一个取决于业务语言。“取任意前三条记录”可以使用 ROW_NUMBER()；“取销售额前三名，包括并列”通常更适合 RANK() 或 DENSE_RANK()。

聚合函数也可以作为窗口函数

在聚合函数后添加 OVER，可以在保留明细的同时显示分组汇总：

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

同一地区的每一行都会显示相同的地区总额。这样可以继续计算当前记录占地区总额的比例：

SELECT
  salesperson,
  region,
  amount,
  amount / NULLIF(
    SUM(amount) OVER (PARTITION BY region),
    0
  ) AS region_ratio
FROM sales;

NULLIF(..., 0) 用于避免除以零。不同数据库对整数除法和结果类型的处理不同，实际使用时可能需要 CAST 为小数类型。

累计值

累计销售额需要窗口内部排序：

SELECT
  id,
  sold_on,
  amount,
  SUM(amount) OVER (
    ORDER BY sold_on, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales
ORDER BY sold_on, id;

窗口帧的含义是：从分区第一行开始，一直计算到当前行。

• UNBOUNDED PRECEDING：分区的第一行；
• CURRENT ROW：当前行；
• ROWS BETWEEN ... AND ...：按物理行范围选择窗口。

使用 id 打破同一天的并列，并显式写出 ROWS，可以避免数据库默认窗口帧和并列排序带来的意外结果。

如果要分别计算每位销售人员的累计值，再加入分区：

SUM(amount) OVER (
  PARTITION BY salesperson
  ORDER BY sold_on, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

移动平均

计算当前记录与前两条记录的三条移动平均：

SELECT
  id,
  sold_on,
  amount,
  AVG(amount) OVER (
    ORDER BY sold_on, id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_average
FROM sales
ORDER BY sold_on, id;

第一行窗口中只有一条记录，第二行有两条，从第三行开始最多包含三条。

注意，这里的“前两条”是排序后的两行，不一定等于“前两天”。如果一天有多条记录或日期中断，按日期区间计算需要使用数据库支持的 RANGE 写法，或先按天聚合再计算。

LAG 和 LEAD：访问前后行

LAG 可以取得上一行的值，常用于计算环比：

SELECT
  salesperson,
  sold_on,
  amount,
  LAG(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sold_on, id
  ) AS previous_amount
FROM sales
ORDER BY salesperson, sold_on, id;

还可以直接计算与上一笔销售额的差值：

SELECT
  salesperson,
  sold_on,
  amount,
  amount - LAG(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sold_on, id
  ) AS change_from_previous
FROM sales;

每个销售人员的第一条记录没有上一行，所以 LAG 返回 NULL。LEAD 与它相反，用于取得下一行的值。

部分数据库允许为偏移量和缺失时的默认值传参，例如 LAG(amount, 1, 0)。使用前应确认目标数据库的函数签名。

每组取前 N 条

窗口函数非常适合解决“每个地区取金额最高的两笔销售”这类问题。先在子查询中编号，再在外层过滤：

SELECT id, salesperson, region, amount
FROM (
  SELECT
    id,
    salesperson,
    region,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY amount DESC, id
    ) AS position_in_region
  FROM sales
) AS ranked_sales
WHERE position_in_region <= 2
ORDER BY region, position_in_region;

之所以需要子查询，是因为窗口函数通常在 WHERE 之后计算，不能直接这样写：

-- 错误：WHERE 阶段还没有计算出窗口函数结果
SELECT
  id,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS position
FROM sales
WHERE position <= 2;

部分数据库提供 QUALIFY 子句，可以直接过滤窗口函数结果，但它不是所有常见数据库都支持的通用语法。需要可移植性时，使用子查询或公共表表达式更稳妥。

SQL 中的计算顺序

理解窗口函数何时计算，有助于解释很多限制。可以把常见逻辑顺序简化为：

FROM / JOIN
    -> WHERE
    -> GROUP BY
    -> HAVING
    -> 窗口函数
    -> SELECT
    -> ORDER BY
    -> LIMIT

因此：

• WHERE 不能直接引用窗口函数结果；
• 窗口函数可以处理聚合后的结果；
• 最终 ORDER BY 可以使用窗口函数别名；
• 最终结果顺序仍由查询最外层的 ORDER BY 决定。

窗口定义中的 ORDER BY 只决定计算顺序，不保证查询结果的展示顺序。

命名窗口

多个函数使用相同窗口定义时，部分数据库支持用 WINDOW 为它命名：

SELECT
  salesperson,
  sold_on,
  amount,
  SUM(amount) OVER salesperson_timeline AS running_total,
  AVG(amount) OVER salesperson_timeline AS running_average
FROM sales
WINDOW salesperson_timeline AS (
  PARTITION BY salesperson
  ORDER BY sold_on, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

命名窗口可以减少重复并避免多个计算的分区、排序条件意外不一致。具体支持和语法应以目标数据库为准。

常见陷阱

排序条件不唯一

ROW_NUMBER()、LAG() 和累计值都依赖顺序。如果只按可能重复的日期或金额排序，同行之间的先后可能不稳定。通常应追加主键等唯一列作为最终排序条件。

忽略默认窗口帧

为聚合窗口函数加入 ORDER BY 后，默认窗口帧可能不再是整个分区；并列值也可能被一起纳入。需要累计或移动范围时，应明确写出 ROWS BETWEEN ... AND ...。

把窗口分区当成最终排序

窗口中的 ORDER BY 用于计算，不负责最终展示。需要稳定结果时，应在查询末尾再次写 ORDER BY。

在大结果集上创建昂贵窗口

分区和排序可能消耗大量内存、临时磁盘和 CPU。应尽量先过滤无关数据，检查执行计划，并为常见过滤与排序条件评估合适索引。

用窗口函数代替所有聚合

如果最终只需要每个地区一行总额，GROUP BY 更直接。只有需要同时保留明细，或进行排名、前后行比较、累计计算时，窗口函数才更合适。

小结

• 窗口函数在保留明细行的同时完成跨行计算。
• PARTITION BY 决定分组，窗口内的 ORDER BY 决定计算顺序，窗口帧决定当前行具体查看的范围。
• ROW_NUMBER、RANK、DENSE_RANK 对并列值的处理不同。
• SUM、AVG 等聚合函数配合 OVER 可以计算分组总额、累计值和移动平均。
• LAG 与 LEAD 用于访问前后行；过滤窗口结果通常需要子查询或公共表表达式。