「MySQL - 集約関数」の版間の差分

提供: MochiuWiki : SUSE, EC, PCB

← 古い編集
ビジュアルウィキテキスト
編集の要約なし
 
(同じ利用者による、間の1版が非表示)
79行目: 79行目:
! 関数 !! 説明
! 関数 !! 説明
|-
|-
| COUNT(*)
| COUNT(*) || 全ての行数をカウントする。<br><code>NULL</code> 値を含む全ての行を対象とする。
|
* 全ての行数をカウントする
* <code>NULL</code> 値を含む全ての行を対象とする
|-
|-
| COUNT(column) || 指定列の <code>NULL</code> 以外の値のみをカウントする。<br><code>NULL</code> 値は自動的に除外される。
| COUNT(column) || 指定列の <code>NULL</code> 以外の値のみをカウントする。<br><code>NULL</code> 値は自動的に除外される。
868行目: 865行目:
* ディスク上の一時テーブルは、メモリ上の一時テーブルより遅い。
* ディスク上の一時テーブルは、メモリ上の一時テーブルより遅い。
<br>
<br>
  <syntaxhighlight sql">
  <syntaxhighlight lang="sql">
  -- 一時テーブルのサイズ制限を確認
  -- 一時テーブルのサイズ制限を確認
  SHOW VARIABLES LIKE 'tmp_table_size';
  SHOW VARIABLES LIKE 'tmp_table_size';
886行目: 883行目:
*: 大量データをパーティション分割して、集計対象を絞る。
*: 大量データをパーティション分割して、集計対象を絞る。
<br>
<br>
==== GROUP_CONCATの制限 ====
==== GROUP_CONCATの制限 ====
<code>GROUP_CONCAT</code> は、出力制限に注意が必要である。<br>
<code>GROUP_CONCAT</code> は、出力制限に注意が必要である。<br>

2026年2月15日 (日) 20:14時点における最新版

概要

MySQLの集約関数 (Aggregate Functions) は、複数の行から単一の値を計算する関数である。
集約関数は、GROUP BY 句と組み合わせて使用することで、データをグループ化して集計を行うことができる。

主要な集約関数には、COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT が含まれる。
また、MySQL 5.7.22以降では、JSON_ARRAYAGG および JSON_OBJECTAGG が追加されている。

集約関数は、NULL 値を自動的に無視する特性を持つ (一部例外を除く)。
全ての値が NULL の場合、集約関数は NULL を返す。(COUNT(*) は、0を返す)

MySQL 8.0以降では、集約関数を OVER() 句と組み合わせてウインドウ関数として使用できる。
ウインドウ関数として使用した場合、行を削減せず各行が保持される。

GROUP BY 句を使用する際は、MySQL 8.0のデフォルトSQLモードである ONLY_FULL_GROUP_BY に注意が必要である。
このモードでは、SELECT リストの非集約列は全て GROUP BY 句に含まれるか、関数従属である必要がある。

集約関数のパフォーマンスは、インデックスの有無、データ量、DISTINCT の使用などに影響される。
EXPLAIN で実行計画を確認し、Using temporary、Using filesort をチェックすることが推奨される。


基本構文

MySQLで提供される主要な集約関数を以下に示す。

主要な集約関数一覧
関数名 説明 戻り値型
COUNT(*) 全ての行数をカウント (NULL含む) BIGINT
COUNT(column) 指定列のNULL以外の値をカウント BIGINT
COUNT(DISTINCT column) 指定列の重複を除いた値をカウント BIGINT
SUM(column) 指定列の合計値を計算 (NULL除外) DECIMAL または DOUBLE
SUM(DISTINCT column) 指定列の重複を除いた合計値を計算 DECIMAL または DOUBLE
AVG(column) 指定列の平均値を計算 (NULL除外) DECIMAL または DOUBLE
AVG(DISTINCT column) 指定列の重複を除いた平均値を計算 DECIMAL または DOUBLE
MAX(column) 指定列の最大値を取得 列の型に依存
MIN(column) 指定列の最小値を取得 列の型に依存
GROUP_CONCAT(column) 指定列の値を連結した文字列を生成 TEXT
JSON_ARRAYAGG(column) 指定列の値をJSON配列として集約 (MySQL 5.7.22+) JSON
JSON_OBJECTAGG(key, value) キーと値のペアをJSONオブジェクトとして集約 (MySQL 5.7.22+) JSON


