MySQL - GROUP BY

概要

GROUP BY 句は、指定した列の値が同じ行をグループ化し、各グループごとに集約関数を適用するために使用される。

GROUP BY 句を使用することにより、部署別の平均給与、カテゴリ別の売上合計、日別の注文数等、データを集約して分析することができる。
集約関数 (COUNT、SUM、AVG、MAX、MIN 等) と組み合わせて使用することが一般的である。

GROUP BY 句は、WHERE 句の後、HAVING 句の前に記述される。
WHERE 句がグループ化前の個別行に対する条件指定であるのに対し、HAVING 句はグループ化後の集計結果に対する条件指定である。

MySQL 8.0以降では、GROUP BY の挙動にいくつかの重要な変更がある。
MySQL 5.7以前では GROUP BY による暗黙的なソートが行われていたが、MySQL 8.0以降ではこの挙動が廃止され、明示的に ORDER BY を指定する必要がある。
また、WITH ROLLUP 修飾子により小計や総計行を生成できるようになり、GROUPING() 関数でスーパーアグリゲート行を識別できる。

パフォーマンスの観点では、GROUP BY 列にインデックスを作成することで、ルースインデックススキャンやタイトインデックススキャンといった効率的な実行戦略が使用される。
インデックスがない場合は一時テーブルと filesort が使用されるため、パフォーマンスが低下する可能性がある。

基本構文

GROUP BY句の基本構文

GROUP BY句の基本構文は以下の通りである。

 # 基本構文
 SELECT column1, aggregate_function(column2)
    FROM table_name
    GROUP BY column1;
 
 # 複数列でのグループ化
 SELECT column1, column2, aggregate_function(column3)
    FROM table_name
    GROUP BY column1, column2;

GROUP BY句の実行順序は以下の通りである。

 # 実行順序
 FROM table_name          # 1. テーブルから行を取得
    WHERE condition       # 2. グループ化前のフィルタリング
    GROUP BY column1      # 3. グループ化
    HAVING condition      # 4. グループ化後のフィルタリング
    ORDER BY column1      # 5. 並び替え
    LIMIT n;              # 6. 行数制限

単一列でのグループ化の例を以下に示す。

 # 部署別の従業員数を取得
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 
 # カテゴリ別の商品数を取得
 SELECT category_id, COUNT(*) AS product_count
    FROM products
    GROUP BY category_id;
 
 # ステータス別の注文数を取得
 SELECT status, COUNT(*) AS order_count
    FROM orders
    GROUP BY status;

複数列でのグループ化の例を以下に示す。

 # 部署と役職の組み合わせごとの従業員数
 SELECT department_id, job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id, job_title;
 
 # 年と月の組み合わせごとの売上合計
 SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, SUM(amount) AS total_sales
    FROM orders
    GROUP BY YEAR(order_date), MONTH(order_date);
 
 # カテゴリとサプライヤーの組み合わせごとの商品数
 SELECT category_id, supplier_id, COUNT(*) AS product_count
    FROM products
    GROUP BY category_id, supplier_id;

WHERE句との組み合わせ

WHERE 句と GROUP BY 句を組み合わせることで、グループ化前に行をフィルタリングできる。

 # WHERE句でフィルタリングしてからグループ化
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    WHERE hire_date >= '2024-01-01'
    GROUP BY department_id;
 
 # 特定のステータスの注文のみを集計
 SELECT user_id, COUNT(*) AS completed_order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id;
 
 # 価格が1000円以上の商品のカテゴリ別平均価格
 SELECT category_id, AVG(price) AS avg_price
    FROM products
    WHERE price >= 1000
    GROUP BY category_id;

実行順序の重要性を理解する例を以下に示す。

 # 実行順序 : WHERE → GROUP BY → HAVING
 
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    WHERE hire_date >= '2024-01-01'      # 1. 2024年以降に雇用された従業員のみを対象
    GROUP BY department_id               # 2. 部署ごとにグループ化
    HAVING AVG(salary) > 50000;          # 3. 平均給与が50000以上の部署のみを抽出

式によるグループ化

