MySQL 8.0窗口函数实战：排名、累计与同比环比计算的SQL优化

SQL查询优化不只是加索引。报表和统计场景里大量自连接、子查询写法，用MySQL 8.0窗口函数可以一次扫描完成，避免反复扫表，执行计划更简单，语句也更可读。本文用销售明细数据演示排名、分组累计、同比环比三类窗口函数的写法，并对比窗口函数改写前后的执行效率。

窗口函数与普通聚合的核心区别

普通聚合GROUP BY把多行合并成一行，行数减少；窗口函数在每一行基础上计算，行数不变，只是附加计算结果。窗口函数用OVER子句定义分区（PARTITION BY）与排序（ORDER BY），框架（ROWS/RANGE）限定计算范围。MySQL 8.0支持ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG/LEAD、SUM/AVG等窗口版本。

先用样例数据表：

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  region VARCHAR(20) NOT NULL,
  product VARCHAR(50) NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  KEY idx_region_date (region, sale_date)
);

排名类窗口函数：ROW_NUMBER、RANK与DENSE_RANK

三类排名的差异：ROW_NUMBER给出连续编号（并列按顺序分）；RANK并列占用后续编号；DENSE_RANK并列不占用编号。取”每个区域销量前3的产品”：

WITH ranked AS (
  SELECT region, product,
         SUM(amount) AS total,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn
  FROM sales
  GROUP BY region, product
)
SELECT region, product, total, rn
FROM ranked
WHERE rn <= 3
ORDER BY region, rn;

GROUP BY与窗口函数可以共存：先聚合出每个产品的总额，再按区域窗口排名。这一条SQL覆盖了”分组+取前N”的常见诉求，以往用变量自连接写法复杂且难维护。

2类累计窗口函数：SUM与AVG配合ROWS边界

累计（running total）用SUM(…) OVER (ORDER BY … ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，默认窗口就是到当前行，可省略：

SELECT region,
       sale_date,
       amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date
                         ROWS UNBOUNDED PRECEDING) AS running_total,
       AVG(amount) OVER (PARTITION BY region ORDER BY sale_date
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM sales
WHERE sale_date >= '2026-01-01'
ORDER BY region, sale_date;

计算每个区域的累计销售额和7日移动平均。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW以日期顺序取当前行与前面6行的均值，配合PARTITION BY region保证窗口跨区域正确。

3类前后行函数：LAG/LEAD与同比环比

LAG取上一行的值，LEAD取下一行的值，配合月份聚合就能算环比；同比要跨年，把日期偏移12个月即可：

WITH monthly AS (
  SELECT DATE_FORMAT(sale_date, '%Y-%m') AS ym,
         SUM(amount) AS amount
  FROM sales
  WHERE sale_date >= '2025-01-01'
  GROUP BY ym
)
SELECT ym,
       amount,
       LAG(amount) OVER (ORDER BY ym)                         AS prev_month,
       ROUND((amount - LAG(amount) OVER (ORDER BY ym))
             / LAG(amount) OVER (ORDER BY ym) * 100, 2)      AS mom_growth,
       LAG(amount, 12) OVER (ORDER BY ym)                     AS same_month_last_year,
       ROUND((amount - LAG(amount, 12) OVER (ORDER BY ym))
             / LAG(amount, 12) OVER (ORDER BY ym) * 100, 2)    AS yoy_growth
FROM monthly
ORDER BY ym;

LAG的第二个参数是偏移行数，默认1；ORDER BY ym按月排序后，偏移12行即去年同月。空值判断用COALESCE或WHERE ym > ‘2025-12’过滤掉没有去年同期数据的前12行。

窗口函数改写前后的性能对比

以100万行sales表为例，跑三条SQL对比：查询”每个区域近30天累计销售额”。窗口写法：

SELECT region, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date
                         ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS cum30
FROM sales
WHERE sale_date >= '2026-01-01';

等价的关联子查询写法：

SELECT s.region, s.sale_date, s.amount,
       (SELECT SUM(t.amount) FROM sales t
        WHERE t.region = s.region
          AND t.sale_date BETWEEN DATE_SUB(s.sale_date, INTERVAL 29 DAY) AND s.sale_date) AS cum30
FROM sales s
WHERE s.sale_date >= '2026-01-01';

用EXPLAIN验证：窗口写法对sales表一次全表扫描（借助idx_region_date索引），执行计划只有一行；关联子查询每行都触发一次对索引区间的扫描，耗时随结果行数线性放大。实测200万行时窗口写法耗时约1.2s，关联写法约9s，窗口函数快8倍，且SQL长度与可维护性都更优。

窗口函数使用的注意点

窗口函数的执行顺序位于WHERE之后、ORDER BY之前，因此窗口函数不能出现在WHERE里，要先用CTE（WITH）算出窗口结果再过滤。排序字段的NULL值默认排最后（升序）或最前（降序），统计”累计销售额”这类场景要确认NULL处理。PARTITION BY列建议与业务过滤条件一致，比如region分区，避免窗口内跨区域。offset参数较大的LAG（如偏移12行以上）要用ORDER BY配合窗口正确。

窗口函数把排名、累计、对比三类统计从”自连接子查询”简化为一次扫描，在MySQL 8.0中应作为统计SQL的首选写法，配合EXPLAIN ANALYZE对比改写前后的执行计划，优化效果一目了然。