COUNT関数

COUNT 関数は、行数をカウントする集約関数である。

基本的な使用例を以下に示す。

 -- 全ての行数をカウント (NULL含む)
 SELECT COUNT(*) FROM employees;
 
 -- 指定列のNULL以外の値をカウント
 SELECT COUNT(department_id) FROM employees;
 
 -- 重複を除いたカウント
 SELECT COUNT(DISTINCT department_id) FROM employees;
 
 -- 複数列の組み合わせで重複を除いたカウント
 SELECT COUNT(DISTINCT department_id, job_id) FROM employees;


COUNT(*) と COUNT(column) の違いを以下に示す。

COUNT(*) と COUNT(column) の違い
関数 説明
COUNT(*) 全ての行数をカウントする。
NULL 値を含む全ての行を対象とする。
COUNT(column) 指定列の NULL 以外の値のみをカウントする。
NULL 値は自動的に除外される。


パフォーマンス特性
ストレージエンジン 説明
InnoDB COUNT(*) は、最小の利用可能なセカンダリインデックスをスキャンする。
プライマリキーより小さいセカンダリインデックスが存在する場合、そちらを優先的に使用する。
MyISAM 内部的に行数を保持しているため、WHERE 句なしの COUNT(*) は高速に実行される。


戻り値の型は、MySQL 8.0以降では常に BIGINT である。

COUNT(DISTINCT column1, column2) のように複数列を指定した場合、列の組み合わせでユニークな行をカウントする。

SUM関数

SUM 関数は、指定列の合計値を計算する集約関数である。

基本的な使用例を以下に示す。

 -- 給与の合計
 SELECT SUM(salary) FROM employees;
 
 -- 部署ごとの給与合計
 SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;
 
 -- 重複を除いた合計
 SELECT SUM(DISTINCT salary) FROM employees;


NULL 値の処理を以下に示す。

  • NULL 値は自動的に除外される。
  • 全ての値が NULL の場合、SUM は NULL を返す。
  • COUNT(column) が0の場合、SUM は NULL を返す。


戻り値の型を以下に示す。

  • 整数型または DECIMAL型の引数
    DECIMAL 型を返す。
  • FLOAT型または DOUBLE型の引数
    DOUBLE 型を返す。


※注意

  • 数値型の列にのみ適用可能
    時間型 (TIME、DATE、DATETIME) に直接適用できない。
    TIME_TO_SEC() 等で事前に数値に変換する必要がある。
  • オーバーフロー
    整数型の合計が型の範囲を超える場合、オーバーフローが発生する可能性がある。
    DECIMAL 型または BIGINT 型を使用することを推奨する。


SUM(DISTINCT column) を使用すると、重複する値を除いて合計を計算する。

時間型の合計を計算する例を以下に示す。

 -- TIME型の合計 (秒に変換)
 SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(work_time))) FROM daily_records;


AVG関数

AVG 関数は、指定列の平均値を計算する集約関数である。

基本的な使用例を以下に示す。

 -- 給与の平均
 SELECT AVG(salary) FROM employees;
 
 -- 部署ごとの給与平均
 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
 
 -- 重複を除いた平均
 SELECT AVG(DISTINCT salary) FROM employees;


内部的な計算方法を以下に示す。

  • AVG(column) は、SUM(column) / COUNT(column) と同等である。
  • NULL 値は自動的に除外される。


戻り値の型を以下に示す。

  • 整数型または DECIMAL型の引数
    DECIMAL(14, 4) 型を返す。
  • FLOAT型または DOUBLE型の引数
    DOUBLE 型を返す。


精度に関する注意を以下に示す。

  • 整数型の平均を計算する場合、小数点以下が切り捨てられない
    DECIMAL(14, 4) で正確な小数値が返される。
  • 丸め処理
    ROUND(AVG(column), 2) で任意の桁数に丸めることができる。


AVG(DISTINCT column) を使用すると、重複する値を除いて平均を計算する。

丸め処理の例を以下に示す。

 -- 小数点以下2桁に丸める
 SELECT ROUND(AVG(salary), 2) FROM employees;
 
 -- 整数に切り捨て
 SELECT FLOOR(AVG(salary)) FROM employees;
 
 -- 整数に切り上げ
 SELECT CEIL(AVG(salary)) FROM employees;