列名だけでなく、関数や式の結果でグループ化することも可能である。

 # YEAR()関数による年別グループ化
 SELECT YEAR(order_date) AS year, COUNT(*) AS order_count
    FROM orders
    GROUP BY YEAR(order_date);
 
 # DATE_FORMAT()関数による月別グループ化
 SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m');
 
 # 四半期ごとのグループ化
 SELECT YEAR(order_date) AS year, QUARTER(order_date) AS quarter, SUM(amount) AS total_sales
    FROM orders
    GROUP BY YEAR(order_date), QUARTER(order_date);
 
 # 曜日別のグループ化
 SELECT DAYNAME(order_date) AS day_of_week, COUNT(*) AS order_count
    FROM orders
    GROUP BY DAYNAME(order_date);
 
 # 価格帯別のグループ化
 SELECT FLOOR(price / 1000) * 1000 AS price_range, COUNT(*) AS product_count
    FROM products
    GROUP BY FLOOR(price / 1000);

EXTRACT() 関数を使用した日時のグループ化も可能である。

 # EXTRACT()関数による年別グループ化
 SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*) AS order_count
    FROM orders
    GROUP BY EXTRACT(YEAR FROM order_date);
 
 # 年と月の抽出
 SELECT EXTRACT(YEAR FROM order_date) AS year,
        EXTRACT(MONTH FROM order_date) AS month,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

集約関数

GROUP BY句と組み合わせて使用される集約関数を以下に示す。

COUNT()関数

COUNT() 関数は、グループ内の行数をカウントする。

 # COUNT(*): 全行数をカウント (NULL含む)
 SELECT department_id, COUNT(*) AS total_employees
    FROM employees
    GROUP BY department_id;
 
 # COUNT(column): 指定列の非NULL値の数をカウント
 SELECT department_id, COUNT(manager_id) AS employees_with_manager
    FROM employees
    GROUP BY department_id;
 
 # COUNT(DISTINCT column): 重複を除外してカウント
 SELECT department_id, COUNT(DISTINCT job_title) AS unique_job_titles
    FROM employees
    GROUP BY department_id;
 
 # 複数のCOUNTを組み合わせる
 SELECT department_id,
        COUNT(*) AS total_employees,
        COUNT(manager_id) AS with_manager,
        COUNT(*) - COUNT(manager_id) AS without_manager
    FROM employees
    GROUP BY department_id;

COUNT(*) と COUNT(column) の違いに注意が必要である。

 # COUNT(*) はNULLを含む全行をカウント
 SELECT status, COUNT(*) AS total_count
    FROM orders
    GROUP BY status;
 
 # COUNT(column) は非NULL値のみをカウント
 SELECT status, COUNT(shipped_date) AS shipped_count
    FROM orders
    GROUP BY status;
 # shipped_dateがNULLの行は除外される

SUM()関数とAVG()関数

SUM() 関数は合計値を、AVG() 関数は平均値を計算する。
どちらもNULL値は計算から除外される。

 # SUM() : 合計値を計算
 SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id;
 
 # AVG() : 平均値を計算
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id;
 
 # SUMとAVGを組み合わせる
 SELECT category_id,
        SUM(amount) AS total_sales,
        AVG(amount) AS avg_sales,
        COUNT(*) AS order_count
    FROM order_items
    GROUP BY category_id;
 
 # 複数列の集計
 SELECT user_id,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order_value,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id;

NULL値の扱いを理解する例を以下に示す。

 # AVG()はNULL値を除外して計算
 SELECT department_id,
        AVG(bonus) AS avg_bonus,
        COUNT(*) AS total_employees,
        COUNT(bonus) AS employees_with_bonus
    FROM employees
    GROUP BY department_id;
 # bonusがNULLの従業員は平均計算から除外される
 
 # NULL値を0として扱う場合
 SELECT department_id,
        AVG(IFNULL(bonus, 0)) AS avg_bonus_including_zero
    FROM employees
    GROUP BY department_id;

MAX()関数とMIN()関数

MAX() 関数は最大値を、MIN() 関数は最小値を取得する。

 # MAX(): 最大値を取得
 SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id;
 
 # MIN(): 最小値を取得
 SELECT department_id, MIN(salary) AS min_salary
    FROM employees
    GROUP BY department_id;
 
 # MAXとMINを組み合わせる
 SELECT category_id,
        MAX(price) AS max_price,
        MIN(price) AS min_price,
        MAX(price) - MIN(price) AS price_range
    FROM products
    GROUP BY category_id;
 
 # 日付の最大値と最小値
 SELECT user_id,
        MIN(order_date) AS first_order_date,
        MAX(order_date) AS last_order_date
    FROM orders
    GROUP BY user_id;

