SQL统计各分类排名前三的高价值数据方法

过去几年，我在处理这类“分组Top-N查询”时，遇到最多的坑其实就两个：一是函数选错了，二是忘了给窗口函数套子查询。把这俩搞定，剩下的事情往往水到渠成。

先给出最核心的一条建议——想清楚你要的是“前三名”还是“获取前三行”。这两个语义在SQL中差别非常大，对应的函数选择也不一样。多数面试题或业务场景说的“前三”，其实就是物理上前三条记录，不是所有并列前三的行。

用 ROW_NUMBER() 之所以是最稳妥的选择，核心在于它严格按排序顺序赋予唯一序号，保证每组恰好返回前三行——逻辑简单、结果可控。

为什么非它不可？

业务上说“前三名”，多数场景指物理上的前三行，不是“排名 ≤ 3”的所有行。RANK() 遇到并列会重复编号，比如两个第1名，接着直接跳到第3名，这时候你的结果可能远少于3条；DENSE_RANK() 并列不跳号，但同样可能让某组返回4条甚至更多——三个95分全是第1名，你的查询条件 WHERE rnk <= 3 就会把这四条都掏出来。

只有 ROW_NUMBER() 严格按排序顺序给唯一序号，保证 rn <= 3 真正筛出最多3条。这是逻辑层面的硬约束，谈不上哪个函数“更好”，而是要看场景要求什么。

• 误用 RANK() 的典型现象：WHERE rk <= 3 返回5条记录，只因前四条分数全相同
• DENSE_RANK() 适合“同薪同名、不跳号”的场景，比如职级榜，但不适合硬性Top-3数量控制
• MySQL 8.0+、PostgreSQL、SQL Server 都支持，SQLite 需 ≥ 3.25 且编译启用窗口函数

细节决定成败：OVER() 子句的语法门槛

单独写 ROW_NUMBER() 会报错：Window function 'ROW_NUMBER' requires an OVER clause。这还不算完，漏掉 PARTITION BY 就变成全表连续编号，效果相当于一个顺序号发生器；漏掉 ORDER BY 则直接语法错误。

• PARTITION BY category 决定“按哪列分组”，不能写成 GROUP BY —— 后者会聚合行数，破坏原始记录
• ORDER BY total_amount DESC, order_id ASC 中加 order_id ASC 的目的是防排序不稳定，避免每次查询结果不一致
• 如果 total_amount 含 NULL，默认排最前（MySQL）或最后（PostgreSQL），建议加 NULLS LAST 或用 COALESCE(total_amount, -1)

一个老生常谈但总被忘记的限制：窗口函数不能在 WHERE 里用

直接写 WHERE ROW_NUMBER() OVER (...) 会报错：window function is not allowed in WHERE clause。这是 SQL 标准限制，不是 MySQL 特有。

解决方案也很常规：要么套一层子查询，要么用 CTE。

• 子查询写法最通用，但别忘了加别名：

  SELECT category, order_id, total_amount FROM ( SELECT category, order_id, total_amount, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_amount DESC, order_id ASC) AS rn FROM orders ) t WHERE rn <= 3;

• CTE 更易读，尤其当基础数据需过滤时（比如只查 status = 'completed'）：

  WITH ranked AS ( SELECT category, order_id, total_amount, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_amount DESC) AS rn FROM orders WHERE status = 'completed' ) SELECT category, order_id, total_amount FROM ranked WHERE rn <= 3;

• 子查询必须加别名（如 AS t），否则 MySQL 报 ERROR 1248 (42000)：“Every derived table must ha ve its own alias”

容易被忽略的性能与兼容性细节

大表跑 ROW_NUMBER() 会全量扫描分组数据，没索引时慢得明显。另外，PARTITION BY 字段含 NULL 会被归为同一组——这常导致“未知分类”的数据意外挤进某组前三。

• 在 (category, total_amount) 上建联合索引，能显著加速排序和分组
• 若 category 是字符串但混了数字类型（如 '1', 1），隐式转换可能导致分组错乱，建议显式 CAST(category AS CHAR)
• MySQL 5.7 及更早不支持窗口函数，强行运行报 ERROR 1064 (42000)；先执行 SELECT VERSION(); 确认版本
• PARTITION BY 字段为 NULL 时，所有 NULL 行归一组——如需排除，加 WHERE category IS NOT NULL

真正麻烦的从来不是写对那几行 SQL，而是分组字段的 NULL 处理、排序字段的稳定性、以及旧版本兼容性——这些地方一漏，线上查出来的“前三名”就 quietly 错了。