MAX / MIN関数

MAX 関数 および MIN 関数は、指定列の最大値および最小値を取得する集約関数である。

基本的な使用例を以下に示す。

 -- 最大給与
 SELECT MAX(salary) FROM employees;
 
 -- 最小給与
 SELECT MIN(salary) FROM employees;
 
 -- 部署ごとの最大給与・最小給与
 SELECT department_id, MAX(salary), MIN(salary)
 FROM employees
 GROUP BY department_id;


数値型以外への適用を以下に示す。

  • 文字列型
    辞書順 (照合順序に依存) で比較される。
    照合順序 (COLLATION) によって結果が異なる場合がある。
  • 日付型 (DATE、DATETIME、TIMESTAMP)
    時系列順で比較される。
    最も古い日付 (MIN)、最も新しい日付 (MAX) を取得できる。


インデックスとの関係を以下に示す。

  • インデックスが存在する列に対して MAX / MIN を使用する場合、インデックスを活用した高速な検索が可能である。
  • これは、ORDER BY column DESC LIMIT 1 (MAX) や ORDER BY column ASC LIMIT 1 (MIN) と同等の最適化が行われるためである。


NULL 値の処理を以下に示す。

  • NULL 値は常に無視される。
  • 全ての値が NULL の場合、MAX / MIN は NULL を返す。


文字列型への適用例を以下に示す。

 -- 辞書順で最初の名前
 SELECT MIN(first_name) FROM employees;
 
 -- 辞書順で最後の名前
 SELECT MAX(first_name) FROM employees;


日付型への適用例を以下に示す。

 -- 最も古い入社日
 SELECT MIN(hire_date) FROM employees;
 
 -- 最も新しい入社日
 SELECT MAX(hire_date) FROM employees;


GROUP_CONCAT関数

GROUP_CONCAT 関数は、グループ内の値を連結した文字列を生成する集約関数である。

基本的な使用例を以下に示す。

 -- 部署ごとの従業員名を連結
 SELECT department_id, GROUP_CONCAT(first_name)
 FROM employees
 GROUP BY department_id;
 
 -- セパレータを指定
 SELECT department_id, GROUP_CONCAT(first_name SEPARATOR '; ')
 FROM employees
 GROUP BY department_id;
 
 -- ORDER BY句でソート
 SELECT department_id, GROUP_CONCAT(first_name ORDER BY first_name ASC)
 FROM employees
 GROUP BY department_id;
 
 -- 重複を除外
 SELECT department_id, GROUP_CONCAT(DISTINCT job_id)
 FROM employees
 GROUP BY department_id;


オプション句を以下に示す。

  • SEPARATOR句
    連結時のセパレータを指定する。
    デフォルトはカンマ (,) である。
    SEPARATOR でセパレータなしの連結も可能である。
  • ORDER BY句
    連結前に値をソートする。
    ORDER BY column ASC または ORDER BY column DESC を指定できる。
  • DISTINCT
    重複する値を除外してから連結する。


出力制限に関する注意を以下に示す。

  • group_concat_max_len システム変数
    GROUP_CONCAT の出力最大長を制御する (デフォルト1024バイト)。
    この制限を超える場合、無警告で切り詰められる。
    SET SESSION group_concat_max_len = 1000000; で拡張可能である。
  • max_allowed_packet
    最大パケットサイズも制限要因となる。


NULL 値の処理を以下に示す。

  • NULL 値は自動的に除外される。
  • 全ての値が NULL の場合、GROUP_CONCAT は NULL を返す。


複数列の連結例を以下に示す。

 -- 名前と役職を組み合わせて連結
 SELECT department_id,
        GROUP_CONCAT(CONCAT(first_name, ' (', job_id, ')') ORDER BY first_name)
 FROM employees
 GROUP BY department_id;


出力制限の設定例を以下に示す。

 -- セッション単位で制限を拡張
 SET SESSION group_concat_max_len = 1000000;
 
 -- グローバルに制限を拡張
 SET GLOBAL group_concat_max_len = 1000000;


JSON_ARRAYAGG / JSON_OBJECTAGG

JSON_ARRAYAGG 関数 および JSON_OBJECTAGG 関数は、MySQL 5.7.22以降で使用可能なJSON集約関数である。

JSON_ARRAYAGG