文字列や日付にも使用可能である。

 # 文字列の最大値と最小値 (辞書順)
 SELECT department_id,
        MIN(name) AS first_name_alphabetically,
        MAX(name) AS last_name_alphabetically
    FROM employees
    GROUP BY department_id;
 
 # 日付の範囲
 SELECT YEAR(order_date) AS year,
        MIN(order_date) AS earliest_order,
        MAX(order_date) AS latest_order
    FROM orders
    GROUP BY YEAR(order_date);

GROUP_CONCAT()関数

GROUP_CONCAT() 関数は、グループ内の値を文字列として連結する。

 # 基本的なGROUP_CONCAT
 SELECT department_id, GROUP_CONCAT(name) AS employee_names
    FROM employees
    GROUP BY department_id;
 
 # SEPARATOR句で区切り文字を指定
 SELECT department_id, GROUP_CONCAT(name SEPARATOR ', ') AS employee_names
    FROM employees
    GROUP BY department_id;
 
 # ORDER BY句で並び順を指定
 SELECT department_id, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS employee_names
    FROM employees
    GROUP BY department_id;
 
 # DISTINCTで重複を排除
 SELECT user_id, GROUP_CONCAT(DISTINCT product_name ORDER BY product_name SEPARATOR ', ') AS products
    FROM order_items
    JOIN products ON order_items.product_id = products.id
    GROUP BY user_id;

GROUP_CONCAT() のデフォルト最大長は1024文字である。
これは、group_concat_max_len システム変数で変更可能である。

 # 現在のgroup_concat_max_lenを確認
 SELECT @@group_concat_max_len;
 
 # group_concat_max_lenを変更
 SET SESSION group_concat_max_len = 10000;
 
 # またはクエリごとに設定
 SET @old_max_len = @@group_concat_max_len;
 SET SESSION group_concat_max_len = 10000;
 SELECT department_id, GROUP_CONCAT(name SEPARATOR ', ') AS employee_names
    FROM employees
    GROUP BY department_id;
 SET SESSION group_concat_max_len = @old_max_len;

複数列の連結も可能である。

 # 複数列を連結
 SELECT department_id,
        GROUP_CONCAT(CONCAT(name, ' (', job_title, ')') ORDER BY name SEPARATOR ', ') AS employee_info
    FROM employees
    GROUP BY department_id;
 
 # 列名と値を組み合わせる
 SELECT order_id,
        GROUP_CONCAT(CONCAT(product_name, ': ', quantity, '個') SEPARATOR ', ') AS order_details
    FROM order_items
    JOIN products ON order_items.product_id = products.id
    GROUP BY order_id;

JSON_ARRAYAGG()関数と JSON_OBJECTAGG()関数

MySQL 5.7.22以降では、JSON_ARRAYAGG() と JSON_OBJECTAGG() 関数が使用できる。
これらはJSON形式でグループ内の値を集約する。

 # JSON_ARRAYAGG(): JSON配列を生成
 SELECT department_id, JSON_ARRAYAGG(name) AS employee_names
    FROM employees
    GROUP BY department_id;
 # 結果例: {"department_id": 1, "employee_names": ["Alice", "Bob", "Charlie"]}
 
 # JSON_OBJECTAGG(): JSONオブジェクトを生成
 SELECT department_id, JSON_OBJECTAGG(id, name) AS employee_map
    FROM employees
    GROUP BY department_id;
 # 結果例: {"department_id": 1, "employee_map": {"1": "Alice", "2": "Bob"}}
 
 # JSON_ARRAYAGG()とORDER BYの組み合わせ
 SELECT department_id,
        JSON_ARRAYAGG(name ORDER BY name ASC) AS employee_names_sorted
    FROM employees
    GROUP BY department_id;
 
 # JSON_ARRAYAGG()で構造化データを生成
 SELECT department_id,
        JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'salary', salary)) AS employees
    FROM employees
    GROUP BY department_id;

JSON_ARRAYAGG() は、NULL値も含めて配列に追加する。

 # NULL値の扱い
 SELECT department_id,
        JSON_ARRAYAGG(manager_id) AS manager_ids
    FROM employees
    GROUP BY department_id;
 # NULLも配列に含まれる: [1, 2, null, 3]
 
 # NULL値を除外する場合
 SELECT department_id,
        JSON_ARRAYAGG(manager_id) AS manager_ids
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY department_id;

