SQL 窗口函数：分区、排序、帧与 Top N/累计/环比实战

更新时间：2026-09-18。窗口函数（Window Function）是 SQL 从「会聚合」走向「会做分析」的分水岭：它能在不折叠明细行的前提下，给每一行追加一个基于某个「窗口」算出来的值——组内排名、上一行的值、累计和、移动平均都靠它。MySQL 8.0、PostgreSQL 8.4+、SQLite 3.25+ 均支持。本文用一套订单表演示。前置：MySQL 查询优化、MySQL EXPLAIN 深挖。

本文要回答的问题

窗口函数和 GROUP BY 到底差在哪？为什么窗口函数能既保留明细又出聚合？
OVER (PARTITION BY ... ORDER BY ...) 三个部分各决定什么？
ROW_NUMBER/RANK/DENSE_RANK 并列时结果有何不同？
帧（FRAME）ROWS 与 RANGE、UNBOUNDED PRECEDING 怎么决定累计/移动窗口？为什么 LAST_VALUE 常常取不到最后一行？
分组取 Top N、累计求和、移动平均、环比同比怎么一把写对？

一、本质：在明细行上「开一扇窗」

sql

-- 订单表：用户、城市、金额、下单时间
CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  city VARCHAR(32),
  user_id BIGINT,
  amount DECIMAL(10,2),
  created_at DATETIME,
  INDEX idx_city_amt(city, amount)
);

GROUP BY 把同组合并成一行，明细丢失；窗口函数对每一行独立计算，但计算时只看属于这扇窗的那些行，原表多少行结果就多少行。

SQL 窗口函数：分区、排序、帧与 Top N/累计/环比实战示意图（第 2 张）

对比	`GROUP BY` 聚合	窗口函数
结果行数	每组 1 行	与明细相同
能否同时看明细与汇总	不能	能
可用位置	配合 HAVING	SELECT/ORDER BY，不能直接进 WHERE
典型用途	报表合计、去重计数	排名、环比、累计、去重取一

二、语法三要素与执行顺序

bash

函数() OVER (
  PARTITION BY 列        -- 窗口按什么分组（省略=整张表一个分区）
  ORDER BY 列            -- 分区内按什么排序（排名/累计必需）
  ROWS/RANGE BETWEEN ... -- 帧：在排序后的分区里再圈定参与计算的行
)

记住逻辑执行顺序：FROM / JOIN → WHERE → GROUP BY → HAVING → WINDOW(窗口) → SELECT → DISTINCT → ORDER BY → LIMIT。窗口函数在 WHERE 之后才计算，所以不能在 WHERE 里直接引用窗口列，需要套一层子查询或 CTE。

三、四族核心函数

sql

-- 排名族：并列时差异
SELECT city, user_id, amount,
  ROW_NUMBER() OVER w AS rn,        -- 1,2,3,4 永不并列（同序按不确定先后）
  RANK()       OVER w AS rk,        -- 1,2,2,4 并列后跳号
  DENSE_RANK() OVER w AS drk        -- 1,2,2,3 并列不跳号
FROM orders
WINDOW w AS (PARTITION BY city ORDER BY amount DESC);

sql

-- 取值族：上一行 / 下一行 / 窗口首值
SELECT user_id, created_at, amount,
  LAG(amount, 1)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amt,
  LEAD(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS next_amt,
  FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_amt
FROM orders;

聚合族 SUM/AVG/COUNT/MAX/MIN OVER(...) 与分布族 NTILE(n)（均分桶）、CUME_DIST()、PERCENT_RANK() 同理。

四、帧（FRAME）：累计、移动窗口都靠它

有 ORDER BY 时，默认帧是 RANGE UNBOUNDED PRECEDING TO CURRENT ROW（从分区头到当前行），所以累计和天然成立；没有 ORDER BY 时默认是整个分区。

sql

SELECT created_at, amount,
  -- 累计求和：从分区第一行累到当前行
  SUM(amount) OVER (ORDER BY created_at
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum,
  -- 3 期移动平均：前一行 + 当前 + 后一行
  AVG(amount) OVER (ORDER BY created_at
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ma3
FROM orders;

ROWS 按「物理行偏移」，RANGE 按「值的逻辑范围」（并列值会被一起纳入）；做移动平均用 ROWS，做「与当前值相同及之前全部」用 RANGE。

五、可直接复用的四类写法

sql

-- 1) 每个城市金额 Top 3 的订单（分组取 Top N）
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) rn
  FROM orders
) t WHERE rn <= 3;

