如何运用SQL ROW_NUMBER窗口函数实现分类前三名数据的长尾查询？

本文共计1001个文字，预计阅读时间需要5分钟。

单独写 `ROW_NUMBER()` 会触发语法错误，例如 `SELECT id, name, ROW_NUMBER()` 这种写法在大多数主流SQL数据库（如PostgreSQL、SQL Server、MySQL 8.0、Oracle）中是不合法的。它必须配合 `OVER()` 子句来使用，明确指定排序逻辑和分组边界。例如：

常见错误现象：执行时报 Window function 'ROW_NUMBER' requires an OVER clause 或类似提示。

• OVER() 里至少要写 ORDER BY，否则数据库不知道怎么编号
• 要“每个分类下”取前三，就得用 PARTITION BY category 划分窗口，不能只靠 GROUP BY
• 如果漏掉 PARTITION BY，ROW_NUMBER() 会对全表连续编号，失去“每类独立排名”的效果

正确写法：先编号再过滤，不能在 GROUP BY 中用 ROW_NUMBER()

很多人想当然地写成 SELECT category, ROW_NUMBER() OVER (...) AS rn FROM t GROUP BY category，这会失败——ROW_NUMBER() 是窗口函数，不是聚合函数，不能和 GROUP BY 混用在同一层查询中。

正确路径是两层结构：外层查出所有行并编号，内层或 CTE 中筛选 rn 。

• 推荐用 CTE，逻辑清晰且可读性强：

  WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn FROM products ) SELECT * FROM ranked WHERE rn <= 3;

• MySQL 8.0+ 和 PostgreSQL 支持直接在主查询加 WHERE 过滤别名，但注意：不能在同一个 SELECT 级别引用 rn，必须嵌套或用 CTE
• 排序字段建议非空且有索引，否则 ORDER BY score DESC 在大数据量时性能明显下降

注意 PARTITION BY 和 ORDER BY 的字段选择差异

PARTITION BY 决定“分几组”，ORDER BY 决定“每组内怎么排”。两者语义不同，选错字段会导致结果完全偏离预期。

• 如果业务要求“每个分类下销售额最高的前三款商品”，PARTITION BY category 正确，但 ORDER BY sales_amount DESC 才对；写成 ORDER BY id 就变成按主键排，毫无业务意义
• 多个排序字段可解决并列问题，例如 ORDER BY score DESC, created_at ASC 表示分数相同时取更早录入的
• 如果 PARTITION BY 字段存在 NULL 值，不同数据库行为不一：PostgreSQL 把 NULL 归为一组，MySQL 8.0 默认也如此，但某些旧版本可能跳过或报错，建议提前 WHERE category IS NOT NULL

ROW_NUMBER() vs RANK() vs DENSE_RANK()：选错会影响“前三名”定义

“前三名”听起来简单，但实际取决于是否允许并列。用错函数会导致该进前三的被踢出去，或不该重复的重复出现。

• ROW_NUMBER() 严格不并列：1,2,3,4… 即使两个 score 都是 95，也会给 1 和 2 —— 这才是真正的“前三个记录”
• RANK() 并列跳号：95,95,90 → 1,1,3，此时“前三名”实际返回 4 行（两个第 1 + 一个第 3），不符合“仅取三条”的需求
• DENSE_RANK() 并列不跳号：95,95,90 → 1,1,2，同样可能返回多于三条
• 所以只要明确要“最多三条记录”，就必须用 ROW_NUMBER()，其他两个函数适用于“Top N 分数段”这类场景

真正容易被忽略的是：当某分类数据不足三条时，ROW_NUMBER() 仍能正常返回全部（1 条就 1 条，2 条就 2 条），不需要额外补空；但如果你依赖结果恒为 3×N 行，就得自己补缺。