HAVING句

HAVING 句は、GROUP BY 句でグループ化された後の結果に対して条件を指定する。

HAVING句の基本

HAVING 句は、集約関数を使用した条件指定に使用される。

 # 基本的なHAVING句
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5;
 
 # 平均給与が50000以上の部署
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) >= 50000;
 
 # 合計売上が100000以上のカテゴリ
 SELECT category_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY category_id
    HAVING SUM(amount) >= 100000;

複数の条件を組み合わせることも可能である。

 # AND条件
 SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) >= 50000 AND COUNT(*) > 10;
 
 # OR条件
 SELECT category_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY category_id
    HAVING SUM(amount) >= 100000 OR COUNT(*) > 100;
 
 # 複雑な条件
 SELECT user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) >= 5 AND SUM(amount) > 50000;

WHEREとHAVINGの違い

WHERE 句と HAVING 句の違いを理解することが重要である。

WHEREとHAVINGの違い
項目	WHERE句	HAVING句
適用タイミング	グループ化前	グループ化後
対象	個別の行	グループ化された結果
集約関数	使用不可	使用可能
実行順序	GROUP BYの前	GROUP BYの後

具体的な違いを以下の例で示す。

 # WHERE句 : グループ化前の個別行をフィルタリング
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    WHERE salary > 50000      # 給与が50000以上の従業員のみを対象
    GROUP BY department_id;
 
 # HAVING句 : グループ化後の集計結果をフィルタリング
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) > 50000;  # 部署の平均給与が50000以上のグループのみを抽出
 
 # WHEREとHAVINGを組み合わせる
 SELECT department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary
    FROM employees
    WHERE hire_date >= '2024-01-01'    # 1. 2024年以降に雇用された従業員のみを対象
    GROUP BY department_id              # 2. 部署ごとにグループ化
    HAVING COUNT(*) > 5;                # 3. 従業員数が5人以上の部署のみを抽出

WHERE 句では集約関数を使用できない。

 # エラーになる例: WHERE句で集約関数を使用
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    WHERE COUNT(*) > 5        # エラー: Invalid use of group function
    GROUP BY department_id;
 
 # 正しい形式: HAVING句で集約関数を使用
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5;

集約関数を使用した条件指定

HAVING 句では、様々な集約関数を条件として使用できる。

 # COUNT()を使用
 SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) >= 10;
 
 # SUM()を使用
 SELECT category_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY category_id
    HAVING SUM(amount) > 100000;
 
 # AVG()を使用
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) BETWEEN 50000 AND 80000;
 
 # MAX()とMIN()を使用
 SELECT category_id,
        MAX(price) - MIN(price) AS price_range
    FROM products
    GROUP BY category_id
    HAVING MAX(price) - MIN(price) > 10000;
 
 # 複数の集約関数を組み合わせる
 SELECT user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order_value
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) >= 5 AND AVG(amount) > 5000;

MySQL 8.0以降の変更点

MySQL 8.0以降では、GROUP BY の挙動にいくつかの重要な変更がある。

GROUP BYの暗黙ソート廃止

MySQL 5.7以前では、GROUP BYを使用すると自動的に結果がソートされていた。
MySQL 8.0以降では、この暗黙的なソートが廃止され、明示的に ORDER BY を指定する必要がある。

 # MySQL 5.7以前 : GROUP BYで自動的にソートされる
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 # 結果は暗黙的にdepartment_id順にソートされる
 
 # MySQL 8.0以降 : 明示的にORDER BYを指定する必要がある
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    ORDER BY department_id;
 
 # 集計結果でソートする場合
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    ORDER BY employee_count DESC;

この変更により、不要なソート処理が削減され、パフォーマンスが向上する可能性がある。

 # ソートが不要な場合は、ORDER BYを省略することでパフォーマンス向上
 SELECT category_id, COUNT(*) AS product_count
    FROM products
    GROUP BY category_id;
 # 結果の順序は保証されないが、ソート処理が不要
 
 # 必要な場合のみORDER BYを明示
 SELECT category_id, COUNT(*) AS product_count
    FROM products
    GROUP BY category_id
    ORDER BY product_count DESC;

ROLLUP修飾子

