MySQL 8 窗口函数实战：分组取最新、分组取 TOP N 与滑动累计，替代子查询的完整写法

  1.  Home
  2. Text  
    3. Share to：

为什么老站长该把子查询换成窗口函数

做站久了总会遇到一类查询：从文章表里挑出每个分类最新的一篇、从访问日志里算每个 IP 的累计请求数、把订单按用户分组后取金额最高的前三笔。过去我们习惯用相关子查询或者自连接来写，代码又长又难读，数据量一大就慢得让人抓狂。MySQL 8.0 引入的窗口函数（Window Function）正是为这类「分组内排名与累计」的场景准备的，它可以在不折叠结果行的前提下，对每一行附带计算出分组内的排序、累计和、上一行/下一行等派生值。

这篇文章不讲教科书式的语法罗列，而是从个人站长真实会碰到的三类查询出发：分组取最新、分组取 TOP N、滑动累计与环比，把窗口函数怎么替代旧的写法、性能差在哪里、以及线上使用时要避开的坑一次讲透。

窗口函数到底做了什么

普通聚合（GROUP BY）会把一组多行压缩成一行，你一旦用了 GROUP BY，就没法在同一行里既看到分组汇总又看到明细。窗口函数的思路不一样：它先按 PARTITION BY 把结果集划成若干「窗口」（分组），再在窗口内部按 ORDER BY 排序，最后用一个函数对窗口内的相关行求值，但每一行仍然保留，派生值作为新列挂在这一行上。

函数名(...) OVER (
  PARTITION BY 分组列
  ORDER BY 排序列
  [ROWS/RANGE 帧范围]
)

OVER 子句就是「窗口函数」与普通函数的唯一区别标志。没有 OVER 的 ROW_NUMBER() 或 SUM() 会被 MySQL 当成语法错误；而有 OVER 时，SUM() 不再折叠行，而是逐行给出窗口内的累计值。

最常用的几个函数可以这样归纳：

  • ROW_NUMBER()：窗口内从 1 开始的连续序号，重复值也各不相同。
  • RANK() / DENSE_RANK()：并列排名。RANK 遇并列会跳号（1,1,3），DENSE_RANK 不跳号（1,1,2）。
  • SUM() / AVG() / COUNT() 带 OVER：窗口内聚合或累计聚合。
  • LAG() / LEAD()：取窗口内上一行 / 下一行的值，做环比、同比特别顺手。
  • FIRST_VALUE() / LAST_VALUE()：窗口第一行 / 最后一行的值。

场景一：每个分类只取最新一篇（去重取一）

这是个人站长最常写的查询——首页或分类页要展示「每个分类下最新的一篇文章」。老写法是相关子查询套一层，或者对每个分类单独跑一次，分类一多就要跑几十次。

假设文章表结构近似如下（字段是示意，按你本站的实际表名替换）：

CREATE TABLE posts (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  category  VARCHAR(32),
  title     VARCHAR(200),
  created   DATETIME,
  KEY idx_cat_created (category, created)
);

用 ROW_NUMBER() 一行搞定：

SELECT id, category, title, created
FROM (
  SELECT id, category, title, created,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY created DESC, id DESC
         ) AS rn
  FROM posts
) t
WHERE t.rn = 1;

内层子查询给每一行标上「在本分类内按时间倒序的序号」，外层只留序号为 1 的行，也就是每个分类的最新一篇。ORDER BY created DESC, id DESC 里补一个 id 是为了在同一时间戳有多个分类文章时保证结果稳定——不补的话，ROW_NUMBER 在并列时的分配是未定义的，可能每次跑出来不一样。

如果某个分类的最新文章可能有并列（比如同一秒导入的），而你想把并列的都保留，就把 ROW_NUMBER() 换成 RANK()，然后仍然过滤 rn = 1，这样并列的行都会留下来。

场景二：分组取 TOP N（每个分类前 3 篇）

把上一节的 rn = 1 改成 rn <= 3，就得到了「每个分类最新 3 篇」。这个模式几乎可以做任何「分组取 TOP N」：

SELECT category, title, created, rn
FROM (
  SELECT category, title, created,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY created DESC
         ) AS rn
  FROM posts
) t
WHERE t.rn <= 3
ORDER BY category, rn;

对比一下旧的自连接写法，你会立刻明白窗口函数的价值。用自连接取「每类前 3」要写成：

SELECT p1.category, p1.title
FROM posts p1
LEFT JOIN posts p2
  ON p1.category = p2.category
 AND p1.created < p2.created
GROUP BY p1.id
HAVING COUNT(p2.id) < 3;

自连接的写法要 join 出的中间结果远大于原表（每类 N 篇就产生约 N² 的匹配行），在几十万行的日志表上会直接拖垮数据库。窗口函数则只需一次扫描加一次排序，代价可控得多。只要索引建对（category, created 的联合索引），窗口函数在这个场景下几乎是标准答案。

场景三：累计值与环比（滑动窗口）

做数据看板时，经常要给出「每个 IP 按时间的累计访问量」以及「今天比昨天增长了多少」。这类需求靠 SUM() OVER 配合帧范围，以及 LAG() 就能优雅解决。

先看累计求和。默认带 ORDER BY 的窗口帧是「从窗口第一行到当前行」，所以下面的查询会给出逐行递增的累计值：

SELECT day, pv,
       SUM(pv) OVER (ORDER BY day) AS running_pv
FROM daily_stats
ORDER BY day;

如果你想让它按某个维度分组后各自累计，只需要加 PARTITION BY：

