MySQL 8.0窗口函数实战：分组排名与同环比统计查询优化

MySQL 8.0窗口函数把一批过去必须靠自连接、子查询或应用层处理的统计需求拉回了SQL本身。分组排名、移动平均、同环比、TopN，用窗口函数一条语句即可完成，执行计划也比老写法干净得多。本文覆盖窗口函数在报表统计中的高频场景，附可直接运行的SQL与性能对比，适用MySQL 8.0及以上版本。

MySQL窗口函数基础语法：OVER子句与分区规则

窗口函数语法为”函数名() OVER (PARTITION BY … ORDER BY … frame)”。PARTITION BY定义分组边界，ORDER BY定义组内排序，框架子句定义计算范围。示例表为订单表orders(amount金额, created_at下单时间, user_id用户)：

-- 每个用户的订单按金额排名
SELECT user_id, amount,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rk,
       DENSE_RANK() OVER w AS drk
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY amount DESC);

三个排名函数的区别：ROW_NUMBER严格递增不重复；RANK遇到并列跳号（1,1,3）；DENSE_RANK并列不跳号（1,1,2）。TOPN需求用ROW_NUMBER配合派生表过滤，比老写法的自连接少一次全表扫描。

分组排名实战：每类目Top3商品统计

SELECT category, product_name, sales
FROM (
    SELECT category, product_name, sales,
           ROW_NUMBER() OVER (PARTITION BY category
                              ORDER BY sales DESC) AS rn
    FROM product_sales
    WHERE stat_date = '2026-09-15'
) t
WHERE rn <= 3;

这条SQL只扫一遍表。老写法需要按类目分别取Top3再UNION ALL，类目多时SQL膨胀且优化器难以利用索引。同场景的变体：组内占比用SUM窗口累计，组内差值用LAG/LEAD算环比：

-- 日环比：当前天与上一天的差与增长率
SELECT stat_date, amount,
       LAG(amount) OVER (ORDER BY stat_date) AS prev_amount,
       ROUND((amount - LAG(amount) OVER (ORDER BY stat_date))
             / LAG(amount) OVER (ORDER BY stat_date) * 100, 2) AS growth_pct
FROM daily_amount;

移动平均与累计统计：框架子句实战

框架（frame）限定窗口的计算行范围，移动统计的标准写法：

-- 7日移动平均（含当日往前6天）
SELECT stat_date, amount,
       AVG(amount) OVER (ORDER BY stat_date
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
       SUM(amount) OVER (ORDER BY stat_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_total
FROM daily_amount;

ROWS按物理行定位，RANGE按逻辑值定位。去重累计（如累计用户数）必须用RANGE，ROWS会把重复日期的行重复计入：

-- 按日期累计去重用户数
SELECT stat_date, uid,
       COUNT(DISTINCT uid) OVER (ORDER BY stat_date
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_users
FROM user_login;

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

窗口函数的性能瓶颈在排序。EXPLAIN输出中的WINDOW步骤显示分区与排序方式，Using filesort出现时优先建复合索引，顺序必须与PARTITION BY + ORDER BY完全一致：

ALTER TABLE product_sales
    ADD INDEX idx_cat_date_sales (category, stat_date, sales);

三个优化要点：一是分区列与排序列建联合索引后，窗口计算可走索引顺序，避免临时文件排序；二是大分区内（单分区超过百万行）的累计统计会退化为全分区扫描，可按时间预分表或改用汇总表；三是FILTER条件尽量放在窗口计算前的派生表里，外层WHERE会导致全量计算后再过滤，代价翻倍。同环比的年对齐用LEAD带参数跨行取值：

-- 月同比：LAG按月序取12个月前
SELECT stat_month, amount,
       LAG(amount, 12) OVER (ORDER BY stat_month) AS yoy_prev,
       amount - LAG(amount, 12) OVER (ORDER BY stat_month) AS yoy_diff
FROM monthly_amount;

窗口函数不能直接出现在WHERE里（先计算后过滤需要派生表包一层），GROUP BY与窗口函数混用时，窗口基于分组后数据计算。报表系统建议把这些统计逻辑固化为视图或存储过程，配合定时汇总表减少实时计算压力。