SQL Interview Prep · レッスン

部署ごとの最高給与者

Q: 「部署ごとの最高給与者」レッスンは無料ですか？

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

Q: 「部署ごとの最高給与者」で何を学びますか？

パーティション化と順位付けを組み合わせて、グループごとの上位N件の給与問題を解きます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

Q: 「部署ごとの最高給与者」レッスンにはどのくらい時間がかかりますか？

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

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

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

パーティション化と順位付けを組み合わせて、グループごとの上位N件の給与問題を解きます。

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

グローバルランキングからグループごとのランキングへ

次のステップは、「各部門で最も給与が高い従業員を見つけてください」です。これはランキングとグループ化を組み合わせた、確実に中級レベルで問われる問題です。

id、name、department_id、salaryを持つemployeeテーブルがあるとします。全体の最大値ではなく、部門ごとに最高給与の従業員を1人（同率なら複数人）取得したいとします。

ここで新しく使う重要な機能がPARTITION BYです。これにより、各部門内でランキングが最初から始まります。

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BYでランキングをリセットする

ウィンドウにPARTITION BY department_idを追加すると、データベースは各部門内で独立してランキングを計算します。

各部門はそれぞれランク1から始まります。そのため、部門1の最高給与の従業員も、部門5の最高給与の従業員もランク1になります。パーティション分割をしなければ、全体で最も高い給与だけがランク1になります。

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

ランク1への絞り込み

最高給与の従業員だけを残すには、ランク付けしたクエリを包み、ランク1でフィルタリングします。いつものように、ウィンドウ関数はサブクエリまたはCTEで計算してからでなければ、その結果でフィルタリングできません。

ここでDENSE_RANK（またはRANK）を使うと、ある部門で最高給与が2人の従業員で同率になった場合、両方が返されます。これは通常、「最も給与が高い従業員」の正しい解釈です。

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

1件だけにしたい場合のROW_NUMBER

同率の従業員がいても、部門ごとに必ず1件だけ返すよう面接官から求められることがあります。その場合はROW_NUMBERを使い、idが最小のものなど、決定的なタイブレーカーを追加します。

タイブレーカーがないと、同率の場合の選択が任意になり、結果が非決定的になります。, id ASCを追加すると、同じ条件で常に同じ行が選ばれます。

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

ここでのDENSE_RANKとROW_NUMBERとRANKの比較

正確な問題文に合わせて選択します。

DENSE_RANK = 1: 部門ごとに、給与が最高額で同率の従業員をすべて返します。
RANK = 1: 最高順位に限ればDENSE_RANKと同じです（順位1より下でのみ順位の欠番が影響します）。
ROW_NUMBER = 1: 部門ごとに従業員を必ず1人だけ返し、同率の場合はORDER BYで指定した順序で決定します。

どれを選んだのか、そしてなぜ選んだのかを説明できることが、面接官の評価ポイントです。

ウィンドウ関数以前の相関サブクエリによる方法

ウィンドウ関数が登場する前は、相関サブクエリが標準的な解決方法でした。同じ部門に、より高い給与の従業員がいない行だけを残します。

この方法では、最高給与が同率の従業員を自然にすべて返せます。移植性はありますが、オプティマイザーが書き換えない限り、内側のMAXが外側の各行に対して評価されるため、遅くなることがあります。

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

GROUP BYしてから結合する方法

もう1つの移植性の高いパターンは、GROUP BYで部門ごとの最高給与を計算し、その後で結合して該当する従業員を取得する方法です。

効率がよく、わかりやすい方法です。結合によって、部門の最高給与と一致する従業員がすべて戻されるため、同率も保持されます。

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

部門ごとの上位N件

このパターンは、「部門ごとに給与上位3人」のような場合にも、新しい考え方を加えずに拡張できます。フィルターを範囲に変更するだけです。

DENSE_RANKでは、rnk <= 3により、給与額の異なる上位3段階を返します（同率がある場合は3行を超える可能性があります）。ROW_NUMBERでは、rn <= 3により、部門ごとにちょうど3行を返します。

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

実例

部門1：Ana 120、Bob 120、Cara 90。部門2：Dan 200、Eve 150。

DENSE_RANK = 1: 部門1からAna（120）とBob（120）、部門2からDan（200）を返します。合計3行です。
idをタイブレーカーにしたROW_NUMBER = 1: AnaとBobのうちidが小さい方1人と、Danを返します。合計2行です。

同じデータでも、関数によって行数が変わります。問題文に合うものを選んでください。

部門を含め、名前を結合する

面接では、departmentテーブルを追加して部門名を求めるよう指示されることもよくあります。その場合は、ランキングを計算した後で結合するだけです。

ランキングはemployeeテーブル上で計算し、最後に参照テーブルを結合します。こうすれば、パーティション分割が適切な粒度で行われます。

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

避けるべき落とし穴

グループごとのランキングでよくある間違いは次のとおりです。

PARTITION BYを忘れて全体でランキングし、会社全体で最高給与の従業員だけを返してしまう。
同率の従業員もすべて表示する意図の問題でROW_NUMBERを使い、最高給与の従業員を意図せず除外してしまう。
ウィンドウ関数をラップせず、直接WHEREに記述しようとする。
ランキングの前に部門テーブルを結合し、パーティションの粒度を誤って変更してしまう。

クイックチェック

要件に合うランキング関数を選んでください。

まとめ

部門ごとの最高給与を求めるには、全体のランキングパターンにPARTITION BY department_idを加えます。

DENSE_RANK = 1は、部門ごとに最高給与で同率の従業員をすべて返します。
タイブレーカーを指定したROW_NUMBER = 1は、部門ごとに必ず1人だけ返します。
移植性の高い代替方法として、部門ごとのMAXを使う相関サブクエリや、GROUP BYで求めた最高給与をテーブルに再結合する方法があります。

上位N件に拡張するには、= 1を<= Nに変更します。同率をどのように扱うか、声に出して説明してください。

よくある質問