JSON_ARRAYAGG 関数は、グループ内の値をJSON配列として集約する。

基本的な使用例を以下に示す。

 -- 部署ごとの従業員名をJSON配列として集約
 SELECT department_id, JSON_ARRAYAGG(first_name)
 FROM employees
 GROUP BY department_id;
 
 -- 結果例
 -- {"department_id": 10, "names": ["John", "Jane", "Bob"]}


GROUP_CONCAT との違いを以下に示す。

  • NULL値の扱い
    JSON_ARRAYAGG は、NULL 値も配列要素として含める。
    GROUP_CONCAT は、NULL 値を無視する。
  • 戻り値の型
    JSON_ARRAYAGG は、JSON配列型を返す。
    GROUP_CONCAT は、文字列 (TEXT型) を返す。


NULL値を含む例を以下に示す。

 -- NULL値も配列要素として含まれる
 SELECT JSON_ARRAYAGG(commission_pct) FROM employees;
 
 -- 結果例: [0.2, 0.3, null, null, 0.15]


JSON_OBJECTAGG

JSON_OBJECTAGG 関数は、キーと値のペアをJSONオブジェクトとして集約する。

基本的な使用例を以下に示す。

 -- 従業員IDと名前のペアをJSONオブジェクトとして集約
 SELECT department_id, JSON_OBJECTAGG(employee_id, first_name)
 FROM employees
 GROUP BY department_id;
 
 -- 結果例
 -- {"department_id": 10, "employees": {"100": "John", "101": "Jane"}}


キー重複時の動作を以下に示す。

  • 同じキーが複数回現れる場合、最後の値で上書きされる。
  • どの値が最後になるかは、行の順序に依存する。
  • 明示的に ORDER BY 句を使用することで、順序を制御できる。


キー重複の例を以下に示す。

 -- 同じキーが複数回現れる場合、最後の値で上書き
 SELECT JSON_OBJECTAGG(job_id, first_name)
 FROM employees;
 
 -- 結果例: {"CLERK": "Sarah", "MANAGER": "Bob"}
 -- (CLERKやMANAGERが複数いる場合、最後の行の値が使用される)



GROUP BYとの組み合わせ

基本的な使い方

GROUP BY 句は、集約関数と組み合わせて使用することにより、データをグループ化して集計を行う。

基本的な使用例を以下に示す。

 -- 部署ごとの従業員数
 SELECT department_id, COUNT(*)
 FROM employees
 GROUP BY department_id;
 
 -- 部署ごとの平均給与
 SELECT department_id, AVG(salary)
 FROM employees
 GROUP BY department_id;
 
 -- 役職ごとの最大給与・最小給与
 SELECT job_id, MAX(salary), MIN(salary)
 FROM employees
 GROUP BY job_id;


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

 -- 部署と役職の組み合わせでグループ化
 SELECT department_id, job_id, COUNT(*), AVG(salary)
 FROM employees
 GROUP BY department_id, job_id;


WHERE 句との組み合わせを以下に示す。

 -- 給与が5000以上の従業員を対象に部署ごとの平均給与を計算
 SELECT department_id, AVG(salary)
 FROM employees
 WHERE salary >= 5000
 GROUP BY department_id;


実行順序を以下に示す。

  1. WHERE句でフィルタリング (グループ化前)
  2. GROUP BY句でグループ化
  3. 集約関数で集計
  4. HAVING句でフィルタリング (グループ化後)
  5. ORDER BY句でソート
  6. LIMIT句で件数制限


WITH ROLLUP

WITH ROLLUP 修飾子は、小計と総計を含む追加行を生成する。

基本的な使用例を以下に示す。

 -- 部署ごとの従業員数と総計
 SELECT department_id, COUNT(*)
 FROM employees
 GROUP BY department_id WITH ROLLUP;
 
 -- 結果例:
 -- +---------------+----------+
 -- | department_id | COUNT(*) |
 -- +---------------+----------+
 -- |            10 |        5 |
 -- |            20 |        8 |
 -- |            30 |       12 |
 -- |          NULL |       25 |  -- 総計行
 -- +---------------+----------+