SELECT ip, day, pv,
       SUM(pv) OVER (
         PARTITION BY ip
         ORDER BY day
       ) AS running_pv
FROM ip_daily
ORDER BY ip, day;

再看环比。LAG() 取出窗口内上一行的值，与当前行相减就得到日环比：

SELECT day, pv,
       LAG(pv) OVER (ORDER BY day) AS prev_pv,
       pv - LAG(pv) OVER (ORDER BY day) AS diff
FROM daily_stats
ORDER BY day;

LAG(pv, 1, 0) 的第三个参数是「没有上一行时的默认值」，不填则返回 NULL。第一行没有上一天，若不设默认值，diff 会是 NULL，很多前端图表遇到 NULL 会直接画出断点，所以线上给个 0 更稳。

帧范围（frame）——最容易踩坑的地方

默认的窗口帧不是常量，它取决于 OVER 里有没有 ORDER BY：

  • 有 ORDER BY：默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从窗口第一行累计到当前行。
  • 没有 ORDER BY：默认帧是整个分区 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即聚合整组。

这两者行为差别巨大。比如下面这个查询，没写 ORDER BY 时 SUM 给的是整组总和（每组每行都一样），写了 ORDER BY 就变成累计：

-- 整组总和（每行相同）
SELECT category, pv, SUM(pv) OVER (PARTITION BY category) AS total FROM t;

-- 逐行累计（从第一行加起）
SELECT category, pv, SUM(pv) OVER (PARTITION BY category ORDER BY day) AS running FROM t;

另一个易错点是 LAST_VALUE()：因为默认帧到「当前行」为止，LAST_VALUE() 拿到的其实是当前行自己，而不是整个分区的最后一行。要让 LAST_VALUE() 真正取分组最后一行，必须显式把帧扩展到分区末尾：

LAST_VALUE(pv) OVER (
  PARTITION BY category
  ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

忘了写帧范围是新手最常犯的错，结果 LAST_VALUE 等于当前值，白白排查半天。

性能：索引与排序才是瓶颈

窗口函数本身不会「神奇地快」，它底层要做两件事——按 PARTITION BY 分桶、按 ORDER BY 排序。如果排序能用上索引，窗口函数的成本就很低；用不上索引，就成了全表排序。 所以关键仍是索引设计：

  • PARTITION BY category ORDER BY created DESC——建 (category, created) 联合索引，MySQL 可借索引顺序免去额外排序。
  • 多列排序时，索引列顺序要与 PARTITION BY 加 ORDER BY 的顺序一致，且注意方向。
  • 先 WHERE 过滤掉大量无关行再开窗，比全表开窗后再过滤快得多——但要注意，WHERE 里的过滤条件如果引用窗口结果（比如过滤 rn = 1），必须放在外层子查询，不能直接写进内层 WHERE，因为窗口函数是「先算后过滤」。

一个常见的性能陷阱是：把 ROW_NUMBER() 的过滤放到同一个 SELECT 的 WHERE 里，MySQL 会报 Unknown column 'rn' in 'where clause'。正确做法是套一层派生表，如前面示例所示。

用 EXPLAIN 对比新旧写法时，重点看 type 有没有变成 ALL（全表扫描）以及 Extra 里有没有 Using filesort。如果窗口函数查询出现了 Using temporary; Using filesort，说明分区或排序没能用上索引，该回去检查索引了。

三个线上必须记住的坑

  1. 窗口函数不能在 WHERE/HAVING 里直接过滤。 SQL 的执行顺序是「FROM → WHERE → GROUP BY → HAVING → SELECT（含窗口）→ ORDER BY → LIMIT」，窗口在 SELECT 阶段才计算，自然早于它的 WHERE 看不到它。要过滤就再套一层子查询。
  2. 只会出现在 SELECT 列表和 ORDER BY 里。 把窗口函数写在 JOIN ... ON 或 GROUP BY 里都是语法错误，这是标准行为，不是 MySQL 的缺陷。
  3. 版本要求 8.0 以上。 MySQL 5.7 不支持窗口函数，如果本站还在 5.7，升级前要有回退方案——切记别在生产库直接大版本升级，先备份（mysqldump 或用 LVM 快照做一份一致性副本），再在从库验证业务 SQL 无误后再切换。

把旧查询迁移过来的检查清单

  • 先确认数据库版本 SELECT VERSION(); 是否 ≥ 8.0。
  • 把每个「相关子查询取 TOP1」或「自连接取 TOP N」翻译成 ROW_NUMBER() OVER(...)，外层过滤 rn <= N。
  • 检查索引：PARTITION BY + ORDER BY 的组合列，尽量建成一条联合索引。
  • 跑 EXPLAIN，确认没有多余的 Using filesort / Using temporary。
  • 对需要「稳定结果」的分组排序，补上唯一列（如 id）作为排序第二列。
  • 给 LAG/LEAD 设默认值，避免前端图表出现断点。
  • 改完后在从库或测试库先跑一遍真实数据，对比新旧结果的条数和数值是否一致。

窗口函数是 MySQL 8 时代最值得个人站长掌握的一项能力。它不增加任何运维成本，却能把过去又慢又难维护的「分组取最新 / 分组取 TOP N / 滑动累计」查询，压缩成一段一眼能看懂、性能又可控的 SQL。下一次再遇到「每个分类最新一篇」这种需求，先想想能不能用 ROW_NUMBER() 解决，你会发现代码清爽了很多。

Last modification：October 11th, 2026 at 09:25 pm
© 允许规范转载

相关文章

Leave a Comment