MySQL 8 的窗口函数：分组取第 N 条的三种写法

「每个分类取最新一条」是 SQL 里的经典难题。MySQL 8 之前只能靠自连接或者变量，写法别扭还有性能问题。有了窗口函数之后，这件事变得很自然。

数据准备

CREATE TABLE article (
    id INT PRIMARY KEY,
    category VARCHAR(32),
    title VARCHAR(128),
    created_at DATETIME
);

写法一：ROW_NUMBER

SELECT * FROM (
    SELECT a.*,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC) AS rn
    FROM article a
) t
WHERE rn = 1;

PARTITION BY 是分组，ORDER BY 决定组内编号。rn = 1 就是每组第一条。要取前三条就是 rn <= 3。

ROW_NUMBER 的特点是编号唯一，遇到 created_at 相同的行也会强行排出先后（不确定的顺序）。

写法二：RANK / DENSE_RANK

RANK()       OVER (PARTITION BY category ORDER BY score DESC)
DENSE_RANK() OVER (PARTITION BY category ORDER BY score DESC)

有并列时的区别：

分数  90 90 80 70
RANK       1  1  3  4
DENSE_RANK 1  1  2  3

排行榜类需求用 DENSE_RANK，取「排名前三」不会因为并列而少人。

写法三：LAG / LEAD 比较前后行

SELECT category, created_at,
       LAG(created_at) OVER (PARTITION BY category ORDER BY created_at) AS prev_time,
       TIMESTAMPDIFF(DAY,
           LAG(created_at) OVER (PARTITION BY category ORDER BY created_at),
           created_at) AS gap_days
FROM article;

算「距离上一篇多少天」，不用自连接，一行就出来了。

累计与占比

SELECT category, title,
       SUM(1) OVER (PARTITION BY category) AS cat_total,
       ROUND(100.0 * COUNT(*) OVER (PARTITION BY category) /
             COUNT(*) OVER (), 1) AS pct
FROM article;

OVER () 不带 PARTITION 表示全表，可以算全局占比。这类报表以前要写子查询，现在很直接。

性能要注意

窗口函数需要排序和分组，数据量大时开销不小。EXPLAIN 里看到 Using temporary; Using filesort 是正常的，但要确认排序能在索引上完成：

CREATE INDEX idx_cat_time ON article (category, created_at DESC);

有了这个索引，PARTITION BY category ORDER BY created_at 可以直接利用索引顺序，省掉排序。

分区表上要特别注意：窗口函数不能跨分区计算，PARTITION BY 的列最好和分区键一致，否则会全表扫描再排序。

对比老写法

MySQL 5.7 时代用变量：

SELECT * FROM (
    SELECT a.*, @rn := IF(@cat = category, @rn + 1, 1) AS rn, @cat := category
    FROM article a, (SELECT @rn := 0, @cat := '') x
    ORDER BY category, created_at DESC
) t WHERE rn = 1;

能跑，但依赖变量的执行顺序（官方明确说不可靠），MySQL 8 里也还能用但被标记为废弃。有窗口函数就别用这个了。

一句话

窗口函数的核心是「分组 + 排序 + 编号」，记住这个思路，取最新、取前 N、算环比、算累计都是同一个模板。