用 SQL 窗口函数做排行榜:一次查询解决排名问题
排行榜的三个常见写法
做排行榜时,很多人第一反应是查出全部数据,再在代码里循环比较分数、手动拼接名次;或者用自连接把表跟自己 join 一遍,靠 COUNT(*) 数出比自己分数高的人数。这两种写法在数据量小时看不出问题,一旦行数上万,查询时间就会从毫秒级涨到几十秒,而且还容易把并列名次算错。
ROW_NUMBER、RANK 与 DENSE_RANK 的区别
SQL 标准里有三个窗口函数专门解决排名:
ROW_NUMBER()给每一行一个唯一序号,分数相同也不并列,取哪一个取决于 ORDER BY 的第二排序键。RANK()允许并列,但并列之后会跳号,例如 1、1、3。DENSE_RANK()允许并列且不跳号,例如 1、1、2。
选哪个取决于业务含义:比赛名次通常要跳号,用 RANK;榜单展示往往不希望出现空号,用 DENSE_RANK。
一条查询做出分组排行榜
窗口函数的 PARTITION BY 是实现分组排行的关键。下面的查询给每个部门内部按销售额排名,只取前三名:
SELECT dept, name, amount, rk
FROM (
SELECT dept, name, amount,
RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS rk
FROM sales
) t
WHERE rk <= 3
ORDER BY dept, rk;
注意窗口函数不能直接写在 WHERE 里,因为 WHERE 的执行顺序在窗口计算之前,必须先包一层子查询或 CTE。
性能与索引
窗口函数的排序开销主要来自 ORDER BY 的字段。给 (dept, amount DESC) 建联合索引,让数据库直接顺着索引扫描,通常能省掉一次显式排序。另外,分区字段的选择要贴合查询条件,如果每次都只查某个部门,先用 WHERE 过滤再开窗,比全表开窗再筛要快得多。
几个容易踩的坑
第一,NULL 值在 ORDER BY 中的位置各家数据库默认不同,MySQL 认为 NULL 最小,PostgreSQL 认为最大,排名结果会不一致,建议显式写 NULLS LAST 或先用 COALESCE 处理。第二,窗口函数不能和 DISTINCT 混用在同一个 SELECT 层级,需要先聚合再开窗。第三,如果只是要「取每组最新一条」,用 ROW_NUMBER() 配 WHERE rn = 1 比 GROUP BY 加子查询更直观,也更容易走索引。
- 点赞
- 收藏
- 关注作者
评论(0)