-- 2) 每个用户只保留最新一单（去重取一，比 GROUP BY 自连接清爽）
SELECT id, user_id, amount, created_at FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
  FROM orders
) t WHERE rn = 1;

-- 3) 环比：本期与上一期差值/比例
SELECT ym, gmv,
  LAG(gmv) OVER (ORDER BY ym) AS prev_gmv,
  ROUND((gmv - LAG(gmv) OVER (ORDER BY ym))
        / LAG(gmv) OVER (ORDER BY ym) * 100, 2) AS mom_pct
FROM (SELECT DATE_FORMAT(created_at,'%Y-%m') ym, SUM(amount) gmv
      FROM orders GROUP BY DATE_FORMAT(created_at,'%Y-%m')) m;

-- 4) 累计占比
SELECT city, amount,
  SUM(amount) OVER (ORDER BY amount DESC) AS cum,
  SUM(amount) OVER () AS total,
  ROUND(SUM(amount) OVER (ORDER BY amount DESC)
        / SUM(amount) OVER () * 100, 2) AS cum_pct
FROM orders;

六、坑与对照

在 WHERE 里直接写窗口函数 → 报错。 因为窗口在 WHERE 后才算；要么子查询/CTE 包一层，要么把「只看每组第一名」改写为先聚合再 join。
ROW_NUMBER 与 RANK 选错。 需要稳定取一行用 ROW_NUMBER，但并列值时谁排第一不确定，最好在 ORDER BY 末尾补一个唯一列（如 ORDER BY amount DESC, id）保证结果确定；要保留并列用 RANK/DENSE_RANK。
LAST_VALUE 取不到窗口最后一行。 默认帧只到当前行，当前行之后不可见，必须显式 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
忘写 ORDER BY，累计就变全量。 有/无 ORDER BY 的默认帧不同，跨库迁移尤其要显式写帧。
大分区排序成本高。 窗口常伴随 PARTITION BY ... ORDER BY 的排序，与深分页一样吃排序开销，要有匹配的复合索引支撑，分布式库会按分区键 shuffle。

需求	用哪个
分组取最新/最高分一条	`ROW_NUMBER() ... =1`（加唯一列兜底）
排名并列不跳号	`DENSE_RANK`
与上一条/下一条比较	`LAG/LEAD`
累计求和	`SUM() OVER(ORDER BY ...)`（默认帧即累计）
移动平均	`AVG() OVER(... ROWS BETWEEN n PRECEDING AND CURRENT ROW)`

七、相关与延伸

分页列表的另一个性能大坑见深分页优化；执行计划与排序节点见 MySQL EXPLAIN 深挖、PostgreSQL EXPLAIN；逐行查询的反模式见什么是 N+1 查询；批量数据搬运见批量插入。

总结

SQL 窗口函数：用 OVER(PARTITION BY 分区 ORDER BY 排序帧) 在不折叠明细的前提下给每行追加排名/取值/聚合结果；执行顺序在 WHERE 之后所以只能放进子查询/CTE 过滤；排名记牢 ROW_NUMBER 不并列、RANK 跳号、DENSE_RANK 不跳号，累计依赖默认帧到当前行、移动平均显式写 ROWS 帧，LAST_VALUE 必须把帧扩到 UNBOUNDED FOLLOWING——Top N、去重取一、环比同比、累计占比四类写法可直接套用。

SQL 窗口函数：分区、排序、帧与 Top N/累计/环比实战 ​

本文要回答的问题 ​

一、本质：在明细行上「开一扇窗」 ​

二、语法三要素与执行顺序 ​

三、四族核心函数 ​

四、帧（FRAME）：累计、移动窗口都靠它 ​

五、可直接复用的四类写法 ​

六、坑与对照 ​

七、相关与延伸 ​

总结 ​