複数列でのロールアップを以下に示す。

 -- 部署と役職の組み合わせでロールアップ
 SELECT department_id, job_id, COUNT(*)
 FROM employees
 GROUP BY department_id, job_id WITH ROLLUP;
 
 -- 結果例:
 -- +---------------+---------+----------+
 -- | department_id | job_id  | COUNT(*) |
 -- +---------------+---------+----------+
 -- |            10 | CLERK   |        3 |
 -- |            10 | MANAGER |        2 |
 -- |            10 | NULL    |        5 |  -- 部署10の小計
 -- |            20 | CLERK   |        5 |
 -- |            20 | ANALYST |        3 |
 -- |            20 | NULL    |        8 |  -- 部署20の小計
 -- |          NULL | NULL    |       13 |  -- 総計
 -- +---------------+---------+----------+


GROUPING 関数を以下に示す。
GROUPING 関数は、NULL 値が通常のグループ値か、小計行かを区別するために使用する。

 -- GROUPING関数で小計行を識別
 SELECT department_id,
        job_id,
        COUNT(*),
        GROUPING(department_id) AS dept_grouping,
        GROUPING(job_id) AS job_grouping
 FROM employees
 GROUP BY department_id, job_id WITH ROLLUP;
 
 -- GROUPING関数の戻り値:
 -- 0 = 通常のグループ値
 -- 1 = 小計行 (ROLLUPによって生成されたNULL)


小計行のラベル付け例を以下に示す。

 -- 小計行にラベルを付ける
 SELECT IF(GROUPING(department_id), 'Total', department_id) AS department,
        IF(GROUPING(job_id), 'Subtotal', job_id) AS job,
        COUNT(*)
 FROM employees
 GROUP BY department_id, job_id WITH ROLLUP;


ONLY_FULL_GROUP_BY モード

ONLY_FULL_GROUP_BY は、MySQL 8.0のデフォルトSQLモードに含まれる厳格なグループ化ルールである。

基本的なルールを以下に示す。

  • SELECT リストの非集約列は、全て GROUP BY 句に含まれる必要がある。
  • または、非集約列が GROUP BY 列に関数従属である必要がある。


エラーの例を以下に示す。

 -- エラー: first_name が GROUP BY に含まれていない
 SELECT department_id, first_name, COUNT(*)
 FROM employees
 GROUP BY department_id;
 
 -- エラーメッセージ:
 -- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
 -- and contains nonaggregated column 'employees.first_name' which is not
 -- functionally dependent on columns in GROUP BY clause


正しい書き方を以下に示す。

 -- 正: first_name を GROUP BY に含める
 SELECT department_id, first_name, COUNT(*)
 FROM employees
 GROUP BY department_id, first_name;
 
 -- 正: first_name を集約関数に含める
 SELECT department_id, GROUP_CONCAT(first_name), COUNT(*)
 FROM employees
 GROUP BY department_id;


ANY_VALUE 関数を以下に示す。
ANY_VALUE 関数は、グループ内の任意の値を返す関数である。
ONLY_FULL_GROUP_BY モードでも、ANY_VALUE を使用することで非集約列を SELECT リストに含めることができる。

 -- ANY_VALUE関数で回避
 SELECT department_id, ANY_VALUE(first_name), COUNT(*)
 FROM employees
 GROUP BY department_id;
 
 -- 注意: どの値が返されるかは不定である


ONLY_FULL_GROUP_BY モードの無効化を以下に示す。

 -- セッション単位で無効化
 SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
 
 -- グローバルに無効化
 SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));


関数従属の例を以下に示す。

 -- employee_id がプライマリキーの場合、他の列は関数従属
 SELECT employee_id, first_name, last_name, COUNT(*)
 FROM employees
 GROUP BY employee_id;
 
 -- employee_id がプライマリキーであるため、first_name と last_name は
 -- employee_id に関数従属しており、エラーにならない



HAVING句との組み合わせ

HAVING 句は、グループ化後の結果に対してフィルタリングを行う。

WHERE 句 と HAVING 句の違いを以下に示す。

WHERE句とHAVING句の違い
項目 WHERE句 HAVING句
フィルタリングのタイミング グループ化前 グループ化後
条件の対象 個々の行 グループ
集約関数の使用 使用できない 使用できる


基本的な使用例を以下に示す。

 -- 従業員数が5人以上の部署のみを抽出
 SELECT department_id, COUNT(*)
 FROM employees
 GROUP BY department_id
 HAVING COUNT(*) >= 5;
 
 -- 平均給与が7000以上の部署のみを抽出
 SELECT department_id, AVG(salary)
 FROM employees
 GROUP BY department_id
 HAVING AVG(salary) >= 7000;


