Coding Interview Prep · 课时

保留每个键对应的最新行

Q: 「保留每个键对应的最新行」这节课中我会学到什么？

掌握“每位客户保留最新记录”的模式：按键分区、按日期排序 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

掌握“每位客户保留最新记录”的模式：按键分区、按日期排序

保留每个键对应的最新行 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

每个键对应最新记录的问题

“返回每位客户最近的一笔订单。”“获取每台设备的最新状态。”这种每个键对应最新行的问题，是 SQL 面试中出现频率最高的任务之一，因为它在实际分析工作中极其常见。

这是一种专门的每组取第一条问题：按键进行分区，按时间戳降序排列，然后保留第一行。本课将详细讲解这一模式及其替代方案。

为什么单独使用 MAX 不够

一个很容易想到的初始答案是按客户分组并使用 MAX(order_date)。这只能得到最新的日期，而无法得到该订单的其余列，例如订单 id、金额或状态。

如果面试官要求返回完整的最新行，那么使用 MAX 和 GROUP BY 时，还需要根据键和最大日期再次连接回原表。这种写法冗长，而且在出现并列时可能出错。窗口函数更加简洁。

-- Gives the date, not the full row
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;

ROW_NUMBER 模式

按键进行分区，按时间戳降序排列，最新的行就会获得 rn = 1。只保留这些行，就能得到每个键对应的完整最新记录。

这是首选答案。即使时间戳相同，它也会为每个键恰好返回一行，这通常正是“最新一行”所表达的含义。

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1;

处理时间戳并列

同一客户的两笔订单可能拥有相同的 order_date（日期相同，或时间戳完全相同）。如果没有并列决胜字段，哪一行会成为 rn = 1 就是任意的，并且可能在不同运行之间发生变化。

请加入唯一的次级键，例如 order_id DESC，让最新行的选择具有确定性。面试官尤其会考查您是否注意到了这个边界情况。

ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC, order_id DESC
) AS rn

PostgreSQL 的 DISTINCT ON 快捷写法

PostgreSQL 提供了一种简洁的写法：DISTINCT ON (key) 会根据 ORDER BY 为每个键保留第一行。ORDER BY 必须先列出相同的键列，然后再列出并列决胜字段或时间戳。

这种写法在 PostgreSQL 中简洁且快速，但不具备跨数据库兼容性。您可以将其作为特定 SQL 方言的加分项，同时将 ROW_NUMBER 作为默认的通用写法。

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

带条件的最新行

实际问题通常会增加筛选条件，例如“每位客户最近的一笔已完成订单”。请在排名之前应用筛选条件，以便只对符合条件的行编号。

请将条件放在内部查询的 WHERE 中（它会在窗口函数之前执行），然后在外层查询中取 rn = 1。在排名之后筛选会得到错误的行。

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT * FROM ranked WHERE rn = 1;

示例：最新设备状态

status_log 表记录了 device_id、status 和 logged_at。要获取每台设备的当前状态，请按 device_id 进行分区，按 logged_at DESC 排序，并保留 rn = 1。

许多实体的“当前状态”仪表板正是以仅追加事件日志为基础构建的。同一模式也适用于最新价格、最新位置和最新版本查询。

WITH latest AS (
  SELECT device_id, status, logged_at,
    ROW_NUMBER() OVER (
      PARTITION BY device_id ORDER BY logged_at DESC
    ) AS rn
  FROM status_log
)
SELECT device_id, status, logged_at
FROM latest
WHERE rn = 1;

性能说明

以下要点有助于体现高级水平：

在 (customer_id, order_date DESC) 上建立索引，可以让数据库引擎高效读取每个键对应的最新行。
窗口函数方案只扫描一次表，而相关子查询不是这样。
PostgreSQL 中的 DISTINCT ON 可以使用同一个索引，通常是单表查询中最快的选择。
对于以追加为主的事件日志，可以考虑创建一张物化的“最新记录”表，并进行增量刷新。

常见错误

请注意以下问题：

使用 MAX(date)，却只返回日期而不是完整行。
忘记加入并列决胜字段，导致日期并列时结果不具确定性。
在排名之后才根据条件筛选，这可能会选中本应排除的行。
混淆“最新的一行”（ROW_NUMBER）与“所有并列的最新行”（RANK）。

快速检查

请选择正确的每个键对应最新行的查询。

回顾：每个键对应最新行

模式是：PARTITION BY 键，ORDER BY 时间戳 DESC（加上唯一的并列决胜字段），保留 rn = 1。

MAX(date) 返回的是日期，而不是完整行。
请始终加入并列决胜字段，以确保结果具有确定性。
如果希望返回所有在最新时间戳上并列的行，请使用 RANK() = 1。
筛选条件应放在内部查询中，并在排名之前应用。
PostgreSQL 的 DISTINCT ON 是一种简洁、快速且特定于 SQL 方言的替代方案。

常见问题解答