SQL: グループ化クエリ

1. 🌍 現実世界の例え

教師が成績表の束を持っている場面を想像してください：

各生徒の平均点 → 生徒ごとにグループ化して平均を計算
各科目の最高点 → 科目ごとにグループ化して最大値を検索
各クラスの合格率 → クラスごとにグループ化して比率を計算

これがGROUP BYの役割です：まずデータをグループに分割し、各グループに対して統計を実行します。前のレッスンで学んだ集約関数はテーブル全体に対して単一の値を計算しますが、GROUP BYはカテゴリごとに値を計算します。

2. 🎯 コアコンセプト

(1) GROUP BYの基本構文

SQL

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

実行ロジック：まずGROUP BYカラムでグループ化し、各グループに集約関数を適用します。

SQL

-- 各部門に何人の従業員がいますか？
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

出力：

TEXT 📖 参照専用

department_id  headcount
-------------  ---------
1              3
2              2
3              2
NULL           1

💡 ルール：SELECT内のカラムはGROUP BYに含まれるか、集約関数で囲まれている必要があります。それ以外の場合、セマンティクスが曖昧になり、データベースがエラーを報告します。

(2) グループ後の集約

GROUP BYのコアパターンは：グループ化 → 集約です。

SQL

-- 部門ごとの平均給与
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

一般的な集約の組み合わせ：

SQL

SELECT department_id,
       COUNT(*) AS headcount,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees
GROUP BY department_id;

(3) 複数カラムのグループ化

複数のカラムでグループ化できます — すべての指定カラムの値が一致する行が同じグループと見なされます：

SQL

-- 部門とステータス別の従業員数
SELECT department_id, status, COUNT(*) AS headcount
FROM employees
GROUP BY department_id, status;

出力：

TEXT 📖 参照専用

department_id  status    headcount
-------------  --------  ---------
1              active    3
2              active    1
2              inactive  1
3              active    2

💡 理解：複数カラムのグループ化は、Excelの多段階小計に似ています。まず部門でグループ化し、次にステータスでグループ化します — 各部門+ステータスの組み合わせが1つのグループになります。

(4) GROUP BY + ORDER BY

グループ化後に並べ替えできます：

SQL

-- 部門別の平均給与、高い順に並べ替え
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

(5) WHERE vs HAVING — 行のフィルタリング vs グループのフィルタリング

これはこのレッスンで最も重要な概念の1つです：

特徴	WHERE	HAVING
フィルタリング対象	行（グループ化前）	グループ（グループ化後）
実行タイミング	`GROUP BY`の前	`GROUP BY`の後
集約関数の使用	❌ 不可	✅ 可能
使用方法	単独で使用可能	`GROUP BY`と併用必須

SQL

-- WHERE：まず行をフィルタリングしてからグループ化
-- 従業員が3人以上の部門を見つける
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE salary > 5000       -- まず給与5000未満の行を除外
GROUP BY department_id;

-- HAVING：まずグループ化してからグループをフィルタリング
-- 従業員が3人以上の部門を見つける
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3;     -- メンバーが3人未満のグループを除外

SQL

-- WHERE + HAVINGの組み合わせ
-- 2024年に採用された従業員がおり、平均給与が10000より高い部門を見つける
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'   -- まず行をフィルタリング
GROUP BY department_id
HAVING AVG(salary) > 10000;      -- その後グループをフィルタリング

(6) 実行順序

SQLクエリの記述順序と実行順序は異なります：

TEXT 📖 参照専用

記述順序：  SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
実行順序： FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

実行順序を理解することは、正しいSQLを書くために不可欠です：

FROM — データソースを決定
WHERE — 行ごとにフィルタリング
GROUP BY — グループ化
HAVING — グループをフィルタリング
SELECT — カラムを選択し、式を計算
ORDER BY — 並べ替え

💡 重要：これが集約関数がWHEREで使用できない理由です — WHEREが実行される時点ではGROUP BYがまだ実行されていないため、集約結果が存在しないからです。

3. 📝 基本構文

SQL

-- 基本的なグループ化
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

-- 複数カラムのグループ化
SELECT column1, column2, aggregate_function(column_name)
FROM table_name
GROUP BY column1, column2;

-- WHERE + GROUP BY + HAVING
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE row_level_condition
GROUP BY column_name
HAVING aggregate_level_condition
ORDER BY sort_column;

💡 ヒント：

GROUP BYのカラムはSELECTのカラム順と一致する必要はありませんが、非集約カラムはすべて含める必要があります

HAVINGは集約関数のエイリアスを使用できます（MySQLとPostgreSQLでサポート）が、標準SQLでは完全な式を記述する必要があります

NULL値はGROUP BYで一緒にグループ化されます

4. 📌 例

(1) サンプル：部門別の従業員統計

SQL

SELECT d.department_name,
       COUNT(e.employee_id) AS headcount,
       AVG(e.salary) AS avg_salary,
       SUM(e.salary) AS total_salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY total_salary DESC;

