窗口函数三种排序语义对比

示例说明

三个函数在无并列时结果一致，差异只在并列值上爆发：ROW_NUMBER 给出唯一行号（98 分两人是 1 和 2）；RANK 并列同名次且下一个名次跳空（1、1、3）；DENSE_RANK 并列同名次但不跳空（1、1、2）。排行榜要 RANK，录取配额要 DENSE_RANK（前五名实际可能六人），去重取一行要 ROW_NUMBER。

技术要点

• RANK 跳空而 DENSE_RANK 紧凑
• ROW_NUMBER 恒定唯一适合去重
• 分组前 N 是 ROW_NUMBER 的招牌场景

代码解读

第一段查询把三种函数并排展示，两个九十八分、两个九十二分的并列结构让差异一目了然；第二段是每组前 N 的标准范式：CTE 内先 ROW_NUMBER 按 PARTITION 分组排名，外层 WHERE 过滤行号，任何分组取头部的问题都套这个模子。

使用建议

分页去重场景 ROW_NUMBER 要配唯一排序键防随机。并列敏感的业务先明确并列策略再选函数。大分区排名注意排序溢出成本。

代码示例

-- 三种排序语义在同一数据上的行为差异
WITH scores AS (
  SELECT * FROM (VALUES
    ('ada', 98), ('grace', 98), ('linus', 95),
    ('margaret', 92), ('katherine', 92), ('alan', 88)
  ) AS t(player, points)
)
SELECT
  player,
  points,
  ROW_NUMBER() OVER (ORDER BY points DESC) AS row_num,
  RANK()       OVER (ORDER BY points DESC) AS rank_no,
  DENSE_RANK() OVER (ORDER BY points DESC) AS dense_no
FROM scores
ORDER BY points DESC, player;

-- 每组前 N（配额类查询的标准写法）
WITH ranked AS (
  SELECT
    region,
    salesperson,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY region ORDER BY revenue DESC
    ) AS rn
  FROM quarterly_revenue
)
SELECT region, salesperson, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, revenue DESC;