窗口函数

窗口函数（Window Function / OLAP 函数）是 SQL:2003 引入、MySQL 8.0 才支持的特性。它与聚合函数最大的区别：

聚合函数把多行"压缩"成一行，窗口函数为每一行计算一个基于"一组相关行"的值，行数不变。

一、语法结构

函数名([参数]) OVER (
    [PARTITION BY 分区列]     -- 怎么分组，省略则整个结果集为一个分区
    [ORDER BY 排序列]          -- 分区内怎么排序
    [frame 子句]               -- 当前行的计算范围（窗口帧）
)

窗口可以命名后复用，多个窗口函数共用同一个窗口时强烈建议这样写：

SELECT
    name,
    RANK()       OVER w AS rk,
    SUM(salary)  OVER w AS running_total
FROM employee
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

求值时机

窗口函数在 HAVING 之后、ORDER BY 之前求值，因此：

不能出现在 WHERE / GROUP BY / HAVING 中；要按窗口函数结果过滤，必须外套一层子查询或 CTE；
可以出现在 SELECT 和 ORDER BY 中；
窗口函数看到的是已经聚合之后的行，所以 SUM(SUM(x)) OVER (...) 这种嵌套是合法且常用的（先分组求和，再对各组的和做累计）。

二、函数分类

排名类

函数	说明	1,2,2,4 还是 1,2,2,3
`ROW_NUMBER()`	行号，不管值是否相同，严格递增	1,2,3,4
`RANK()`	并列同名次，跳号	1,2,2,4
`DENSE_RANK()`	并列同名次，不跳号	1,2,2,3
`NTILE(n)`	把分区尽量均分成 n 桶，返回桶号	用于分位数
`PERCENT_RANK()`	`(rank - 1) / (总行数 - 1)`，取值 [0, 1]	百分比排名
`CUME_DIST()`	累计分布：≤ 当前值的行数占比	分位数分析

选哪个

去重取一条（如"每个用户最新一单"）→ ROW_NUMBER()
"工资前三高的所有员工"（并列都要） → DENSE_RANK()
竞赛式名次（并列后跳号） → RANK()

取值类

函数	说明
`LAG(expr, n, default)`	分区内前 n 行的值，越界返回 `default`（缺省为 `NULL`）
`LEAD(expr, n, default)`	分区内后 n 行的值
`FIRST_VALUE(expr)`	窗口帧内第一行的值
`LAST_VALUE(expr)`	窗口帧内最后一行的值 ⚠️ 见下方 frame 陷阱
`NTH_VALUE(expr, n)`	窗口帧内第 n 行的值

LAG / LEAD 是做"环比 / 同比 / 与上一行比较"的标准工具：

SELECT
    day, pv,
    LAG(pv) OVER (ORDER BY day)                    AS prev_pv,
    pv - LAG(pv) OVER (ORDER BY day)               AS diff,
    ROUND((pv / LAG(pv) OVER (ORDER BY day) - 1) * 100, 2) AS growth_pct
FROM daily_stat;

聚合类

SUM / AVG / COUNT / MAX / MIN 都可以加 OVER() 变成窗口函数：

SELECT
    name, department_id, salary,
    SUM(salary) OVER (PARTITION BY department_id)                    AS dept_total,
    salary / SUM(salary) OVER (PARTITION BY department_id)           AS ratio,
    AVG(salary) OVER (PARTITION BY department_id)                    AS dept_avg,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY hired_at)  AS running_total
FROM employee;

注意最后两行的差别：加了 ORDER BY 的聚合窗口会变成"累计"，因为 frame 默认从分区开头到当前行。

三、窗口帧（frame）⭐⭐⭐

frame 定义"当前行参与计算的范围"，是最容易出错的地方。

{ROWS | RANGE} BETWEEN 起点 AND 终点

边界可以是：UNBOUNDED PRECEDING（分区第一行）、n PRECEDING、CURRENT ROW、n FOLLOWING、UNBOUNDED FOLLOWING（分区最后一行）。

