Coding Interview Prep · レッスン

ROW_NUMBERでグループごとに上位N行を取得する

Q: 「ROW_NUMBERでグループごとに上位N行を取得する」レッスンは無料ですか？

はい。「ROW_NUMBERでグループごとに上位N行を取得する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。

Q: 「ROW_NUMBERでグループごとに上位N行を取得する」で何を学びますか？

「カテゴリごとの上位3件」に使う、パーティション化と順位付けの定番パターンを学びます。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Q: Coding Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

Q: 「ROW_NUMBERでグループごとに上位N行を取得する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

Q: このCoding Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

「カテゴリごとの上位3件」に使う、パーティション化と順位付けの定番パターンを学びます。

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

グループごとのTop-Nに関する質問

SQLの面接で非常によく出る質問に、次のような一見簡単なものがあります。「各部署で給与が最も高い従業員を上位3人返してください。」ここですぐにLIMITを使おうとすると不正解になります。LIMITは各グループではなく、結果セット全体を制限するためです。

面接官が確認しているのは、ウィンドウ関数を理解しているかどうかです。定番の解答は、各グループ内で行に番号を付け、その番号がN以下の行だけを残す方法です。このレッスンでは、そのパターンを段階的に学びます。

LIMITでは解決できない理由

次のクエリを書いたとします。これは部署ごとに3行ではなく、テーブル全体で合計3行だけを返します。

LIMIT（またはTOPやFETCH FIRST）は、最終的な結果セットに対して機能します。標準SQLには、グループごとのLIMITはありません。面接官の前でグループごとの問題に対してLIMIT 3を提案すると、パーティションを十分に理解していないと判断される可能性があります。

-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

ROW_NUMBERを使う

ROW_NUMBER()は、指定した順序に従って各行に重複のない連続した整数を割り当てるウィンドウ関数です。単独で使うと、結果全体に対して番号を付けます。

ここで重要なのがPARTITION BYです。これにより、グループごとに番号が1から振り直されます。PARTITION BY departmentとORDER BY salary DESCを組み合わせると、各部署で給与順に1、2、3、…という独自の順位が付けられます。

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rn
FROM employees;

番号付きの結果を読む

前のクエリを実行すると、すべての行にrnの値が付与されます。各部署では、給与が最も高い行がrn = 1、次の行が2というように続きます。部署が変わると、番号は再び1から始まります。

Sales：Ana (1)、Bo (2)、Cal (3)、Dee (4)
Engineering：Eve (1)、Fin (2)、Gus (3)

これで「部署ごとの上位3人」は、単にrn <= 3の行を残すことを意味します。

WHEREではrnをフィルタリングできない

次の自然な手順はWHERE rn <= 3ですが、これは失敗します。論理的な実行順序では、ウィンドウ関数はWHERE句の後に計算されるため、WHEREが実行される時点ではエイリアスrnはまだ存在しません。

面接官はこの落とし穴をよく出題します。解決策は、サブクエリまたはCTE内でウィンドウ関数を計算し、その内側のクエリの結果を外側のクエリでフィルタリングすることです。

-- ERROR: rn does not exist in WHERE
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

定番のCTEによる解決策

番号付けをrankedという名前のCTEでラップし、外側のWHEREでフィルターを指定してCTEから選択します。これは面接官が期待する解答であり、読みやすい書き方でもあります。

次の骨格を覚えておきましょう。グループでパーティションし、指標で並べ替え、外側のクエリでrn ≤ Nをフィルタリングするという形です。数字を1つ変えるだけで、上位1件、上位5件、その他任意のN件に応用できます。

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

サブクエリ形式

面接官が古い方言を使っている場合や、サブクエリを好む場合は、同じロジックをFROM内の派生テーブルに記述できます。派生テーブルには必ずエイリアスが必要です（ここではr）。エイリアスがないと構文エラーになります。

CTE形式と派生テーブル形式は、この問題では置き換えて使えます。面接官にとって読みやすいほうを選べばよく、どちらも同じように正しい解答です。

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
) AS r
WHERE rn <= 3;

Top-1：グループごとの最上位1件

「各部署で給与が最も高い従業員を1人見つけてください」は、N = 1にするだけです。フィルターをrn = 1に設定します。

GROUP BY departmentとMAX(salary)の組み合わせではいけないのでしょうか。MAXで得られるのは給与の値だけで、その従業員の名前や入社日など、行の残りの情報は得られないためです。ROW_NUMBERなら該当する行全体を保持できるので、通常はこちらが質問の意図に合います。

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT name, department, salary, hire_date
FROM ranked
WHERE rn = 1;

決定的なタイブレーカーを追加する

ROW_NUMBERは、給与が同じ場合でも必ずN行を返します。ただし、同率の行を区別しない限り、どの行がrn = 1になるかは任意です。2人の給与が90000で、rn = 1だけを残す場合、どちらが選ばれるかは実行ごとに予測できません。

employee_idのような一意の第2ソートキーを追加すると、結果が安定し、再現可能になります。面接官は、促されなくても決定性について言及できる候補者を高く評価します。

ROW_NUMBER() OVER (
  PARTITION BY department
  ORDER BY salary DESC, employee_id ASC
) AS rn

具体的な例題

region、product、revenueを持つsalesテーブルがあるとします。地域ごとに売上高が上位2つの商品を返してください。方法は同じで、regionでパーティションし、revenue DESCで並べ替え、rn <= 2を残します。

変わるのはパーティション列と指標列だけであることに注目してください。ビジネス領域が変わっても、構造は同じです。

WITH ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY region ORDER BY revenue DESC, product
         ) AS rn
  FROM sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

パフォーマンスとアピールポイント

正解を示すだけでなく、さらにアピールするには次の点に触れましょう。

(department, salary DESC)のインデックスがあると、エンジンはパーティションごとに順序付けられた行を効率よく生成できます。
ウィンドウ関数を使う方法はテーブルを1回スキャンするため、行ごとに実行される相関サブクエリよりも大幅に効率的です。
非常に大規模なTop-N-of-1のケースでは、DISTINCT ON（Postgres）をショートカットとしてサポートするエンジンもありますが、ROW_NUMBERが移植性の高い標準的な方法です。

必ずタイブレーカーを明示し、求められているNを確認してください。

確認問題

グループごとのTop-Nパターンを理解できているか確認しましょう。

まとめ：グループごとのTop-N

パターンを一言でまとめると、グループでパーティションし、指標で並べ替え、ROW_NUMBERを割り当て、外側のクエリでrn ≤ Nを残すということです。

LIMITは結果セット全体を制限するもので、グループごとには機能しません。
ウィンドウ関数のエイリアスはWHEREでフィルタリングできないため、CTEまたはサブクエリでラップします。
決定的な結果にするには、一意のタイブレーカーを追加します。
Top-1では、MAXとGROUP BYの組み合わせとは異なり、該当する行全体を保持できます。

数字を1つ変えるだけで、同じクエリを上位1件、上位5件、その他任意のN件に使えます。

よくある質問