WITH ROLLUP 修飾子を使用すると、小計と総計を含む結果セットを生成できる。

 # 基本的なROLLUP
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id WITH ROLLUP;
 # 結果には各部署の従業員数と、全体の合計行が含まれる
 
 # 複数列でのROLLUP
 SELECT department_id, job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id, job_title WITH ROLLUP;
 # 部署と役職の組み合わせ、部署ごとの小計、全体の総計が生成される
 
 # SUMとROLLUPの組み合わせ
 SELECT category_id, SUM(amount) AS total_sales
    FROM order_items
    GROUP BY category_id WITH ROLLUP;
 # 各カテゴリの売上と、全カテゴリの売上合計が生成される

ROLLUP は階層的な集計を行う。

 # 年と月での階層的集計
 SELECT YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY year, month WITH ROLLUP;
 
 # 結果:
 # - 各年月の売上
 # - 各年の小計 (月がNULL)
 # - 全体の総計 (年と月がNULL)
 
 # 部署、役職、従業員の階層的カウント
 SELECT department_id, job_title, name, COUNT(*) AS count
    FROM employees
    GROUP BY department_id, job_title, name WITH ROLLUP;

GROUPING()関数

GROUPING() 関数は、ROLLUP で生成されたスーパーアグリゲート行を識別する。
スーパーアグリゲート行の場合は 1 を、通常行の場合は 0 を返す。

 # GROUPING()関数の基本的な使用
 SELECT department_id,
        COUNT(*) AS employee_count,
        GROUPING(department_id) AS is_rollup
    FROM employees
    GROUP BY department_id WITH ROLLUP;
 # is_rollupが1の行は総計行
 
 # 複数列でのGROUPING()
 SELECT YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        SUM(amount) AS total_sales,
        GROUPING(YEAR(order_date)) AS year_rollup,
        GROUPING(MONTH(order_date)) AS month_rollup
    FROM orders
    GROUP BY year, month WITH ROLLUP;
 
 # GROUPING()を使用してラベルを追加
 SELECT
    CASE WHEN GROUPING(department_id) = 1 THEN '全部署合計'
         ELSE CAST(department_id AS CHAR)
    END AS department,
    COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id WITH ROLLUP;

GROUPING() は複数列の集計レベルを識別するのに便利である。

 # 集計レベルを識別
 SELECT department_id,
        job_title,
        COUNT(*) AS employee_count,
        CASE WHEN GROUPING(department_id) = 1 THEN '総計'
             WHEN GROUPING(job_title) = 1 THEN '部署別小計'
             ELSE '詳細'
        END AS level
    FROM employees
    GROUP BY department_id, job_title WITH ROLLUP;

ROLLUPとORDER BYの併用

MySQL 8.0.12以降では、ROLLUP と ORDER BY を併用できる。

 # ROLLUPとORDER BYの併用 (MySQL 8.0.12以降)
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id WITH ROLLUP
    ORDER BY employee_count DESC;
 
 # DISTINCTとROLLUPの併用も可能
 SELECT DISTINCT department_id, job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id, job_title WITH ROLLUP;

ONLY_FULL_GROUP_BY SQLモード

ONLY_FULL_GROUP_BY SQLモードは、SELECT 句、HAVING 句、ORDER BY 句に記述できる非集計列を制限する。

このモードが有効な場合、非集計列はGROUP BY句に含まれている、または、関数従属している必要がある。

 # ONLY_FULL_GROUP_BYモードの確認
 SELECT @@sql_mode;
 
 # エラーになる例 (ONLY_FULL_GROUP_BY有効時)
 SELECT department_id, name, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 # エラー : nameはGROUP BYに含まれておらず、集約関数でもない
 
 # 正しい形式1: nameをGROUP BYに追加
 SELECT department_id, name, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id, name;
 
 # 正しい形式2 : nameを集約関数で囲む
 SELECT department_id, GROUP_CONCAT(name) AS employee_names, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 
 # 正しい形式3 : nameを削除
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;

関数従属が成立する場合は、GROUP BYに含まれていない列も使用できる。

 # 主キーでグループ化した場合、同じテーブルの他の列も使用可能
 SELECT id, name, email
    FROM users
    GROUP BY id;
 # idが主キーの場合、nameとemailは関数従属しているため使用可能

パフォーマンス

GROUP BYのパフォーマンスは、インデックスの有無と使用方法によって大きく異なる。

ルースインデックススキャン

ルースインデックススキャンは、最も効率的なGROUP BYの実行戦略である。
各グループの最初のエントリのみをインデックスから読み取る。

