GROUP BYなしでグループごとに集計する
相関サブクエリを使い、詳細行と並べてグループの最大値を計算します。
「GROUP BYなしでグループごとに集計する」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。
詳細行と集約値の組み合わせ
面接でよく出る質問に、「各行を、その行が属するグループの集約値と一緒に表示してください」というものがあります。たとえば、各従業員と同じ行に、その従業員の部署の最高給与を表示する課題です。
通常の GROUP BY は行をまとめるため、従業員ごとの詳細を残せません。詳細行とグループ単位の値を同時に取得する必要があります。
相関サブクエリを使えば、行をまとめることなく、各詳細行についてグループの集約値を計算できます。
単純なGROUP BYではうまくいかない理由
SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id と書くと、部署ごとに1行が返され、個々の従業員名が失われます。
name を GROUP BY に追加せず SELECT に追加すると、典型的な 「列は GROUP BY に含める必要があります」 というエラーになります。
面接官が確認しているのは、GROUP BY によって行数が減ることを理解しているかどうかです。詳細行を残すには、別の方法で集約値を計算します。
相関サブクエリで解決
グループの集約値を、相関サブクエリとして SELECT リストに配置します。従業員の各行が、同じ従業員の部署に限定された内側の MAX を実行します。
e2.dept_id = e1.dept_id という相関によって集約値が正しいグループに結び付けられ、外側のクエリは従業員ごとに1行を返し続けます。
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;各行とグループの比較
グループの集約値をクエリに組み込めば、各行とその値を比較できます。よくある質問は、「部署の平均を上回る給与を得ている従業員を見つけてください」というものです。
ここでは相関する AVG を WHERE に置いているため、各従業員が自分の部署の平均と比較されます。
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);グループとの差分を計算する
各行がグループの集約値からどれだけ離れているかを表示することもできます。相関する平均値を引けば、行ごとの差分が得られます。
同じ相関サブクエリを複数の SELECT 式で再利用できる点に注目してください。クエリ内に現れるたびに、エンジンは行ごとに評価します。
SELECT e1.name,
e1.salary,
e1.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;グループごとの最高給与者を見つける
部署ごとに最高給与の従業員だけを返すには、各給与を相関する MAX と比較し、一致する行を残します。
このパターンでは同率も返されます。2人の従業員が部署の最高給与を共有している場合、両方が表示されます。この同率の扱いは、面接官がよく続けて尋ねるポイントです。
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);ウィンドウ関数による代替
現代的な SQL には、より簡潔なツールとしてウィンドウ関数があります。MAX(salary) OVER (PARTITION BY dept_id) は、行をまとめず、相関による再スキャンも行わずに、グループの集約値を計算します。
両方の解法を示し、ウィンドウ関数のほうが通常は優れた性能を発揮すると説明できると、面接官から高く評価されます。テーブルを1回スキャンするだけだからです。
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;相関サブクエリとウィンドウ関数のトレードオフ
どちらの方法も同じ形の結果を返しますが、違いがあります。
- 相関サブクエリ：移植性が高く、非常に古いエンジンでも動作しますが、行ごとに再評価されます。
- ウィンドウ関数：1回のパスで処理でき、大きなテーブルでははるかに高速ですが、SQL のウィンドウ関数に対応している必要があります。
どちらを選ぶか、そしてその理由を説明してください。小さなテーブルに対する一度限りの処理ならどちらでも問題ありませんが、大規模な分析ではウィンドウ関数を優先します。
実例：顧客平均を上回る注文
このパターンを注文に適用してみましょう。注文した顧客自身の平均注文額を上回る注文を表示します。
o2.customer_id = o1.customer_id によって相関する AVG の対象を限定し、各注文にその顧客固有の基準値を与えています。
SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);NULLと空のグループに注意する
グループの行が1行だけの場合、その平均はその行の値と等しくなります。そのため salary > avg は偽になり、その行は除外されます。この境界ケースには、こちらから先に触れておきましょう。
また、NULL の給与は AVG と MAX では無視されます。これは SQL の集約関数の仕様です。グループ内のすべての値が NULL の場合、集約値も NULL になり、比較結果は UNKNOWN となって行が除外されます。こうしたケースを予測できることが、回答の周到さを示します。
グループ内の順位を数える
相関する COUNT を使って、行のグループ内順位を表現できます。各従業員の部署内での給与順位を求めるには、その従業員より高い給与を得ている同僚の人数を数えます。
順位1は最高給与者を意味します。1を加えることで、高い給与の人数を1始まりの順位に変換でき、相関によって対象を同じ部署に限定できます。
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;理解度チェック
この課題で、単純な GROUP BY より相関サブクエリが優れている理由を選んでください。
まとめ：GROUP BYを使わないグループ単位の集約
重要なポイント：
- 相関サブクエリを使うと、行をまとめることなく、すべての詳細行にグループ単位の集約値を付加できます。
- 集約値を表示するには SELECT で使い、各行をそのグループと比較するには WHERE で使います。
= MAX(...)パターンは、同率の最高値を持つすべての行を返します。PARTITION BYを使うウィンドウ関数でも同じことを1回のパスで実行でき、通常はこちらのほうが拡張性に優れています。
面接では両方の解法を示し、選んだほうの理由を説明してください。
よくある質問
「GROUP BYなしでグループごとに集計する」レッスンは無料ですか？
はい。「GROUP BYなしでグループごとに集計する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。
「GROUP BYなしでグループごとに集計する」で何を学びますか？
相関サブクエリを使い、詳細行と並べてグループの最大値を計算します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Coding Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。
「GROUP BYなしでグループごとに集計する」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このCoding Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 相関サブクエリの構造
- GROUP BYなしでグループごとに集計する
- 相関EXISTSとNOT EXISTS
- 相関サブクエリをJOINに書き換える