MySQL 8窗口函数实战：分组排序、排名与移动聚合查询优化

MySQL 8.0引入窗口函数后，原先需要临时表、子查询或程序层处理的分组内排名、环比、移动平均等查询，可以直接用一条SQL完成，执行计划更可控，查询性能也更容易优化。窗口函数语法与聚合函数兼容，但对每行计算一个窗口结果集，是SQL查询优化中非常实用的一块。本文给出ROW_NUMBER、RANK、LAG、SUM OVER等常见窗口函数的用法与对应的索引设计建议。

窗口函数基础语法与执行顺序

SELECT
  order_id, customer_id, amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

OVER子句划分窗口，PARTITION BY按字段分组，ORDER BY控制窗口内排序。窗口函数在WHERE、GROUP BY、HAVING之后执行，因此不能直接在WHERE中引用别名，需要用子查询包一层再过滤。

排名函数：ROW_NUMBER、RANK与DENSE_RANK

ROW_NUMBER给每行连续编号；RANK遇到相同值会跳号；DENSE_RANK不跳号。取每个客户最新订单：

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

这种写法替代了曾经的GROUP BY+MAX取id再join的复杂方案，逻辑清晰且只扫一次表。

LAG与LEAD：环比与差值计算

对比上一条记录的值，用LAG：

SELECT date,
       revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) AS daily_change
FROM daily_revenue;

LAG取前N行，第三个参数0是缺省值。环比、同比、多日差值这类指标，一条SQL直接算出来，不需要再建临时表存上期值。

移动聚合：SUM OVER实现滚动汇总

计算近7天移动平均：

SELECT date, amount,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM daily_revenue;

RANGE与ROWS的区别：ROWS按物理行数移动，RANGE按排序键值移动。日期列有重复值时，ROWS BETWEEN 6 PRECEDING 会把同一天的多行一起算进窗口，需按业务口径选择。

窗口函数查询的索引设计与性能要点

PARTITION BY 与 ORDER BY 列需要组合索引支撑，避免窗口内排序使用临时表filesort。以TOP客户查询为例，建立(partition_key, order_key)联合索引，窗口计算在索引扫描阶段完成。窗口函数不需要写GROUP BY，语义上更接近逐行计算，数据量大时注意内存排序；确需大窗口计算时，提前对分区键做压缩过滤。

窗口函数适用场景小结

分组内排名、分组取最新、环比同比、移动平均、累计值，全部可以收敛到窗口函数实现。相比自连接与子查询方案，窗口函数代码更短、执行计划更稳定。MySQL版本低于8.0需升级或改用变量写法，生产环境建议先EXPLAIN确认执行计划再用到核心报表。