ルースインデックススキャンが使用される条件は以下の通りである。

単一テーブルのクエリ
GROUP BY 列がインデックスの左端から連続している
集約関数は MIN() または MAX() のみ (または集約関数なし)

 # インデックス: (department_id, hire_date)
 
 # ルースインデックススキャンが使用される例
 SELECT department_id, MIN(hire_date) AS earliest_hire
    FROM employees
    GROUP BY department_id;
 
 # EXPLAINで確認
 EXPLAIN SELECT department_id, MIN(hire_date) AS earliest_hire
    FROM employees
    GROUP BY department_id;
 # Extra: Using index for group-by (scanning)
 
 # ルースインデックススキャンが使用される条件
 SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id;

ルースインデックススキャンが使用されない例を以下に示す。

 # インデックス: (department_id, hire_date)
 
 # GROUP BY列がインデックスの途中から始まる (使用されない)
 SELECT hire_date, COUNT(*) AS employee_count
    FROM employees
    GROUP BY hire_date;
 
 # MIN/MAX以外の集約関数 (使用されない)
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id;
 
 # 複数テーブルのJOIN (使用されない)
 SELECT d.department_name, COUNT(*) AS employee_count
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.department_name;

タイトインデックススキャン

タイトインデックススキャンは、インデックスの全エントリを読み取るが、テーブル本体へのアクセスは不要である。
ルースインデックススキャンより遅いが、インデックスなしよりは高速である。

 # インデックス: (department_id, salary)
 
 # タイトインデックススキャンが使用される例
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id;
 # インデックスの全エントリを読み取る
 
 # EXPLAINで確認
 EXPLAIN SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id;
 # Extra: Using index

インデックスなしの場合

インデックスがない場合、一時テーブルと filesort が使用される。
これは最も遅い実行戦略である。

 # インデックスがない列でGROUP BY
 SELECT job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY job_title;
 
 # EXPLAINで確認
 EXPLAIN SELECT job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY job_title;
 # Extra: Using temporary; Using filesort

最適化の指針

GROUP BYのパフォーマンスを向上させるための指針を以下に示す。

GROUP BY 列にインデックスを作成する
最も基本的で効果的な最適化
列の順序をインデックスと一致させる
GROUP BY col1, col2 の場合、インデックスも (col1, col2) の順
カバリングインデックスを使用する
SELECT 句と GROUP BY 句の全列を含むインデックスを作成
不要な ORDER BY を削除する
MySQL 8.0以降では暗黙的なソートが廃止されているため、不要な ORDER BY を削除

 # インデックスの作成例
 CREATE INDEX idx_department_id ON employees(department_id);
 
 # 複数列のインデックス
 CREATE INDEX idx_dept_job ON employees(department_id, job_title);
 
 # カバリングインデックス
 CREATE INDEX idx_dept_salary ON employees(department_id, salary);
 
 # このクエリはカバリングインデックスを使用
 SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id;
 # テーブル本体へのアクセスが不要

インデックスの順序が重要である。

 # インデックス: (department_id, job_title)
 
 # インデックスが使用される (左端から一致)
 SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 
 # インデックスが使用される (左端から一致)
 SELECT department_id, job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id, job_title;
 
 # インデックスが使用されない (途中から始まる)
 SELECT job_title, COUNT(*) AS employee_count
    FROM employees
    GROUP BY job_title;

実行計画の確認には、EXPLAIN を使用する。

 # 実行計画を確認
 EXPLAIN SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 
 # MySQL 8.0.18以降では、EXPLAIN ANALYZEで実際の実行時間も確認可能
 EXPLAIN ANALYZE SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id;
 
 # Extraフィールドで最適化状況を確認
 # - "Using index for group-by": ルースインデックススキャン
 # - "Using index": タイトインデックススキャン
 # - "Using temporary; Using filesort": 一時テーブルとソート

JOINとの組み合わせ

GROUP BYは、JOIN と組み合わせることにより、複数テーブルのデータを集約できる。

INNER JOINとGROUP BY

