SQL Interview Prep · レッスン

キーごとに最新の行を残す

Q: 「キーごとに最新の行を残す」で何を学びますか？

キーでパーティション化し、日付で並べ替えて「顧客ごとの最新レコード」を取得するパターンを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

キーでパーティション化し、日付で並べ替えて「顧客ごとの最新レコード」を取得するパターンを学びます。

レッスン 4/413 ステップ

「キーごとに最新の行を残す」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

キーごとの最新レコード問題

「顧客ごとに最も新しい注文を返してください。」「デバイスごとに最新のステータスを取得してください。」このキーごとの最新行の問題は、実際の分析業務で頻繁に登場するため、SQL面接でも特に出題頻度が高い課題の一つです。

これはグループごとのtop-1を求める特殊なケースです。キーでパーティション分割し、タイムスタンプの降順で並べ、最初の行を残します。このレッスンでは、このパターンと代替方法を詳しく扱います。

MAXだけでは不十分な理由

最初に思いつきやすい回答は、顧客ごとにグループ化してMAX(order_date)を求める方法です。これで最新の日付は得られますが、その注文の行に含まれる他の情報、注文ID、金額、ステータスまでは得られません。

面接官が最新の行全体を求めている場合、GROUP BYとMAXの組み合わせでは、キーと最大日付を使ってテーブルに再結合する必要があります。これは冗長で、同順位があると正しく動かない場合もあります。ウィンドウ関数のほうがすっきりしています。

-- 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が付与されます。その行だけを残せば、キーごとの最新レコード全体を取得できます。

これは基本となる回答です。タイムスタンプが同じ場合でもキーごとに必ず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;

タイムスタンプの同順位を解消する

同じ顧客の2件の注文が同じorder_dateになることがあります。同じ日である場合や、タイムスタンプが完全に同じ場合です。タイブレーカーがなければ、どちらがrn = 1になるかは任意であり、実行ごとに変わる可能性があります。

order_id DESCのような一意な第2キーを追加して、最新の行を決定的に選べるようにしてください。面接官は、このエッジケースに気づいたかどうかを特に確認します。

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

相関サブクエリによる代替方法

ウィンドウ関数が一般的に使われるようになる前は、キーごとの最新行を求めるために相関サブクエリを使っていました。同じキーを持つ別の行で、より新しい日付のものが存在しない行だけを残す方法です。

動作しますが、行ごとに内部クエリを実行するため、大きなテーブルでは遅く、同順位の扱いも面倒です。知識の幅を示すために触れるのはよいですが、パフォーマンスの面ではウィンドウ関数による方法を優先してください。

SELECT o.*
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

PostgresのDISTINCT ONショートカット

PostgreSQLには簡潔な記法があります。DISTINCT ON (key)は、ORDER BYに従ってキーごとに最初の行を残します。ORDER BYは同じキー列から始め、その後にタイブレーカーやタイムスタンプを指定する必要があります。

Postgresでは簡潔で高速ですが、移植性はありません。方言固有のボーナスとして紹介しつつ、移植性の高い標準解として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)のインデックスがあれば、エンジンはキーごとの最新行を効率的に読み取れます。
ウィンドウ関数による方法はテーブルを1回スキャンしますが、相関サブクエリはそうではありません。
PostgresのDISTINCT ONは同じインデックスを利用でき、単一テーブルの選択肢として最速になることがよくあります。
追記が多いイベントログでは、増分更新するマテリアライズド「最新」テーブルを検討してください。

よくある間違い

次の点に注意してください。

MAX(date)を使い、日付だけを返して行全体を返さない。
タイブレーカーを忘れ、日付が同じ場合に結果が非決定的になる。
ランキング後に条件でフィルタし、除外すべき行を選んでしまう。
「最新の1行」（ROW_NUMBER）と「最新日時が同じすべての行」（RANK）を混同する。

クイックチェック

キーごとの最新行を取得する正しいクエリを選択してください。

対話的なレッスンで答える

まとめ：キーごとの最新行

パターンはキーでPARTITION BYし、タイムスタンプの降順（および一意なタイブレーカー）でORDER BYし、rn = 1を残すことです。

MAX(date)で得られるのは日付であり、行全体ではありません。
結果を決定的にするため、必ずタイブレーカーを追加してください。
最新のタイムスタンプで同順位になったすべての行が必要な場合は、RANK() = 1を使います。
条件によるフィルタは、ランキング前の内部クエリに置きます。
PostgresのDISTINCT ONは、簡潔で高速な方言固有の代替方法です。

無料で開始

AI チューターと学ぶ SQL — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース: 30
レッスン: 120

無料で学習を始めるコース全体を表示

よくある質問