▶ 試してみよう

出力：

TEXT 📖 参照専用

department_name  headcount  avg_salary  total_salary
---------------  ---------  ----------  ------------
Technology       3          17666.67    53000.00
Finance          2          13500.00    27000.00
Marketing        2          11500.00    23000.00
Administration   1          9000.00     9000.00

💡 解説：LEFT JOINを使用することで、従業員がいない部門も表示されます（従業員数は0）。GROUP BY department_nameは部門名でグループ化します。

(2) サンプル：WHERE + GROUP BY + HAVINGの組み合わせ

SQL

-- 2024年に採用された従業員の中で、平均給与が12000より高い部門を見つける
SELECT department_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'
GROUP BY department_id
HAVING AVG(salary) > 12000
ORDER BY avg_salary DESC;

▶ 試してみよう

出力：

TEXT 📖 参照専用

department_id  headcount  avg_salary
-------------  ---------  ----------
1              2          18500.00
3              1          14000.00

💡 実行プロセス：

WHERE hire_date >= '2024-01-01' — まず2024年に採用された従業員をフィルタリング

GROUP BY department_id — 部門でグループ化

HAVING AVG(salary) > 12000 — 平均給与が12000より高いグループのみ保持

ORDER BY avg_salary DESC — 平均給与の降順で並べ替え

(3) サンプル：複数カラムのグループ化 + 複雑な条件

SQL

-- 部門とステータス別の従業員統計、メンバーが2人以上のグループのみ表示
SELECT d.department_name,
       e.status,
       COUNT(*) AS headcount,
       MIN(e.salary) AS lowest_salary,
       MAX(e.salary) AS highest_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 0
GROUP BY d.department_name, e.status
HAVING COUNT(*) >= 2
ORDER BY d.department_name, headcount DESC;

▶ 試してみよう

出力：

TEXT 📖 参照専用

department_name  status  headcount  lowest_salary  highest_salary
---------------  ------  ---------  -------------  --------------
Finance          active  2          12000.00       14000.00
Marketing        active  2          10000.00       12000.00
Technology       active  3          15000.00       20000.00

💡 解説：まずWHEREで給与がゼロの行を除外し、部門+ステータスでグループ化し、HAVINGでメンバーが2人未満のグループを除外し、最後に並べ替えます。

5. 🎬 実践シナリオ

(1) シナリオ1：売上レポート — 月次注文統計

月次売上レポートを生成し、注文が3件以上の月のみ表示します。

SQL

SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_amount,
    AVG(total_amount) AS avg_order_amount
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
HAVING COUNT(*) >= 3
ORDER BY year, month;

💡 アプローチ：WHEREで完了および発送済みの注文をフィルタリングし、年月でグループ化して統計を出し、HAVINGで注文が3件未満の月を除外します。

(2) シナリオ2：人事分析 — 高給与部門の検索

会社全体の平均よりも平均給与が高い部門を検索します。

SQL

SELECT d.department_name,
       COUNT(e.employee_id) AS headcount,
       AVG(e.salary) AS dept_avg_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees)
ORDER BY dept_avg_salary DESC;

💡 アプローチ：HAVINGではサブクエリを使用できます。まず各部門の平均給与を計算し、会社全体の平均と比較します。これは集約関数とサブクエリの組み合わせた応用です。

▶ サンプル

SQL

SELECT category, COUNT(*) AS cnt FROM products GROUP BY category HAVING cnt > 3;

▶ 試してみよう

❓ よくある質問

Q SELECTのカラムはGROUP BYに含める必要がありますか？

Q HAVINGでSELECTのエイリアスを使用できますか？

Q GROUP BYでNULL値はどのように処理されますか？

Q WHEREとHAVINGを同時に使用できますか？

📖 まとめ

GROUP BYはデータをグループに分割し、各グループに集約関数を適用します
SELECT内の非集約カラムはGROUP BYに含める必要があります
複数カラムのグループ化：複数のカラム値の組み合わせでグループ化
WHERE：グループ化前に行をフィルタリング、集約関数は使用不可
HAVING：グループ化後にグループをフィルタリング、集約関数が使用可能
実行順序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
NULL値は一緒にグループ化されます

📝 練習問題

演習1（⭐）：各部門の従業員数と平均給与をクエリし、平均給与の高い順に並べ替えてください。

演習2（⭐⭐）：2024年以降に採用された各部門の従業員数をクエリし、従業員が2人以上の部門のみ表示してください。

演習3（⭐⭐⭐）：各部門の最も給与の高い従業員をクエリしてください。部門名、従業員名、給与を表示してください。ヒント：サブクエリまたはROW_NUMBER()ウィンドウ関数（後述）を使用して実現できます。

6. 次のレッスン

👉 15-advanced-functions - 高度な関数：SQLの高度な関数を学び、文字列処理、数値計算、日付操作、その他の実用的なスキルをマスターしましょう！

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて