窗口函数是什么
窗口函数在保留明细行的同时做组内计算，写法是 函数名 OVER (PARTITION BY 分组列 ORDER BY 排序列 [帧])。它与 GROUP BY 的根本区别是输出行数不变：GROUP BY 把多行压成一行，窗口函数把计算结果附加到每一行上，因此能在明细旁边同时看到组内排名与组内总量。MySQL 8.0 与 PostgreSQL 都支持常用窗口函数，MySQL 8.0 之前完全没有这个能力。窗口函数只能出现在 SELECT 列表与 ORDER BY 中，不能写在 WHERE 里。
基本信息
| 语法 | 函数名(参数) OVER (PARTITION BY … ORDER BY … [ROWS 帧]) |
|---|---|
| 与 GROUP BY 区别 | GROUP BY 合并行，窗口函数保留明细行 |
| 可用位置 | SELECT 列表与 ORDER BY，不能用于 WHERE |
| PARTITION BY | 划分分组，省略时整个结果集视为一个分区 |
| ORDER BY | 组内排序，决定行号与累计值的计算顺序 |
| 版本要求 | MySQL 8.0+ 与 PostgreSQL 均支持，MySQL 5.7 不支持 |
典型使用场景
- 组内排名：每部门薪资前三、每个类目销量前十
- 累计计算：销售额按天累计、账户流水的滚动余额
- 同环比：用 LAG / LEAD 取上一期数据算增长率
- 去重取最新：ROW_NUMBER 编号后在外层取 rn = 1
- 占比计算：当前行的值除以分区合计
- 保留明细的分组统计：每行后面附带该组的总量与均值
基础示例
基础示例
SELECT dept_id, # 明细行保留
emp_name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk,
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;过滤与帧
过滤与帧
-- 窗口函数不能写在 WHERE 里，需要套一层子查询
SELECT * FROM (
SELECT id, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) AS t
WHERE t.rn <= 3; # 每部门薪资前三
-- 累计求和：默认帧是从分区开头到当前行
SELECT d, amount,
SUM(amount) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING) AS running
FROM sales;常用窗口函数
| 函数 | 作用 | 备注 |
|---|---|---|
| ROW_NUMBER() | 分区内连续唯一编号 | 并列值也按顺序编号，结果唯一 |
| RANK() | 带并列且跳号的排名 | 1,1,3 |
| DENSE_RANK() | 带并列不跳号 | 1,1,2 |
| LAG / LEAD | 取前一行或后一行 | 算环比、算间隔天数 |
| SUM / AVG OVER | 组内累计或合计 | 不带 ORDER BY 时是整组总量 |
| FIRST_VALUE / LAST_VALUE | 组内首值与末值 | 注意默认帧范围会截断结果 |
省略 PARTITION BY 时整个结果集是一个分区，SUM(...) OVER () 会给出全局合计，适合算占比：当前值 / SUM(值) OVER ()。
窗口函数在逻辑上晚于 WHERE 与 GROUP BY 求值，所以同一层里不能既用它又用结果过滤，必须把窗口计算放进子查询或 CTE 再筛。
LAST_VALUE 默认帧只到当前行，直接用它取组内最后一个值会得到当前行本身，需要显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
窗口函数在分区键与排序键上有合适索引时效率更好，否则会先做一次排序；数据量大时先用 EXPLAIN 看看排序与临时表的使用情况。
使用要点
- 窗口定义可复用：多数数据库支持在 WINDOW 子句里命名窗口，多个函数共享同一份定义
- 分区键与排序键上建索引，能减少一次额外的全量排序
- ROW_NUMBER 与 RANK 的选择取决于并列处理：要严格 N 行用 ROW_NUMBER，保留并列用 RANK
- 占比计算写明分母：是全局合计还是分区合计，用 SUM(...) OVER () 与 OVER (PARTITION BY …) 区分
- 与 GROUP BY 同层使用时要留意行粒度已经变化，先想清楚每行代表什么
- 记住求值时机：晚于 WHERE 与 GROUP BY，早于 ORDER BY 与 LIMIT
同一查询里给多个窗口函数写完全相同的 OVER 定义既啰嗦又易错，用命名窗口或在 CTE 里集中计算一次，后续改条件时只改一处。
聚合函数与窗口函数同名但语义不同：SUM(x) 会把多行合并成一行，SUM(x) OVER (…) 保留明细行。读 SQL 时先确认函数名后面有没有 OVER。
窗口函数在 MySQL 8.0 之前完全不可用，遇到 5.7 环境只能用自连接或会话变量模拟，改写前先确认目标数据库版本。