row_number()强制唯一连续序号（如1、2、3），适用于需严格位置区分的场景；rank()对相同值赋予并列名次并跳过后续序号（如95、95、94→1、1、3），适用于允许并列且需体现名次间隔的实时排行榜。
如何用 ROW_NUMBER() 和 RANK() 区分并列名次需求
实时排行榜的核心是「名次计算是否允许并列」。如果分数相同必须占不同位置（比如第1、第2、第3），用 ROW_NUMBER()；如果相同分数要共享名次（比如两个95分都是第1，下一个94分是第3），得用 RANK()。
常见错误是直接套用 ROW_NUMBER() 后发现「并列用户被强行拆开排名」，导致运营同学质疑数据不准。实际场景中，游戏积分榜多用 RANK()，而「按登录时间先后排序的实时抢号列表」才适合 ROW_NUMBER()。
-
RANK()会跳过后续名次：[95,95,94] → [1,1,3] -
DENSE_RANK()不跳过：[95,95,94] → [1,1,2]，适合需要连续序号的榜单（如「前10名」展示） - 所有窗口函数必须配
OVER子句，且ORDER BY不能为空——漏写会报错ERROR: window function requires an ORDER BY clause
怎样让名次变动可追踪（对比上一周期）
单纯查当前排名没用，运营需要知道「张三从第12名升到第7名」。这不能靠前端 diff，得在 SQL 层就拉出新旧两列。典型做法是用 LATERAL 或两次子查询，但更轻量的是用 CTE 配合时间戳切片。
假设表 user_scores 有 user_id、score、updated_at，想比对「5分钟前 vs 当前」：
WITH curr AS (
SELECT user_id, score, RANK() OVER (ORDER BY score DESC) AS rank_now
FROM user_scores
WHERE updated_at >= NOW() - INTERVAL '5 minutes'
),
prev AS (
SELECT user_id, RANK() OVER (ORDER BY score DESC) AS rank_then
FROM user_scores
WHERE updated_at = NOW() - INTERVAL '10 minutes'
)
SELECT c.user_id, c.rank_now, p.rank_then,
COALESCE(p.rank_then, 9999) - c.rank_now AS rank_change
FROM curr c
LEFT JOIN prev p USING (user_id);
注意点：COALESCE(p.rank_then, 9999) 是为了把新进用户（上期无记录）的变动标为「+9992」这类显著值，方便前端高亮；时间范围必须严格不重叠，否则同一用户可能在两段里都被计入，导致 rank 计算失真。
为什么 ORDER BY score DESC NULLS LAST 很关键
真实数据里常有未提交分数的用户，score 为 NULL。PostgreSQL 默认把 NULL 排在最前（NULLS FIRST），结果就是一堆空分用户霸占榜单顶部——这不是 bug，是标准行为，但业务上绝对不可接受。
- 显式写
ORDER BY score DESC NULLS LAST，确保有效分数优先 - 如果想把零分（0）和空分（NULL）都垫底，得先用
CASE归类：ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score DESC - 索引要匹配这个排序逻辑，否则窗口函数执行会变慢：建索引时得包含
score和NULLS LAST语义（实际建法是CREATE INDEX ON user_scores (score DESC NULLS LAST)）
实时刷新时怎么避免全表重算性能崩塌
每秒跑一遍 RANK() OVER (ORDER BY score DESC) 扫全表？用户超10万就卡死。根本解法不是优化 SQL，而是控制「参与排名的数据集大小」。
- 加
WHERE score > (SELECT percentile_cont(0.8) WITHIN GROUP (ORDER BY score) FROM user_scores)，只算前20%活跃用户——大多数榜单真正关注的就是头部 - 用物化视图定期固化排名（
REFRESH MATERIALIZED VIEW CONCURRENTLY），配合定时任务每10秒刷一次，查询走 MV 就不碰原表 - 如果业务允许「最终一致性」，干脆把排名计算下沉到应用层：监听
score变更的逻辑复制消息（wal2json），用 Redis Sorted Set 维护，SQL 只负责查ZRANGE key 0 99 WITHSCORES
别迷信窗口函数万能。当榜单需要毫秒级响应且用户量大，数据库只是数据源，不是计算引擎。