INNER JOIN でテーブルを結合し、GROUP BY で集約する。

 # 部署別の従業員数と部署名を取得
 SELECT d.department_name, COUNT(*) AS employee_count
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
    GROUP BY d.department_name;
 
 # カテゴリ別の商品数とカテゴリ名を取得
 SELECT c.category_name, COUNT(*) AS product_count
    FROM products p
    INNER JOIN categories c ON p.category_id = c.id
    GROUP BY c.category_name;
 
 # ユーザ別の注文数と合計金額を取得
 SELECT u.name,
        COUNT(o.id) AS order_count,
        SUM(o.amount) AS total_spent
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    GROUP BY u.name;

複数のテーブルを結合して集約する例を以下に示す。

 # 注文、注文明細、商品を結合して集計
 SELECT p.product_name,
        COUNT(DISTINCT o.id) AS order_count,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.amount) AS total_sales
    FROM orders o
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
    GROUP BY p.product_name;
 
 # 部署、従業員、プロジェクトを結合
 SELECT d.department_name,
        COUNT(DISTINCT e.id) AS employee_count,
        COUNT(DISTINCT p.id) AS project_count
    FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    INNER JOIN project_assignments pa ON e.id = pa.employee_id
    INNER JOIN projects p ON pa.project_id = p.id
    GROUP BY d.department_name;

LEFT JOINとGROUP BY

LEFT JOIN を使用すると、マッチしない行も含めて集計できる。

 # 全ユーザの注文数を取得 (注文がないユーザも含む)
 SELECT u.name,
        COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.name;
 # 注文がないユーザはorder_countが0になる
 
 # 全部署の従業員数を取得 (従業員がいない部署も含む)
 SELECT d.department_name,
        COUNT(e.id) AS employee_count
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name;
 
 # 全カテゴリの商品数と合計売上を取得 (売上がないカテゴリも含む)
 SELECT c.category_name,
        COUNT(p.id) AS product_count,
        IFNULL(SUM(oi.amount), 0) AS total_sales
    FROM categories c
    LEFT JOIN products p ON c.id = p.category_id
    LEFT JOIN order_items oi ON p.id = oi.product_id
    GROUP BY c.category_name;

COUNT(*) と COUNT(column) の違いに注意が必要である。

 # COUNT(*)は全行をカウント (NULLも含む)
 SELECT u.name,
        COUNT(*) AS row_count,
        COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.name;
 # 注文がないユーザ: row_count=1, order_count=0
 
 # LEFT JOINではCOUNT(joined_table.column)を使用すべき
 SELECT d.department_name,
        COUNT(e.id) AS employee_count
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name;

部署別集計の実用例

実用的な部署別集計の例を以下に示す。

 # 部署別の従業員数、平均給与、最高給与、最低給与
 SELECT d.department_name,
        COUNT(e.id) AS employee_count,
        ROUND(AVG(e.salary), 2) AS avg_salary,
        MAX(e.salary) AS max_salary,
        MIN(e.salary) AS min_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name
    ORDER BY employee_count DESC;
 
 # 部署別の役職ごとの従業員数
 SELECT d.department_name,
        e.job_title,
        COUNT(*) AS employee_count
    FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name, e.job_title
    ORDER BY d.department_name, employee_count DESC;
 
 # 部署別の年別採用人数
 SELECT d.department_name,
        YEAR(e.hire_date) AS hire_year,
        COUNT(*) AS hire_count
    FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name, hire_year
    ORDER BY d.department_name, hire_year;

サンプルクエリ

日別・月別集計

