MySQL 8.0窗口函数实战：复杂统计查询从子查询嵌套到单语句优雅实现的性能飞跃

窗口函数解决的查询痛点

MySQL 8.0引入窗口函数之前，分组排名、累计求和、同比环比计算都需要多层子查询嵌套或用户变量hack。典型场景：查询每个部门薪资前三名的员工，传统写法需要3层子查询+JOIN，窗口函数一条SQL搞定，执行计划从全表扫描3次优化为1次扫描+窗口计算。

窗口函数语法：函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列 frame子句)。窗口函数不会减少结果行数——每行数据都会保留，只是在每行上附加计算结果。这是窗口函数和GROUP BY的本质区别。

排名函数：ROW_NUMBER/RANK/DENSE_RANK的选择

三个排名函数处理并列值的方式不同，选错会导致业务逻辑错误：

-- 部门薪资排名（同名次处理对比）
SELECT 
    name, dept, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rnk
FROM employees;

-- 同部门两人薪资相同时：
-- ROW_NUMBER: 1, 2, 3 (严格递增，相同值随机排)
-- RANK:       1, 1, 3 (并列占位，跳过序号)
-- DENSE_RANK: 1, 1, 2 (并列不占位，连续编号)

-- 实战：每个部门Top3员工（允许并列）
SELECT * FROM (
    SELECT *, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
    FROM employees
) ranked WHERE rnk <= 3;

业务选型原则：分页场景用ROW_NUMBER（严格每页N条），竞赛排名用RANK（冠军并列时亚军从第3开始），等级划分用DENSE_RANK（A等/B等连续编号）。

累计计算与移动平均

frame子句控制窗口的计算范围，是窗口函数最灵活也最容易出错的部分：

-- 累计求和：从年初到当前行的累计销售额
SELECT 
    month, revenue,
    SUM(revenue) OVER (
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM monthly_sales;

-- 移动平均：最近3个月的平均销售额
SELECT 
    month, revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_sales;

-- 同比增长率：今年vs去年同期
SELECT 
    m.month, m.revenue, m.revenue AS current_year,
    LAG(m.revenue, 12) OVER (ORDER BY m.month) AS last_year,
    ROUND((m.revenue - LAG(m.revenue, 12) OVER (ORDER BY m.month)) 
        / LAG(m.revenue, 12) OVER (ORDER BY m.month) * 100, 2) AS yoy_growth
FROM monthly_sales m;

ROWS和RANGE的区别：ROWS按物理行偏移计算窗口，RANGE按逻辑值计算。对于月度数据，用ROWS BETWEEN 2 PRECEDING精确取前2行。但如果月份有缺失（某月没数据），ROWS会取到非连续月份，RANGE能按值范围计算。实际业务中90%场景用ROWS更可控。

LEAD/LAG：前后行对比的利器

LEAD和LAG用于引用窗口中前后的行值，是环比、连续登录天数、库存变化等场景的核心工具：

-- 用户连续登录天数统计
WITH daily_login AS (
    SELECT DISTINCT user_id, DATE(login_time) AS login_date
    FROM user_logs
    WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
),
grouped AS (
    SELECT 
        user_id, login_date,
        login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS grp
    FROM daily_login
)
SELECT 
    user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    DATEDIFF(MAX(login_date), MIN(login_date)) + 1 AS streak_days
FROM grouped
GROUP BY user_id, grp
HAVING streak_days >= 3
ORDER BY streak_days DESC;

-- 库存异常检测：单日库存变动超过阈值
SELECT 
    product_id, stock_date, stock_qty,
    stock_qty - LAG(stock_qty) OVER (PARTITION BY product_id ORDER BY stock_date) AS daily_change,
    CASE 
        WHEN ABS(stock_qty - LAG(stock_qty) OVER (PARTITION BY product_id ORDER BY stock_date)) > 1000
        THEN 'ABNORMAL'
        ELSE 'NORMAL'
    END AS alert
FROM inventory_daily;

窗口函数性能优化

窗口函数的执行计划中有Windowing aggregate步骤，性能取决于分区数和排序方式：

-- 查看窗口函数执行计划
EXPLAIN ANALYZE
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees;

-- 优化1：确保PARTITION BY和ORDER BY列有索引
CREATE INDEX idx_dept_salary ON employees(dept, salary DESC);

-- 优化2：减少窗口计算次数（多个窗口函数共享排序时合并）
-- 低效：两个窗口函数分别排序
SELECT 
    SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date),
    AVG(salary) OVER (PARTITION BY dept ORDER BY hire_date)
FROM employees;

-- 优化：显式定义WINDOW子句共享排序
SELECT 
    SUM(salary) OVER w AS dept_cumsum,
    AVG(salary) OVER w AS dept_cumavg
FROM employees
WINDOW w AS (PARTITION BY dept ORDER BY hire_date ROWS UNBOUNDED PRECEDING);

WINDOW子句让多个窗口函数共享同一个排序和分区，MySQL优化器只需要做一次排序计算。在10万行以上数据表中，这个优化可以将窗口函数执行时间减少40%-60%。

窗口函数替代存储过程的场景清单

窗口函数可以替代许多需要存储过程或应用层代码的统计计算：

分组Top-N：ROW_NUMBER + WHERE替代GROUP_CONCAT+应用层解析

累计指标：SUM OVER替代逐行UPDATE

环比/同比：LAG替代自连接

连续区间识别：日期-ROW_NUMBER差值分组替代游标遍历

中位数：PERCENT_RANK替代应用层排序取中间值

把这些统计逻辑从应用层下推到数据库层执行，减少数据传输量，利用数据库的排序和聚合优化器，在百万行级数据上性能优势显著。