MySQL 8.0窗口函数实战：从排名统计到时序分析

MySQL 8.0引入的窗口函数（Window Functions）是SQL查询能力的重要增强。相比传统GROUP BY聚合查询将多行压缩为一行，窗口函数能在保留原始行的同时计算聚合值，大幅简化排名、累计统计、环比分析等复杂查询的编写。掌握窗口函数的语法和典型应用场景，是提升数据库查询效率和开发效率的有效手段。

窗口函数语法结构：OVER子句与窗口定义

窗口函数的语法核心是OVER子句，它定义了函数的计算窗口范围：

函数名() OVER (
    [PARTITION BY 分区表达式]
    [ORDER BY 排序表达式 [ASC|DESC]]
    [frame_clause]  -- 窗口帧定义
)

PARTITION BY：将结果集按指定列分区，窗口函数在每个分区内独立计算。不指定PARTITION BY时，整个结果集作为一个分区。

ORDER BY：定义分区内行的排序方式。排序决定了累计计算的方向和ROW/RANGE帧的边界。

frame_clause：定义窗口帧的范围，即当前行参与计算的数据行范围。默认帧范围为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

MySQL 8.0支持的窗口函数分为三类：排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）、聚合窗口函数（SUM、AVG、COUNT、MAX、MIN）、值函数（LEAD、LAG、FIRST_VALUE、LAST_VALUE、NTH_VALUE）。

排名函数：ROW_NUMBER、RANK与DENSE_RANK的区别

三种排名函数在处理并列值时行为不同：

-- 示例数据：学生成绩表
-- | 学生 | 科目 | 分数 |
-- |------|------|------|
-- | 张三 | 数学 | 95   |
-- | 李四 | 数学 | 95   |
-- | 王五 | 数学 | 88   |
-- | 赵六 | 数学 | 82   |

SELECT
    学生, 科目, 分数,
    ROW_NUMBER() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS row_num,
    RANK()       OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS dense_rank_val
FROM scores;

-- 结果：
-- | 学生 | 科目 | 分数 | row_num | rank_val | dense_rank_val |
-- |------|------|------|---------|----------|----------------|
-- | 张三 | 数学 | 95   | 1       | 1        | 1              |
-- | 李四 | 数学 | 95   | 2       | 1        | 1              |
-- | 王五 | 数学 | 88   | 3       | 3        | 2              |
-- | 赵六 | 数学 | 82   | 4       | 4        | 3              |

ROW_NUMBER始终返回唯一序号，并列值随机排序；RANK并列值排名相同，后续跳过；DENSE_RANK并列值排名相同，后续不跳过。实际应用中，ROW_NUMBER用于分页和去重，RANK用于竞赛排名，DENSE_RANK用于连续排名场景。

累计统计与滑动窗口：SUM和AVG的窗口应用

窗口帧（Frame）控制累计计算的范围。ROWS模式按物理行数计算，RANGE模式按逻辑值范围计算：

-- 计算每日销售额及7天滑动平均值
SELECT
    order_date,
    daily_amount,
    -- 累计销售额
    SUM(daily_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount,
    -- 7天滑动平均
    AVG(daily_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7d,
    -- 月累计
    SUM(daily_amount) OVER (
        PARTITION BY DATE_FORMAT(order_date, '%Y-%m')
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS monthly_cumulative
FROM (
    SELECT DATE(created_at) AS order_date,
           SUM(amount) AS daily_amount
    FROM orders
    GROUP BY DATE(created_at)
) daily;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW表示当前行及前6行（共7行）参与计算。注意边界情况：前6天不足7行时，AVG仍然正确计算——分母是实际行数而非7。

RANGE模式的区别在于它按ORDER BY列的值范围而非物理行偏移计算：

-- RANGE模式：3天滑动窗口(按日期值而非行数)
AVG(daily_amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW
) AS avg_3d

RANGE模式适用于数据可能有日期缺失的场景——即使某天没有数据行，3天的值范围仍然正确。

时序分析：LAG和LEAD实现环比增长计算

LAG和LEAD分别获取前N行和后N行的值，是计算环比增长的核心工具：

-- 计算月度收入环比增长率
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / LAG(revenue, 1) OVER (ORDER BY month) * 100, 2
    ) AS growth_rate_pct
FROM monthly_revenue;

-- 计算每个用户的连续登录天数
SELECT
    user_id,
    login_date,
    login_date - INTERVAL LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) DAY AS days_gap
FROM user_login_log;

-- 筛选连续登录3天以上的用户
WITH login_gaps AS (
    SELECT
        user_id,
        login_date,
        DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) AS gap
    FROM user_login_log
),
login_groups AS (
    SELECT
        user_id,
        login_date,
        SUM(CASE WHEN gap > 1 OR gap IS NULL THEN 1 ELSE 0 END)
            OVER (PARTITION BY user_id ORDER BY login_date) AS group_id
    FROM login_gaps
)
SELECT user_id, COUNT(*) AS consecutive_days
FROM login_groups
GROUP BY user_id, group_id
HAVING COUNT(*) >= 3;

连续登录天数的计算利用了”断裂标记法”：当gap大于1时标记新分组，相同group_id内的行即为连续登录的日期序列。

窗口函数性能优化：索引利用与执行计划分析

窗口函数的执行计划中会出现Using filesort，表示需要排序操作。优化窗口函数查询的关键是让排序走索引：

1. 匹配PARTITION BY和ORDER BY的复合索引：当查询为PARTITION BY dept ORDER BY salary DESC时，创建索引(dept, salary DESC)，MySQL可以利用索引完成分区和排序，避免额外排序操作。

2. 减少分区数量：PARTITION BY的基数越高，排序和缓冲的开销越大。对于高基数的分区列（如user_id），考虑是否可以改写为应用层循环查询。

3. 避免在窗口函数中使用子查询：窗口函数的OVER子句不支持子查询，但可以在外层查询的CTE中预先计算中间结果，再对CTE应用窗口函数。

4. 监控Sort_merge_passes状态变量：当排序数据量超过sort_buffer_size时，MySQL使用磁盘临时文件完成排序，性能急剧下降。适当增大sort_buffer_size或优化查询减少排序数据量。

-- 检查排序溢出到磁盘的次数
SHOW STATUS LIKE 'Sort_merge_passes';

-- 临时增大sort_buffer_size(仅当前会话)
SET SESSION sort_buffer_size = 8 * 1024 * 1024;  -- 8MB

窗口函数是MySQL 8.0中实用价值最高的新特性之一。熟练掌握排名、累计、滑动窗口和时序分析的写法，能够将多条嵌套子查询简化为单条声明式SQL，显著提升查询编写效率和可读性。