MySQL 8.0窗口函数排名统计与移动聚合查询优化

窗口函数语法框架与执行顺序

MySQL 8.0引入窗口函数大幅简化了排名、累计统计等复杂查询的编写。窗口函数语法为函数名() OVER (PARTITION BY ... ORDER BY ... frame_clause)，其执行在WHERE、GROUP BY、HAVING之后，在ORDER BY和LIMIT之前。理解执行顺序对编写正确查询至关重要——窗口函数只能引用SELECT列表中的表达式或输入列，不能引用WHERE过滤后的别名。

窗口函数与GROUP BY的核心区别：GROUP BY将每组聚合为单行，窗口函数为每行计算聚合值但保留原始行数。窗口函数不改变结果集行数，适合需要在明细数据上附加统计信息的场景。

排名函数对比与使用场景

MySQL 8.0提供三个排名函数，行为差异在于并列排名的处理：

SELECT
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank_val,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM students;

假设分数为95、90、90、85，三个函数的结果分别是：ROW_NUMBER输出1,2,3,4（不重复编号）；RANK输出1,2,2,4（跳过后续名次）；DENSE_RANK输出1,2,2,3（不跳过名次）。

实际应用中：ROW_NUMBER用于分页和数据去重（取每组第一条），RANK用于竞赛排名（并列名次后跳位），DENSE_RANK用于连续等级分配（如绩效考核等级）。分区排名示例——每个部门的薪资排名：

SELECT
dept_id,
name,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

窗口帧定义与移动聚合

窗口帧（Window Frame）控制聚合函数的计算范围，是移动统计的核心。语法为ROWS|RANGE BETWEEN start AND end：

SELECT
date,
amount,
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sum,
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_7day_sum,
AVG(amount) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_7day_avg
FROM daily_sales;

ROWS模式按物理行号计算范围，RANGE模式按逻辑值计算范围。RANGE更符合业务语义——RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW计算7天时间窗口内的聚合，自动跳过无数据的日期。ROWS模式严格取前6行，如果某天无数据会导致窗口偏移。

时间窗口移动统计的最佳实践：

-- 30天移动平均（RANGE模式，自动处理缺失日期）
AVG(price) OVER (
ORDER BY trade_date
RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW
) AS ma30

-- 月初至今累计
SUM(amount) OVER (
PARTITION BY YEAR(date), MONTH(date)
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS mtd_sum

窗口函数性能优化

窗口函数的性能开销主要来自排序和缓冲区维护。优化策略：

1. 索引匹配：窗口函数的ORDER BY和PARTITION BY列组合建立索引，避免filesort。对于PARTITION BY dept_id ORDER BY salary DESC，索引(dept_id, salary)可完全消除排序。

2. 减少重复排序：多个窗口函数共享相同PARTITION BY和ORDER BY时，MySQL合并为单次排序。将相同窗口定义的函数放在一起：

SELECT
SUM(amount) OVER w1 AS cumulative,
AVG(amount) OVER w1 AS moving_avg,
ROW_NUMBER() OVER w2 AS row_num
FROM sales
WINDOW w1 AS (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
w2 AS (PARTITION BY dept ORDER BY date)

3. 避免大帧缓存：UNBOUNDED PRECEDING帧要求缓存全部分区数据，内存消耗与分区大小成正比。对于大分区（如百万行的单分区），考虑缩小帧范围或拆分分区。

4. EXPLAIN分析：检查执行计划中是否出现Using filesort标记，出现则说明窗口函数的排序未走索引，需要补充索引或调整查询。

常见业务场景SQL模板

场景1：Top N per Group——取每个分组的前N条记录：

SELECT * FROM (
SELECT
dept_id,
name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) t WHERE rn <= 3;

场景2：同比增长率——对比同期数据，使用LAG函数取前N期值：

SELECT
month,
revenue,
LAG(revenue, 12) OVER (ORDER BY month) AS prev_year_revenue,
ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month))
/ LAG(revenue, 12) OVER (ORDER BY month) * 100, 2) AS yoy_growth
FROM monthly_revenue;

场景3：用户留存分析——计算N日留存率，结合CTE和窗口函数：

WITH first_login AS (
SELECT
user_id,
MIN(login_date) AS first_date
FROM user_logins
GROUP BY user_id
),
retention AS (
SELECT
f.first_date,
COUNT(DISTINCT l.user_id) AS retained_users
FROM first_login f
JOIN user_logins l ON f.user_id = l.user_id
AND l.login_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
GROUP BY f.first_date
)
SELECT
first_date,
retained_users,
ROUND(retained_users * 100.0 / (
SELECT COUNT(DISTINCT user_id) FROM first_login WHERE first_date = r.first_date
), 2) AS retention_rate_7d
FROM retention r;