窗口函数

窗口函数（Window Function，又称分析函数）是 SQL:2003 引入、现代数据库（MySQL 8.0、PostgreSQL、Oracle、SQL Server、SQLite 3.25+）都支持的重要特性。与普通聚合函数不同，窗口函数不会把多行合并成一行，而是对每一行计算"以它为中心的一个窗口"内的值——常用于排名、同比环比、分组内累计等分析场景。

为什么需要窗口函数

聚合函数的局限

普通 GROUP BY 聚合会把多行合并成一行，丢失明细：

sql

-- 只能得到每个定位一行，看不到每个英雄的明细
SELECT role_main, AVG(hp_max) FROM heros GROUP BY role_main;

如果想在保留每行英雄明细的同时，显示"该定位的平均生命值"，普通聚合做不到，需要窗口函数。

窗口函数登场

sql

SELECT name, hp_max, role_main,
       AVG(hp_max) OVER (PARTITION BY role_main) AS role_avg_hp
FROM heros;

结果中每行都保留了英雄明细，同时多出一列"所属定位的平均生命值"。

窗口函数基本语法

sql

窗口函数() OVER (
    [PARTITION BY 分组列]      -- 按哪些列分组（窗口分区）
    [ORDER BY 排序列]          -- 分区内如何排序
    [ROWS/RANGE BETWEEN 窗口边界]  -- 定义窗口范围（可选）
)

三个关键部分

子句	作用	类比
`PARTITION BY`	把数据分成多个独立窗口	类似 GROUP BY 但保留所有行
`ORDER BY`	定义窗口内排序	决定排名/累计顺序
窗口边界（FRAME）	限定窗口大小	默认到"当前分区所有行"

完整示例

sql

-- 每个英雄，加上"该定位平均生命值"和"该定位内生命排名"
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`	前后行/首尾取值（同比环比）

排名窗口函数

ROW_NUMBER / RANK / DENSE_RANK 对比

sql

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）	名次紧凑（如奖金档位）

分组内排名

sql

-- 每个定位内，按生命值排名
SELECT name, role_main, hp_max,
       RANK() OVER (PARTITION BY role_main ORDER BY hp_max DESC) AS rank_in_role
FROM heros;

NTILE：分桶

sql

-- 按生命值从高到低分成 4 档（四分位）
SELECT name, hp_max,
       NTILE(4) OVER (ORDER BY hp_max DESC) AS quartile
FROM heros;

聚合窗口函数

累计值（Running Total）

sql

-- 按上线日期排序，累计英雄数
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);

窗口内移动平均

sql

-- 每个英雄与其定位平均生命对比
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;

窗口帧（FRAME）控制

默认窗口是"分区内所有行"。用 ROWS BETWEEN 限定滑动窗口（如移动平均）：

sql

-- 按上线日期排序，取当前行及前 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);

窗口边界	含义
`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 行

取值窗口函数

LAG / LEAD：访问前后行

sql

-- 按上线年份，看英雄数相对上一年（同比）
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 无值时的默认值：

sql

-- 每个英雄相对上一个上线英雄的生命差
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;

FIRST_VALUE / LAST_VALUE

sql

-- 每个定位内，生命最高/最低的英雄名
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 陷阱：默认帧是"到当前行为止"，所以 LAST_VALUE 不指定 ROWS BETWEEN ... UNBOUNDED FOLLOWING 时只能取到当前行，得不到分区最后一行。建议用 FIRST_VALUE + ORDER BY 反转 或明确帧边界。

窗口函数与聚合函数对比

特性	聚合函数（GROUP BY）	窗口函数
是否合并行	是（多行并一行）	否（保留所有行）
与明细同查	需子查询	直接同行输出
支持排名/前后行	❌	✅
WHERE 过滤	在 WHERE 中	不能在 WHERE，需子查询/CTE 包装
典型场景	汇总报表	排名、占比、同比环比、移动平均

窗口函数使用注意事项

执行顺序：窗口函数在 WHERE、GROUP BY、HAVING 之后、ORDER BY 之前执行。因此不能在 WHERE 中直接过滤窗口函数结果，需要外层包一层：

sql

-- ❌ 错误：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：多个窗口函数各自写 OVER，可复用但需重复：

sql

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;

MySQL 8.0 起才支持窗口函数（5.7 不支持，只能用临时变量或子查询模拟）；
性能：窗口函数常需排序，分区+排序较大时注意索引与执行计划。

典型应用场景

场景 1：取每组前 N 名（Top-N）

sql

-- 每个定位生命值前 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;

场景 2：去重（保留每组最早一条）

sql

-- 每个用户取 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
);

场景 3：占比计算

sql

-- 每个定位英雄数占总数比例
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 需注意窗口帧边界；
窗口函数结果不能在 WHERE 中使用，需子查询/CTE 包装；
MySQL 8.0+ 支持，是数据分析与面试高频考点。