以下の例では、日付データを集計している。

 # 日別の注文数と売上合計
 SELECT DATE(order_date) AS order_day,
        COUNT(*) AS order_count,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE(order_date)
    ORDER BY order_day DESC;
 
 # 月別の売上集計
 SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
        COUNT(*) AS order_count,
        SUM(amount) AS total_sales,
        AVG(amount) AS avg_order_value
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
    ORDER BY month DESC;
 
 # 年別の売上推移
 SELECT YEAR(order_date) AS year,
        COUNT(*) AS order_count,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY YEAR(order_date)
    ORDER BY year;
 
 # 年月の階層的集計
 SELECT YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        COUNT(*) AS order_count,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY YEAR(order_date), MONTH(order_date)
    ORDER BY year DESC, month DESC;
 
 # 曜日別の注文傾向
 SELECT DAYNAME(order_date) AS day_of_week,
        COUNT(*) AS order_count,
        AVG(amount) AS avg_order_value
    FROM orders
    GROUP BY DAYNAME(order_date)
    ORDER BY FIELD(day_of_week, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

GROUP_CONCATでのリスト生成

GROUP_CONCAT() 関数を使用して、グループ内の値をリストとして表示する。

 # 部署ごとの従業員名リスト
 SELECT d.department_name,
        GROUP_CONCAT(e.name ORDER BY e.name SEPARATOR ', ') AS employee_list
    FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name;
 
 # ユーザごとの注文商品リスト
 SELECT u.name,
        GROUP_CONCAT(DISTINCT p.product_name ORDER BY p.product_name SEPARATOR ', ') AS products_ordered
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
    GROUP BY u.name;
 
 # カテゴリごとの商品名と価格のリスト
 SELECT c.category_name,
        GROUP_CONCAT(CONCAT(p.product_name, ' (', p.price, '円)') ORDER BY p.price DESC SEPARATOR ' | ') AS products
    FROM categories c
    INNER JOIN products p ON c.id = p.category_id
    GROUP BY c.category_name;
 
 # 部署ごとの従業員IDリスト
 SELECT d.department_name,
        GROUP_CONCAT(e.id ORDER BY e.id SEPARATOR ',') AS employee_ids
    FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name;

JSON_ARRAYAGGでのJSON配列生成

JSON_ARRAYAGG() 関数を使用して、グループ内の値をJSON配列として表示する。

 # 部署ごとの従業員名JSON配列
 SELECT d.department_name,
        JSON_ARRAYAGG(e.name) AS employees
    FROM departments d
    INNER JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name;
 
 # ユーザごとの注文情報JSON配列
 SELECT u.name,
        JSON_ARRAYAGG(
           JSON_OBJECT(
              'order_id', o.id,
              'order_date', o.order_date,
              'amount', o.amount
           )
        ) AS orders
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    GROUP BY u.name;
 
 # カテゴリごとの商品情報JSON配列
 SELECT c.category_name,
        JSON_ARRAYAGG(
           JSON_OBJECT(
              'product_name', p.product_name,
              'price', p.price,
              'stock', p.stock_quantity
           ) ORDER BY p.price DESC
        ) AS products
    FROM categories c
    INNER JOIN products p ON c.id = p.category_id
    GROUP BY c.category_name;

MySQL - GROUP BY

目次

概要

基本構文

GROUP BY句の基本構文

WHERE句との組み合わせ

式によるグループ化

集約関数

COUNT()関数

SUM()関数とAVG()関数

MAX()関数とMIN()関数

GROUP_CONCAT()関数

JSON_ARRAYAGG()関数と JSON_OBJECTAGG()関数

HAVING句

HAVING句の基本

WHEREとHAVINGの違い

集約関数を使用した条件指定

MySQL 8.0以降の変更点

GROUP BYの暗黙ソート廃止

ROLLUP修飾子

GROUPING()関数

ROLLUPとORDER BYの併用

ONLY_FULL_GROUP_BY SQLモード

パフォーマンス

ルースインデックススキャン

タイトインデックススキャン

インデックスなしの場合

最適化の指針

JOINとの組み合わせ

INNER JOINとGROUP BY

LEFT JOINとGROUP BY

部署別集計の実用例

サンプルクエリ

日別・月別集計

GROUP_CONCATでのリスト生成

JSON_ARRAYAGGでのJSON配列生成

関連項目

MySQL - GROUP BY

概要

基本構文

GROUP BY句の基本構文

WHERE句との組み合わせ

式によるグループ化

集約関数

COUNT()関数

SUM()関数とAVG()関数

MAX()関数とMIN()関数

GROUP_CONCAT()関数

JSON_ARRAYAGG()関数 と JSON_OBJECTAGG()関数

HAVING句

HAVING句の基本

WHEREとHAVINGの違い

集約関数を使用した条件指定

MySQL 8.0以降の変更点

GROUP BYの暗黙ソート廃止

ROLLUP修飾子

GROUPING()関数

ROLLUPとORDER BYの併用

ONLY_FULL_GROUP_BY SQLモード

パフォーマンス

ルースインデックススキャン

タイトインデックススキャン

インデックスなしの場合

最適化の指針

JOINとの組み合わせ

INNER JOINとGROUP BY

LEFT JOINとGROUP BY

部署別集計の実用例

サンプルクエリ

日別・月別集計

GROUP_CONCATでのリスト生成

JSON_ARRAYAGGでのJSON配列生成

関連項目

JSON_ARRAYAGG()関数と JSON_OBJECTAGG()関数