默认值（必须记住）：

是否写了 `ORDER BY`	默认 frame
没写	`RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`（整个分区）
写了	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`（分区首行到当前行）

ROWS 与 RANGE 的区别：

ROWS 按物理行数偏移，"前 2 行"就是前 2 行；
RANGE 按值的范围，排序值相同的行（peer）会被整体纳入。

-- 数据：salary = 100, 200, 200, 300
SUM(salary) OVER (ORDER BY salary ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- → 100, 300, 500, 800     每行各算各的
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- → 100, 500, 500, 800     两个 200 是 peer，累计值相同

LAST_VALUE 的经典陷阱

-- ❌ 结果永远等于当前行，因为默认 frame 到 CURRENT ROW 为止
SELECT LAST_VALUE(salary) OVER (PARTITION BY d ORDER BY salary) FROM employee;
-- ✅ 必须显式把 frame 拉到分区末尾
SELECT LAST_VALUE(salary) OVER (
    PARTITION BY d ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) FROM employee;

FIRST_VALUE 没有这个问题（默认 frame 起点就是分区首行），但为了不踩坑，凡是用 FIRST_VALUE / LAST_VALUE / NTH_VALUE 都建议显式写 frame。

移动窗口（滑动平均）：

-- 7 日移动平均（含当天在内的最近 7 天）
SELECT day, pv,
       AVG(pv) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_stat;

ROWS 6 PRECEDING ≠ 最近 7 天

ROWS 数的是行，如果某天没有数据（缺行），就会把更早的日期算进来。要严格按"日期"取范围，必须用 RANGE + INTERVAL：

AVG(pv) OVER (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)

RANGE 带数值/时间间隔偏移时，ORDER BY 只能有一列，且必须是数值或时间类型。

四、常见套路模板

1. 分组内 Top N

SELECT * FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rk
    FROM employee
) t WHERE rk <= 3;

2. 分组内取最新一条（去重）

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) rn
    FROM orders
) t WHERE rn = 1;

排序键加上 id 兜底，避免 created_at 相同时结果不稳定。

3. 占比与累计占比（帕累托分析）

SELECT product, sales,
       sales / SUM(sales) OVER ()                                    AS pct,
       SUM(sales) OVER (ORDER BY sales DESC) / SUM(sales) OVER ()    AS cum_pct
FROM product_sales;

4. 连续区间（gaps and islands）⭐

核心技巧：等差序列相减得常量。 连续的 id 减去连续的行号，同一段内差值恒定：

WITH t AS (
    SELECT id, login_date,
           login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY login_date) DAY AS grp
    FROM login_log
)
SELECT id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days
FROM t GROUP BY id, grp
HAVING COUNT(*) >= 3;      -- 连续登录 3 天以上

5. 与分组聚合值比较

-- 薪水高于本部门平均的员工：不需要相关子查询
SELECT * FROM (
    SELECT e.*, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg FROM employee e
) t WHERE salary > dept_avg;

6. 中位数

-- 奇数行取中间一行，偶数行取中间两行的平均
SELECT AVG(salary) AS median FROM (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS rn,
           COUNT(*)    OVER ()                 AS cnt
    FROM employee
) t
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2));

COUNT(*) OVER () 拿到总行数，无需再扫一遍表。奇数时两个 FLOOR 结果相同（IN 自动去重），偶数时正好取到中间两行。

五、性能注意事项

窗口函数几乎总是需要排序。如果 PARTITION BY a ORDER BY b 恰好命中索引 (a, b)，MySQL 可以省掉排序；否则会走 filesort，数据量大时溢出磁盘。
一条 SQL 里多个窗口函数，只要窗口定义相同就共用一次排序。所以尽量用 WINDOW w AS (...) 统一窗口定义，而不是写出 3 个略有差别的 OVER (...)。
窗口函数不会减少扫描的行数——它作用于 WHERE 之后的结果集。先用 WHERE 把数据缩小，再开窗。
MySQL 8.0 对窗口函数不做谓词下推，WHERE rn = 1 这类外层过滤无法减少内层计算量。数据量极大时，仍然要靠索引把候选集限制住。

实战

电影评分

👉 Leetcode 链接-1341在新窗口打开

综合题：求评论电影数最多的用户名（并列取字典序最小），以及 2020 年 2 月平均评分最高的电影名（并列取字典序最小）。

(select u.name as results
 from MovieRating mr join Users u on mr.user_id = u.user_id
 group by mr.user_id
 order by count(*) desc, u.name asc
 limit 1)
union all
(select m.title as results
 from MovieRating mr join Movies m on mr.movie_id = m.movie_id
 where mr.created_at >= '2020-02-01' and mr.created_at < '2020-03-01'
 group by mr.movie_id
 order by avg(mr.rating) desc, m.title asc
 limit 1)

考点

UNION ALL 每一支带 ORDER BY ... LIMIT 时必须用括号包住，否则 ORDER BY 会被解析为作用于整体；
日期过滤用半开区间而不是 DATE_FORMAT(created_at, '%Y-%m') = '2020-02'，前者能走索引。

排名分数

👉 Leetcode 链接-178在新窗口打开

分数相同名次相同，且名次连续不跳号 —— DENSE_RANK() 的定义式题目。

select
    score,
    dense_rank() over (order by score desc) as 'rank'
from
    Scores

rank 在 MySQL 8.0 中是保留字，作为列别名必须加反引号或引号。

平均售价

👉 Leetcode 链接-1251在新窗口打开

价格表按生效区间存储，销量表按天记录，求每个产品的加权平均售价（保留 2 位小数）。

考点

区间连接：连接条件是 BETWEEN 而不是等值。另外要用 LEFT JOIN + IFNULL 处理"有价格但没有销量"的产品（否则这些产品会丢失）。

select
    p.product_id,
    ifnull(round(sum(u.units * p.price) / sum(u.units), 2), 0) as average_price
from
    Prices p
left join
    UnitsSold u
on
    p.product_id = u.product_id
    and u.purchase_date between p.start_date and p.end_date
group by
    p.product_id

产品销售分析 III

👉 Leetcode 链接-1070在新窗口打开

对每个产品，找出它首次销售年份的所有销售记录（product_id、first_year、quantity、price）。

select product_id, year as first_year, quantity, price
from (
    select *, rank() over (partition by product_id order by year) as rk
    from Sales
) t
where rk = 1

用 RANK() 而不是 ROW_NUMBER()：同一产品在首年可能有多条销售记录，都要保留。

连续签到天数（模板题）

给定 login(user_id, login_date)，求每个用户的最长连续登录天数：

with dedup as (
    select distinct user_id, login_date from login
),
grouped as (
    select user_id, login_date,
           date_sub(login_date,
                    interval row_number() over (partition by user_id order by login_date) day
           ) as grp
    from dedup
)
select user_id, max(cnt) as max_streak
from (
    select user_id, grp, count(*) as cnt from grouped group by user_id, grp
) t
group by user_id

第一步 DISTINCT 去掉同一天多次登录，否则行号会错位——这是这类题最常见的错误。

下一步 👉 索引原理

# 窗口函数

# 一、语法结构

# 二、函数分类

# 排名类

# 取值类

# 聚合类

# 三、窗口帧（frame）⭐⭐⭐

# 四、常见套路模板

# 1. 分组内 Top N

# 2. 分组内取最新一条（去重）

# 3. 占比与累计占比（帕累托分析）

# 4. 连续区间（gaps and islands）⭐

# 5. 与分组聚合值比较

# 6. 中位数

# 五、性能注意事项

# 实战

# 电影评分

# 排名分数

# 平均售价

# 产品销售分析 III

# 连续签到天数（模板题）