WHERE 句 と HAVING 句の組み合わせを以下に示す。

 -- 給与が5000以上の従業員を対象に、
 -- 従業員数が3人以上の部署の平均給与を計算
 SELECT department_id, COUNT(*), AVG(salary)
 FROM employees
 WHERE salary >= 5000
 GROUP BY department_id
 HAVING COUNT(*) >= 3;


複数の条件を指定する例を以下に示す。

 -- 従業員数が5人以上、かつ平均給与が7000以上の部署
 SELECT department_id, COUNT(*), AVG(salary)
 FROM employees
 GROUP BY department_id
 HAVING COUNT(*) >= 5 AND AVG(salary) >= 7000;


エイリアスの使用を以下に示す。

 -- HAVING句でエイリアスを使用
 SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
 FROM employees
 GROUP BY department_id
 HAVING emp_count >= 5 AND avg_salary >= 7000;



ウインドウ関数としての使用

MySQL 8.0以降では、集約関数を OVER() 句と組み合わせてウインドウ関数として使用できる。
ウインドウ関数として使用した場合、行を削減せず各行が保持される。

基本的な使用例を以下に示す。

 -- 各従業員の給与と全体の平均給与を表示
 SELECT employee_id,
        first_name,
        salary,
        AVG(salary) OVER() AS avg_salary
 FROM employees;
 
 -- 結果例:
 -- +-------------+------------+--------+------------+
 -- | employee_id | first_name | salary | avg_salary |
 -- +-------------+------------+--------+------------+
 -- |         100 | John       |   8000 |       6500 |
 -- |         101 | Jane       |   7000 |       6500 |
 -- |         102 | Bob        |   5000 |       6500 |
 -- +-------------+------------+--------+------------+


PARTITION BY 句を以下に示す。
PARTITION BY 句は、ウインドウを部分的に分割して、部分ごとの集約を行う。

 -- 部署ごとの平均給与を各行に表示
 SELECT employee_id,
        first_name,
        department_id,
        salary,
        AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary
 FROM employees;
 
 -- 結果例:
 -- +-------------+------------+---------------+--------+------------------+
 -- | employee_id | first_name | department_id | salary | dept_avg_salary  |
 -- +-------------+------------+---------------+--------+------------------+
 -- |         100 | John       |            10 |   8000 |             7000 |
 -- |         101 | Jane       |            10 |   6000 |             7000 |
 -- |         102 | Bob        |            20 |   5000 |             5500 |
 -- |         103 | Alice      |            20 |   6000 |             5500 |
 -- +-------------+------------+---------------+--------+------------------+


