窗口函数（Window Function，又称分析函数）是 SQL:2003 引入、现代数据库（MySQL 8.0、PostgreSQL、Oracle、SQL Server、SQLite 3.25+）都支持的重要特性。与普通聚合函数不同，窗口函数不会把多行合并成一行，而是对每一行计算"以它为中心的一个窗口"内的值——常用于排名、同比环比、分组内累计等分析场景。
普通 GROUP BY 聚合会把多行合并成一行，丢失明细：
-- 只能得到每个定位一行，看不到每个英雄的明细
SELECT role_main, AVG(hp_max) FROM heros GROUP BY role_main;如果想在保留每行英雄明细的同时，显示"该定位的平均生命值"，普通聚合做不到，需要窗口函数。
SELECT name, hp_max, role_main,
AVG(hp_max) OVER (PARTITION BY role_main) AS role_avg_hp
FROM heros;结果中每行都保留了英雄明细，同时多出一列"所属定位的平均生命值"。
窗口函数() OVER (
[PARTITION BY 分组列] -- 按哪些列分组（窗口分区）
[ORDER BY 排序列] -- 分区内如何排序
[ROWS/RANGE BETWEEN 窗口边界] -- 定义窗口范围（可选）
)| 子句 | 作用 | 类比 |
|---|---|---|
PARTITION BY | 把数据分成多个独立窗口 | 类似 GROUP BY 但保留所有行 |
ORDER BY | 定义窗口内排序 | 决定排名/累计顺序 |
| 窗口边界（FRAME） | 限定窗口大小 | 省略时：有 ORDER BY 则默认帧为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（排序值并列的行一并计入）；无 ORDER BY 则为整个分区 |
-- 每个英雄，加上"该定位平均生命值"和"该定位内生命排名"
SELECT
name, hp_max, role_main,
AVG(hp_max) OVER (PARTITION BY role_main) AS role_avg_hp,
RANK() OVER (PARTITION BY role_main ORDER BY hp_max DESC) AS role_rank
FROM heros;| 类别 | 函数 | 用途 |
|---|---|---|
| 聚合窗口 | SUM AVG COUNT MAX MIN | 窗口内聚合，不合并行 |
| 排名窗口 | ROW_NUMBER RANK DENSE_RANK NTILE | 序号/排名/分桶 |
| 取值窗口 | LAG LEAD FIRST_VALUE LAST_VALUE | 前后行/首尾取值（同比环比） |
SELECT
name, hp_max,
ROW_NUMBER() OVER (ORDER BY hp_max DESC) AS rn, -- 1,2,3,4,5 连续，同值也排不同
RANK() OVER (ORDER BY hp_max DESC) AS rk, -- 1,1,3,4,5 有跳跃
DENSE_RANK() OVER (ORDER BY hp_max DESC) AS drk -- 1,1,2,3,4 不跳跃
FROM heros LIMIT 6;| 函数 | 相同值名次 | 名次是否跳跃 | 用途 |
|---|---|---|---|
ROW_NUMBER | 不并列（按序给号） | 无 | 精确行号、取每组第 N 条 |
RANK | 并列 | 跳跃（1,1,3） | 传统排名（同名次占位） |
DENSE_RANK | 并列 | 不跳跃（1,1,2） | 名次紧凑（如奖金档位） |
-- 每个定位内，按生命值排名
SELECT name, role_main, hp_max,
RANK() OVER (PARTITION BY role_main ORDER BY hp_max DESC) AS rank_in_role
FROM heros;-- 按生命值从高到低分成 4 档（四分位）
SELECT name, hp_max,
NTILE(4) OVER (ORDER BY hp_max DESC) AS quartile
FROM heros;-- 按上线日期排序，累计英雄数
SELECT
YEAR(birthdate) AS year,
COUNT(*) AS hero_count,
SUM(COUNT(*)) OVER (ORDER BY YEAR(birthdate)) AS running_total
FROM heros
WHERE birthdate IS NOT NULL
GROUP BY YEAR(birthdate);-- 每个英雄与其定位平均生命对比
SELECT name, hp_max, role_main,
AVG(hp_max) OVER (PARTITION BY role_main) AS role_avg,
hp_max - AVG(hp_max) OVER (PARTITION BY role_main) AS diff
FROM heros;带 ORDER BY 时，默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（注意是 RANGE 而不是 ROWS：排序值并列的行会一起计入窗口）。用 ROWS BETWEEN 可以按物理行精确限定滑动窗口（如移动平均）：
-- 按上线日期排序，取当前行及前 2 行的平均（3 期移动平均）
SELECT
YEAR(birthdate) AS year,
COUNT(*) AS cnt,
AVG(COUNT(*)) OVER (
ORDER BY YEAR(birthdate)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM heros
WHERE birthdate IS NOT NULL
GROUP BY YEAR(birthdate);| 窗口边界 | 含义 |
|---|---|
（省略帧，默认）RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | 从分区开头到当前行（按 ORDER BY 值计），含并列行（累计） |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | 从分区开头到当前行（按物理行计，不含并列） |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | 当前行及前 2 行 |
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING | 当前行到分区结尾 |
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING | 前后各 1 行 |
-- 按上线年份，看英雄数相对上一年（同比）
SELECT
YEAR(birthdate) AS year,
COUNT(*) AS cnt,
LAG(COUNT(*)) OVER (ORDER BY YEAR(birthdate)) AS prev_cnt, -- 上一行
LEAD(COUNT(*)) OVER (ORDER BY YEAR(birthdate)) AS next_cnt -- 下一行
FROM heros
WHERE birthdate IS NOT NULL
GROUP BY YEAR(birthdate);LAG(expr, offset, default)：offset 默认 1（前 1 行），default 无值时的默认值：
-- 每个英雄相对上一个上线英雄的生命差
SELECT name, hp_max,
hp_max - LAG(hp_max, 1, 0) OVER (ORDER BY birthdate) AS hp_diff
FROM heros
WHERE birthdate IS NOT NULL;-- 每个定位内，生命最高/最低的英雄名
SELECT name, role_main, hp_max,
FIRST_VALUE(name) OVER (PARTITION BY role_main ORDER BY hp_max DESC) AS top_hero,
LAST_VALUE(name) OVER (PARTITION BY role_main ORDER BY hp_max DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_hero
FROM heros;LAST_VALUE 陷阱：默认帧是
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（到当前行为止，含并列行），所以LAST_VALUE不指定ROWS BETWEEN ... UNBOUNDED FOLLOWING时只能取到当前行，得不到分区最后一行。建议用FIRST_VALUE + ORDER BY 反转或明确帧边界。
| 特性 | 聚合函数（GROUP BY） | 窗口函数 |
|---|---|---|
| 是否合并行 | 是（多行并一行） | 否（保留所有行） |
| 与明细同查 | 需子查询 | 直接同行输出 |
| 支持排名/前后行 | ❌ | ✅ |
| WHERE 过滤 | 在 WHERE 中 | 不能在 WHERE，需子查询/CTE 包装 |
| 典型场景 | 汇总报表 | 排名、占比、同比环比、移动平均 |
WHERE、GROUP BY、HAVING 之后、ORDER BY 之前执行。因此不能在 WHERE 中直接过滤窗口函数结果，需要外层包一层：-- ❌ 错误：WHERE 不能引用窗口函数
SELECT name, hp_max,
RANK() OVER (ORDER BY hp_max DESC) AS rk
FROM heros
WHERE rk <= 3; -- 错误！
-- ✅ 正确：子查询包一层再过滤
SELECT * FROM (
SELECT name, hp_max,
RANK() OVER (ORDER BY hp_max DESC) AS rk
FROM heros
) t
WHERE t.rk <= 3;OVER，可复用但需重复：SELECT name, hp_max,
RANK() OVER (ORDER BY hp_max DESC) AS rk,
DENSE_RANK() OVER (ORDER BY hp_max DESC) AS drk
FROM heros;-- 每个定位生命值前 2 名的英雄
SELECT * FROM (
SELECT name, role_main, hp_max,
ROW_NUMBER() OVER (PARTITION BY role_main ORDER BY hp_max DESC) AS rn
FROM heros
) t
WHERE t.rn <= 2;-- 每个用户取 id 最小的一条（去重）
DELETE FROM user_logs WHERE id NOT IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM user_logs
) t WHERE t.rn = 1
);-- 每个定位英雄数占总数比例
SELECT
role_main,
COUNT(*) AS cnt,
COUNT(*) / SUM(COUNT(*)) OVER () AS pct
FROM heros
GROUP BY role_main;| 函数 | 作用 |
|---|---|
ROW_NUMBER() | 连续行号（不并列） |
RANK() | 排名（并列、跳跃） |
DENSE_RANK() | 排名（并列、不跳跃） |
NTILE(n) | 分 n 桶 |
SUM/AVG/COUNT/MAX/MIN() | 窗口内聚合 |
LAG(col, n, default) | 取前 n 行值 |
LEAD(col, n, default) | 取后 n 行值 |
FIRST_VALUE/LAST_VALUE() | 取窗口首/尾值 |
OVER (PARTITION BY ... ORDER BY ...)；ROW_NUMBER/RANK/DENSE_RANK 的并列与跳跃语义不同，按需选择；LAG/LEAD 用于同比环比、前后行对比，LAST_VALUE 需注意窗口帧边界；