7.2 排名函数三兄弟与组内去重实战

7.2 排名函数三兄弟与组内去重实战

本节摘要：ROW_NUMBER、RANK、DENSE_RANK 三个排名函数都给窗内行编号，差别全在"并列怎么处理"——ROW_NUMBER 硬编号不并列，RANK 并列同名且跳号，DENSE_RANK 并列同名不跳号。按业务口径选对兄弟是报表正确性的关键；ROW_NUMBER 等于 1 则是"组内去重取首行"的标准套路，替代 DISTINCT 与 GROUP BY 的两条老路。

同分并列的排名之争

先造一点并列：分类 3 里让《计算机网络》和《深入理解计算机系统》同价 79 元。三个函数同场竞技：

-- 三兄弟同场：按价格降序给分类3的书编号
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
  RANK()       OVER (ORDER BY price DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM books
WHERE category_id = 3;
​

+-----------------------+----------+---------+------+-----------+
| title                 | price    | row_num | rnk  | dense_rnk |
+-----------------------+----------+---------+------+-----------+
| 算法导论              |   128.00 |       1 |    1 |         1 |
| 计算机网络            |    79.00 |       2 |    2 |         2 |
| 深入理解计算机系统    |    79.00 |       3 |    2 |         2 |
| 操作系统导论          |    20.00 |       4 |    4 |         3 |
+-----------------------+----------+---------+------+-----------+
​

并列的 79 元一行看尽三兄弟的性格。ROW_NUMBER：1、2、3、4 连续硬编号，并列的两行被强行分出 2 和 3——谁得 2 取决于物理顺序，每次执行可能不同。RANK：并列行同名次 2、2，下一个名次跳到 4（并列两人占了 2 和 3 的位置）——"前三名有四人"的体育口径。DENSE_RANK：并列同名 2、2，下一个紧凑排 3——"价格档位"式口径。

业务选型由此有章可循：

图 7-2 三兄弟并列行为对照

组内 Top N：派生表加排名的经典组合

"每个分类价格最高的书"在第 6 章用相关子查询做过（行数级的重复计算），窗口函数版把两种武器合璧——PARTITION BY 切窗、ROW_NUMBER 排名、外层筛第 1：

-- 每分类取价格第一名：开窗 排名 外层过滤 三步走
SELECT title, category_id, price
FROM (
  SELECT
    title, category_id, price,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY price DESC
    ) AS rn
  FROM books
) ranked
WHERE rn = 1;
​

+-----------------------+-------------+----------+
| title                 | category_id | price    |
+-----------------------+-------------+----------+
| 数据库系统概念        |           2 |    89.00 |
| 算法导论              |           3 |   128.00 |
| 操作系统导论          |           5 |    39.00 |
+-----------------------+-------------+----------+
​

结构上是 7.1 节"派生表包装窗口值"的直接应用：内层算 rn，外层 WHERE rn = 1。三个可调旋钮各管一事：PARTITION BY 定"每组"（换成 customer_id 就是"每顾客最新一单"）；ORDER BY 定"第一"的定义（换成 created_at DESC 就是"最近一条"）；外层条件定取几条（rn <= 3 即每组前三）。这一个模板覆盖了业务里绝大多数"每组取代表行"的需求。

Top N 的另一个变体是"整体前 N 但每组限额"，例如"全店销量前十、每分类最多进三本"：

-- 双层排名：先组内限额 再全局排序
SELECT title, category_id, sales
FROM (
  SELECT title, category_id, sales,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS cat_rn,
         DENSE_RANK() OVER (ORDER BY sales DESC) AS overall_rnk
  FROM 月销售统计
) t
WHERE cat_rn <= 3          -- 每分类最多三本
ORDER BY overall_rnk       -- 按全局名次展示
LIMIT 10;                  -- 总共十本
​

模板输出：全局榜单 每分类至多三本 同分并列按 DENSE_RANK 同列
​

组内去重：ROW_NUMBER 取首替代 DISTINCT

脏数据场景：同步任务重复写入了订单，同单同书出现多行，要"每单每书只留一条（保留 id 最大的）"。老三样的困境与窗口解法对照：

-- 目标：order_id + book_id 相同的重复行 只留 id 最大的一条
DELETE FROM order_items
WHERE id NOT IN (
  SELECT keep_id FROM (
    SELECT MAX(id) AS keep_id
    FROM order_items
    GROUP BY order_id, book_id          -- 老三样：分组取最大 id
  ) t
);
​

Query OK, 3 rows affected -- 三条重复行被清除
​

GROUP BY 版在这个场景够用；但当"留哪条"的规则复杂起来——比如"优先留状态为完成的，其次留 id 最大的"——GROUP BY 的 MAX 只认单列，规则写不进去。ROW_NUMBER 的 ORDER BY 想多复杂有多复杂：

-- 复杂保留规则的去重：ROW_NUMBER 排序即规则
SELECT id, order_id, book_id, status
FROM (
  SELECT id, order_id, book_id, status,
         ROW_NUMBER() OVER (
           PARTITION BY order_id, book_id
           ORDER BY CASE status WHEN 'completed' THEN 0 ELSE 1 END, id DESC
         ) AS rn
  FROM order_items
) t
WHERE rn = 1;
​

先选已完成状态 其次取 id 大的 每组只留一行
第3章的 CASE 在这里成为排序规则的组成部分
​

窗口函数与 CASE 的组合拳至此成型——ORDER BY 里写业务规则，是 SQL 表达力的巅峰操作之一。取首行删除时套在 DELETE 的 NOT IN 里（或先 SELECT 确认再删），安全流程沿用第 2 章的先查后改。

⚠️ 常见坑：并列时依赖 ROW_NUMBER 的顺序。两个订单"同一天下单、同金额"，ROW_NUMBER 谁排第 2 没有承诺，今天跑和明天跑可能不同。要么接受任意一条（幂等场景），要么把排序键补齐到完全确定（加 id 作最后一级排序），别让报表今天一个样明天一个样。

💡 关键直觉：ROW_NUMBER 是"强行仲裁"，RANK 是"并列认账但记账跳号"，DENSE_RANK 是"并列认账且记账紧凑"。先问业务对并列的态度，再挑兄弟，别让工具替业务做决定。

本节要点回顾

• 三兄弟一句分：ROW_NUMBER 不并列、RANK 并列跳号、DENSE_RANK 并列紧凑，口径决定选型；
• 组内 Top N 模板：PARTITION BY 定组、ORDER BY 定第一、外层筛 rn，三个旋钮覆盖"每组取代表行"一族需求；
• 多层排名可叠加：组内限额加全局名次，一层窗口解决一层维度；
• 去重升级：保留规则复杂时 ROW_NUMBER 的 ORDER BY 写规则，CASE 入排序键是标准技巧；
• 并列顺序不可依赖：要确定结果就把排序键补齐到唯一（通常加 id）；
• 排名值不能进 WHERE：派生表或 CTE 先物化再筛，7.1 节的执行位置规则贯穿本章。