ORDER BY 句を以下に示す。
ORDER BY 句は、ウインドウ内の行順序を指定する。
これにより、累積合計や移動平均などの計算が可能になる。

 -- 部署ごとの給与の累積合計
 SELECT employee_id,
        first_name,
        department_id,
        salary,
        SUM(salary) OVER(PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
 FROM employees;
 
 -- 結果例:
 -- +-------------+------------+---------------+--------+-------------------+
 -- | employee_id | first_name | department_id | salary | cumulative_salary |
 -- +-------------+------------+---------------+--------+-------------------+
 -- |         100 | John       |            10 |   8000 |              8000 |
 -- |         101 | Jane       |            10 |   6000 |             14000 |
 -- |         102 | Bob        |            20 |   5000 |              5000 |
 -- |         103 | Alice      |            20 |   6000 |             11000 |
 -- +-------------+------------+---------------+--------+-------------------+


フレーム指定を以下に示す。
ROWS 関数 または RANGE 句を使用して、ウインドウ内の特定の行範囲に対する集約を行うことができる。

 -- 現在行と直前の2行 (合計3行) の移動平均
 SELECT employee_id,
        first_name,
        salary,
        AVG(salary) OVER(ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
 FROM employees;
 
 -- 現在行と前後1行 (合計3行) の移動平均
 SELECT employee_id,
        first_name,
        salary,
        AVG(salary) OVER(ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
 FROM employees;


下表に、フレーム指定のキーワードを示す。

フレーム指定のキーワード
キーワード 説明
ROWS 物理的な行数でフレームを指定する
RANGE 値の範囲でフレームを指定する
UNBOUNDED PRECEDING パーティションの最初の行から
UNBOUNDED FOLLOWING パーティションの最後の行まで
CURRENT ROW 現在の行
n PRECEDING 現在行からn行前
n FOLLOWING 現在行からn行後


  • 累積合計の例 
     -- 部署ごとの給与の累積合計 (明示的なフレーム指定)
 SELECT employee_id,
        first_name,
        department_id,
        salary,
        SUM(salary) OVER(
           PARTITION BY department_id
           ORDER BY employee_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_salary
 FROM employees;

  • 移動平均の例 
     -- 直近3件の給与の移動平均
 SELECT employee_id,
        first_name,
        salary,
        AVG(salary) OVER(
           ORDER BY employee_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_avg_3
 FROM employees;


下表に、サポートされる集約関数を示す。
MySQL 8.0.12以降では、以下に示す集約関数がウインドウ関数としてサポートされている。

ウインドウ関数としてサポートされる集約関数 (MySQL 8.0.12以降)
集約関数
COUNT
SUM
AVG
MAX
MIN
GROUP_CONCAT
JSON_ARRAYAGG
JSON_OBJECTAGG



パフォーマンス

インデックスの活用

集約関数のパフォーマンスは、インデックスの有無に大きく影響される。

MAX / MIN 関数とインデックスの関係を以下に示す。

  • インデックスが存在する列に対して MAX / MIN を使用する場合、インデックスを活用した高速な検索が可能である。
  • これは、ORDER BY column DESC LIMIT 1 (MAX) や ORDER BY column ASC LIMIT 1 (MIN) と同等の最適化が行われるためである。


 -- インデックスが存在する場合、高速に実行される
 SELECT MAX(employee_id) FROM employees;
 
 -- EXPLAIN で確認
 EXPLAIN SELECT MAX(employee_id) FROM employees;
 -- type: index (インデックススキャン)


COUNT 関数とインデックスの関係を以下に示す。

  • InnoDBでは、COUNT(*) は最小の利用可能なセカンダリインデックスをスキャンする。
  • プライマリキーより小さいセカンダリインデックスが存在する場合、そちらを優先的に使用する。


 -- セカンダリインデックスが存在する場合、そちらをスキャン
 SELECT COUNT(*) FROM employees;
 
 -- EXPLAIN で確認
 EXPLAIN SELECT COUNT(*) FROM employees;
 -- key: index_name (セカンダリインデックス)


GROUP BY とインデックスの関係を以下に示す。

  • GROUP BY 列にインデックスが存在する場合、ソート処理が不要になる場合がある。
  • EXPLAIN で Using temporary; Using filesort が表示されない場合、インデックスが活用されている。


 -- department_idにインデックスが存在する場合、高速に実行される
 SELECT department_id, COUNT(*)
 FROM employees
 GROUP BY department_id;
 
 -- EXPLAINで確認
 EXPLAIN SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
 -- Extra: Using index (インデックスのみでクエリを実行)


大量データの集計

大量のデータを集計する場合、パフォーマンスに注意が必要である。

一時テーブルの使用を以下に示す。

  • GROUP BY でグループ化を行う場合、内部的に一時テーブル (TEMPORARY TABLE) が使用される可能性がある。
  • 一時テーブルのサイズが tmp_table_size および max_heap_table_size を超える場合、ディスク上の一時テーブルが使用される。
  • ディスク上の一時テーブルは、メモリ上の一時テーブルより遅い。


 -- 一時テーブルのサイズ制限を確認
 SHOW VARIABLES LIKE 'tmp_table_size';
 SHOW VARIABLES LIKE 'max_heap_table_size';
 
 -- EXPLAIN で一時テーブルの使用を確認
 EXPLAIN SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
 -- Extra: Using temporary (一時テーブルを使用)


パフォーマンス改善の方法を以下に示す。

  • インデックスの追加
    GROUP BY 列にインデックスを追加する。
  • 一時テーブルサイズの拡張
    tmp_table_size および max_heap_table_size を増やす。
  • パーティショニング
    大量データをパーティション分割して、集計対象を絞る。


GROUP_CONCATの制限

GROUP_CONCAT は、出力制限に注意が必要である。

出力制限を以下に示す。

  • group_concat_max_len (デフォルト1024バイト)
    GROUP_CONCAT の出力最大長を制御する。
    この制限を超える場合、無警告で切り詰められる。
  • max_allowed_packet
    最大パケットサイズも制限要因となる。


切り詰め時の挙動を以下に示す。

  • 制限を超えた場合、無警告で切り詰められる。
  • 警告 (WARNING) は生成されない。
  • SHOW WARNINGS; でも確認できない。


推奨される対策を以下に示す。

  • セッション単位で group_concat_max_len を拡張する。
  • または、グローバルに group_concat_max_len を拡張する。


 -- セッション単位で拡張
 SET SESSION group_concat_max_len = 1000000;
 
 -- グローバルに拡張
 SET GLOBAL group_concat_max_len = 1000000;


DISTINCT性能

DISTINCT を使用する集約関数は、追加の処理が必要なためパフォーマンスが低下する傾向がある。

パフォーマンス特性を以下に示す。

  • COUNT(DISTINCT column)
    DISTINCT 処理のため、COUNT(column) より遅い傾向がある。
    内部的に一時テーブルを使用する。
  • SUM(DISTINCT column) / AVG(DISTINCT column)
    同様に、DISTINCT 処理のため遅い傾向がある。


推奨される対策を以下に示す。

  • インデックスの追加
    DISTINCT 対象列にインデックスを追加する。
  • サブクエリの使用
    大量データの場合、サブクエリで事前に DISTINCT を行うことで高速化できる場合がある。


 -- サブクエリで事前にDISTINCTを行う
 SELECT COUNT(*) FROM (
    SELECT DISTINCT department_id FROM employees
 ) AS distinct_depts;


WITH ROLLUPの影響

WITH ROLLUP は、追加の処理が必要なためパフォーマンスに影響する。

パフォーマンス特性を以下に示す。

  • WITH ROLLUP は、小計行を生成するため、追加の処理が必要である。
  • 複数列でロールアップする場合、さらに処理が増加する。


推奨される対策を以下に示す。

  • インデックスの追加
    GROUP BY 列にインデックスを追加する。
  • UNION ALLの使用
    大量データの場合、UNION ALL で小計行を個別に計算する方が高速な場合がある。


 -- WITH ROLLUP の代わりに UNION ALL を使用
 SELECT department_id, COUNT(*) FROM employees GROUP BY department_id
 UNION ALL
 SELECT NULL, COUNT(*) FROM employees;



バージョン別の機能差異

MySQLのバージョンによって、集約関数の機能に差異がある。

下表に、MySQL 5.7とMySQL 8.0の主要な違いを示す。

MySQL 5.7 vs MySQL 8.0
機能 MySQL 5.7 MySQL 8.0
JSON_ARRAYAGG / JSON_OBJECTAGG 5.7.22以降でサポート サポート
ウインドウ関数 (OVER句) 未サポート サポート (8.0以降)
GROUPING関数 未サポート サポート (8.0.12以降)
ONLY_FULL_GROUP_BY デフォルトで有効 デフォルトで有効
COUNT関数の戻り値 BIGINT UNSIGNED BIGINT


MySQL 5.7での注意点を以下に示す。

  • ウインドウ関数は使用できない。
    OVER() 句はサポートされていない。
    代替として、サブクエリや自己結合を使用する必要がある。
  • GROUPING関数は使用できない。
    WITH ROLLUP で小計行を識別するには、NULL チェックのみを使用する。


MySQL 8.0での新機能を以下に示す。

  • ウインドウ関数
    集約関数を OVER() 句と組み合わせて使用できる。
    累積合計、移動平均等の計算が可能になる。
  • GROUPING関数
    WITH ROLLUP で小計行を識別できる。
  • 改善された集約関数のパフォーマンス
    インデックスの活用が改善されている。


MySQL 5.7でウインドウ関数を代替する例を以下に示す。

 -- MySQL 8.0 (ウインドウ関数)
 SELECT employee_id,
        salary,
        AVG(salary) OVER() AS avg_salary
 FROM employees;
 
 -- MySQL 5.7 (サブクエリで代替)
 SELECT e.employee_id,
        e.salary,
        (SELECT AVG(salary) FROM employees) AS avg_salary
 FROM employees e;



「https://mochiu.net/index.php?title=MySQL_-_集約関数&oldid=14301」から取得