0Pricing
Coding Interview Prep · レッスン

2番目に高い給与を求める5つの方法

サブクエリ、LIMIT/OFFSET、ウィンドウ関数による解法を比較します。

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

誰もが聞かれる質問

「2番目に高い給与を見つけてください」は、SQL面接で最も頻繁に出される質問です。正解が複数あり、いくつかの微妙な落とし穴もあるため、面接官はこの質問を好んで出します。

idとsalaryの列を持つemployeeテーブルを想定します。求めるのは、重複を除いた給与額のうち2番目に高い値です。

  • 給与が300、200、200、100の場合、答えは2番目の行ではなく200です。
  • 2番目に異なる給与額が存在しない場合、通常、期待される答えはNULLです。

続くシーンでは、これを5通りの方法で解き、それぞれがどのような場面で優れているかを説明します。

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

方法1：MAX未満の値に対するMAX

最も直感的な解決方法は、全体の最大値より厳密に小さい給与のうち、最大のものを求めることです。

これはほぼ英語の文章のように読め、すべてのSQL方言で動作します。内部サブクエリで最大値を見つけ、外側のMAXでそれより小さい値の最大値を求めます。

ポイント：2番目に異なる給与額が存在しない場合、外側のMAXは0行を集約して自動的にNULLを返します。この無料で得られるNULLこそ、面接官が求めている結果です。

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

サブクエリが重複を処理できる理由

方法1ではDISTINCTを一度も使っていないのに、重複を正しく処理できていることに注目してください。

3人が200で、最高給与が300だとします。内側のクエリは300を返します。外側のフィルターは300未満のすべての行を残し、それらに対するMAXは、200が何件あっても200になります。

ここが重要なポイントです。集約関数が重複を自動的にまとめてくれます。集約関数がすでに正しく処理してくれるのに、多くの候補者はDISTINCTを使って必要以上に複雑にしてしまいます。

方法2: OFFSET付きLIMIT

MySQLとPostgreSQLでは、重複のない給与を降順に並べ、先頭の1件をスキップできます。

  • OFFSET 1は最高給与をスキップします。
  • LIMIT 1は次の1件だけを残します。

ここではDISTINCTが不可欠です。そうしないと、最高給与が重複している場合、OFFSET 1は本当の2番目の給与ではなく、最高給与の重複行を指してしまいます。

落とし穴: 2番目に異なる値が存在しない場合、このクエリはNULLではなく0行を返します。このエッジケースはレッスン4で解決します。

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

方法3: SQL ServerとOracleのFETCH

SQL Serverと現行のOracleはLIMIT ... OFFSETをサポートしていません。代わりに、ANSI標準のOFFSET ... FETCH構文を使用します。

ロジックは方法2と同じです。重複のない給与を降順に並べ、1行スキップして1行取得します。方言ごとの書き方を知っていることは、実務経験があることを面接官に示せます。

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

方法4: DENSE_RANKウィンドウ関数

現代的でスケーラブルな方法では、ウィンドウ関数を使用します。DENSE_RANKは最高給与にランク1、次に異なる給与にランク2を割り当て、同率の給与には同じランクを付け、順位を飛ばしません。

サブクエリでランクを計算し、外側のクエリでランク2に絞り込みます。ウィンドウ関数はWHEREで直接フィルタリングできないため、サブクエリで包む必要があることを覚えておいてください。

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

RANKやROW_NUMBERではなくDENSE_RANKを使う理由

「異なる値」を扱う場合、ランキング関数の選択が重要です。

  • ROW_NUMBERはすべての行に一意の番号を付けます。そのため、300の人が2人いると行1と行2になり、ランク2が最高給与の重複になります。これは誤りです。
  • RANKは同率の後で順位を飛ばします。300が2件あると両方がランク1になり、次の給与はランク3になります。そのため、ランク2では見つかりません。これも誤りです。
  • DENSE_RANKは同率に同じランクを付け、順位を飛ばしません。そのため、ランク2は常に2番目に異なる給与になります。これが正しい方法です。

