0Pricing
SQL Interview Prep · レッスン

SELECT列に関するGROUP BYのルール

集計されないすべての列をGROUP BYに含める必要がある理由と、ONLY_FULL_GROUP_BYモードを学びます。

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

面接官がGROUP BYから始める理由

GROUP BYは、面接で初級者と中級者の差が表れるポイントです。面接官が最もよく確認する基本ルールは、SELECTリストの各列は、集約関数の中に含めるか、GROUP BYに列挙する必要があるということです。

このルールを破ると、複数の行を持つグループについて、どの値を表示すべきかエンジンが判断できません。面接官は、グループとは実際には何かを理解しているか確認するために、まさにこの間違いを仕掛けます。

グループとは実際には何か

GROUP BYは、複数の行を異なるキーごとに1行へまとめます。グループ化した後、エンジンには個々の行は残っていません。存在するのはグループごとに1行の集約結果だけです。

  • GROUP BYで指定した列には、グループごとに明確な値が1つあります。
  • COUNT、SUM、AVGなどの集約関数は、多数の値を1つにまとめます。
  • それ以外の集約されていない列は曖昧です。多数ある値のうち、どれを表示すべきなのでしょうか。
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

典型的なエラー

面接官が好んで出すバグの例です。departmentでグループ化しながら、GROUP BYに含まれていない集約対象外の列nameも選択しています。

各部署には複数の従業員がいるため、グループごとに複数の名前があります。エンジンは1つを選べないので、標準SQLではクエリを拒否します。

-- ERROR: name is not in GROUP BY and not aggregated
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

修正方法は2つ

正しい修正方法は2つあります。面接官は、どちらも異なる結果を返すことを理解しているか確認したいと考えています。

  • より細かいグループ化（部署と名前ごとに1行）が本当に必要なら、列をGROUP BYに追加する。
  • 既存のグループごとに1つの値が必要なら、MAX(name)やCOUNT(name)のような集約関数で囲む。
-- Finer grouping
SELECT department, name, COUNT(*) AS rows_for_person
FROM employees
GROUP BY department, name;

MySQLのONLY_FULL_GROUP_BY

よくある落とし穴です。古いMySQLでは、グループ化されていない列の選択が許可されており、グループ内から任意の値を暗黙的に返していました。その結果、見た目は問題なさそうでも誤ったレポートが生成されていました。

現在のMySQLでは、標準ルールを適用するONLY_FULL_GROUP_BYがデフォルトで有効になっています。Postgres、SQL Server、Oracleでは以前からこのルールが適用されています。「古いサーバーでは動いたのに、今は失敗する」と聞かれたら、これが答えです。

-- Legal under ONLY_FULL_GROUP_BY because every
-- selected column is grouped or aggregated
SELECT department, MAX(hire_date) AS latest_hire
FROM employees
GROUP BY department;

関数従属性の例外

面接官が理解の深さを確認するために使う細かな点があります。テーブルの主キーでグループ化すると、そのテーブルの他のすべての列はキーに関数従属するため、グループごとに値が正確に1つになります。

Postgresと最新のMySQLでは、従属する列をGROUP BYに列挙せずに選択できます。グループ化したキーによってそれらの列が一意に決まるため、曖昧さがないからです。

-- Legal: id is the PK, so name is determined by it
SELECT e.id, e.name, COUNT(o.id) AS orders
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
GROUP BY e.id;

例：地域別の売上

地域ごとの売上合計をレポートするとします。グループ化キーはregionで、指標はSUM(amount)です。それ以外の列はすべて集約するか、削除する必要があります。

この形がいかに明快かに注目してください。地域ごとに1行となり、それぞれに合計値が1つ含まれます。集約レポートはすべてこの形になります。

SELECT region,
       SUM(amount)   AS total_sales,
       COUNT(*)      AS num_orders,
       AVG(amount)   AS avg_order
FROM sales
GROUP BY region;

詳細行と集計結果を組み合わせる

ひっかけ問題です。「各注文の金額を、その地域の合計と並べて表示する」場合、通常のGROUP BYではできません。グループ化によって個々の行が失われるためです。

正解は、ウィンドウ関数（SUM(amount) OVER (PARTITION BY region)）を使うか、グループ化したサブクエリを元のテーブルに再結合することです。ここでGROUP BYが不適切なツールだと認識できるかが、この問題のポイントです。

-- Detail rows kept, region total added per row
SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

GROUP BYとSELECTのエイリアス

SELECTで定義したエイリアスをGROUP BYで使えるでしょうか。方言によって異なり、この一貫性のなさこそが面接官の確認するポイントです。

  • MySQLとPostgres: SELECTのエイリアスによるグループ化を許可します。
  • SQL ServerとOracle: 許可しません。完全な式を繰り返す必要があります。

移植性を考えた答えは、GROUP BYで式を繰り返すことです。これならどの環境でも動作します。

-- Portable: repeat the expression rather than the alias
SELECT EXTRACT(YEAR FROM order_date) AS yr, COUNT(*)
FROM sales
GROUP BY EXTRACT(YEAR FROM order_date);

一意性を得るためのDISTINCTとGROUP BYの違い

集約を行わず、重複しない組み合わせだけが必要なら、集約関数なしのGROUP BYはDISTINCTと同じように動作します。面接官から、どちらのほうが明確かを聞かれることがあります。

「重複しない行が欲しい」という意図を表すにはDISTINCTを使ってください。GROUP BYは、集約値も計算する場合に使います。結果は同じでも、読み手に伝わる意図が異なります。

-- These return the same rows
SELECT DISTINCT department, role FROM employees;
SELECT department, role FROM employees GROUP BY department, role;

面接での説明方法

GROUP BYの問題に取り組むときは、次のルールを声に出して説明してください。「選択する各列は、グループ化キーであるか、集約関数で囲まれています。グループ化するとキーごとに1行だけが残るためです」

続いて、グループ化キーと集計値を示し、グループ化されていない列が残っていないことを確認します。このように構造化して答えると、クエリを書く前から中級者としての力量を示せます。

クイックチェック

GROUP BYの基本ルールを理解できているか確認しましょう。

まとめ

ルール：SELECTに指定するすべての列は、グループ化キーであるか、集約関数の対象でなければなりません。理由：GROUP BYはキーごとに1行を残すため、グループ化されていない生の列はどの値を選ぶべきか曖昧になるからです。

  • ルール違反を直すには、その列でグループ化するか、集約関数を適用します。
  • MySQLの古い動作では任意の値が返されていましたが、ONLY_FULL_GROUP_BYによって標準に準拠した動作が強制されます。
  • 主キーによる関数従属性は、唯一の合法的な例外です。
  • グループの合計値と明細行を並べて表示するには、GROUP BYではなくウィンドウ関数を使います。

よくある質問

「SELECT列に関するGROUP BYのルール」レッスンは無料ですか？

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

「SELECT列に関するGROUP BYのルール」で何を学びますか？

集計されないすべての列をGROUP BYに含める必要がある理由と、ONLY_FULL_GROUP_BYモードを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「SELECT列に関するGROUP BYのルール」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

  1. SELECT列に関するGROUP BYのルール
  2. HAVINGとWHEREの違い
  3. 複数列と式でグループ化する
  4. グループを数えてフィルタリングする
← SQL Interview Prepに戻る