方法5: 相関サブクエリによるカウント

ウィンドウ関数が登場する前からある定番の方法です。ある給与がN番目に高いのは、その給与より厳密に高い、異なる給与がちょうどN-1個ある場合です。

2番目に高い給与を求めるには、その給与より高い異なる給与がちょうど1つ必要です。これは洗練された方法ですが、内側のカウントが外側の各行に対して実行されるため、大きなテーブルでは遅くなることがあります。

カウントをN - 1に変更するだけでN番目に高い給与へ簡単に一般化できます。そのため、面接官はこの方法を知っているか確認したがります。

SELECT salary AS second_highest
FROM employee e
WHERE 1 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
);

最初から最後までの具体例

給与が500、500、350、350、100だとします。

  • 方法1: MAXは500で、500未満の最大値は350です。答えは350です。
  • 方法4（DENSE_RANK）: 500 -> ランク1、350 -> ランク2、100 -> ランク3です。ランク2は350です。
  • 方法5: 給与350より高い異なる給与（500）はちょうど1つです。条件に一致します。答えは350です。

5つの方法はすべて、重複があっても2番目に高い異なる給与は350だという同じ結果になります。

どの方法を選ぶべきか

面接での指針:

  • まず質問を明確にする: 「重複のない給与が必要ですか。また、存在しない場合はNULLが必要ですか」と確認します。要件を確認するだけでも評価されます。
  • DENSE_RANKは最も有力な基本解答です。N番目やグループごとの処理にもきれいに一般化できます。
  • MAX未満のMAXは最も簡潔な1行の解答で、何もしなくてもNULLを返します。
  • LIMIT/OFFSETは簡潔ですが、データベース方言に依存し、エッジケースでは行を返しません。

トレードオフを声に出して説明できるかどうかが、中級者の解答と初級者の解答を分けます。

よくある間違いを避ける

面接官が仕掛ける次の落とし穴に注意してください。

  • DENSE_RANKの代わりにROW_NUMBERを使い、最高給与を2回取得してしまう。
  • 最高給与が重複している場合に、LIMIT/OFFSET版でDISTINCTを忘れる。
  • ORDER BY salary DESC LIMIT 1,1が異なる値を返すと思い込む（実際には返しません）。
  • 2番目の値ではなく、2番目の行を返してしまう。

クイックチェック

ランキング関数の選択について理解度を確認しましょう。

まとめ

2番目に高い給与を求める5つの方法を学びました。

  • MAX未満のMAX - 移植性が高く、何もしなくてもNULLを返します。
  • LIMIT/OFFSETとOFFSET/FETCH - 簡潔ですが、データベース方言に依存します。
  • DENSE_RANK - 同率を正しく処理できる、スケーラブルな基本解答です。
  • 相関カウント - 洗練されており、N番目の値にも一般化できます。

重要なポイントは、異なる値が必要かどうかを確認すること、同率を扱う場合はDENSE_RANKを優先すること、そして2番目の値が存在しない場合にNULLを返す方法と行を返さない方法を覚えておくことです。

よくある質問

「2番目に高い給与を求める5つの方法」レッスンは無料ですか？

はい。「2番目に高い給与を求める5つの方法」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。

「2番目に高い給与を求める5つの方法」で何を学びますか？

サブクエリ、LIMIT/OFFSET、ウィンドウ関数による解法を比較します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「2番目に高い給与を求める5つの方法」レッスンにはどのくらい時間がかかりますか？

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

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

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

このコースのすべてのレッスン

  1. 2番目に高い給与を求める5つの方法
  2. DENSE_RANKでn番目に高い値を求める
  3. 部署ごとの最高給与者
  4. n番目の値がない場合にNULLを返す
← Coding Interview Prepに戻る