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

MySQLの集約関数 (Aggregate Functions) は、複数の行から単一の値を計算する関数である。<br>

集約関数は、<code>GROUP BY</code> 句と組み合わせて使用することで、データをグループ化して集計を行うことができる。<br>

主要な集約関数には、<code>COUNT</code>、<code>SUM</code>、<code>AVG</code>、<code>MAX</code>、<code>MIN</code>、<code>GROUP_CONCAT</code> が含まれる。<br>

また、MySQL 5.7.22以降では、<code>JSON_ARRAYAGG</code> および <code>JSON_OBJECTAGG</code> が追加されている。<br>

集約関数は、<code>NULL</code> 値を自動的に無視する特性を持つ (一部例外を除く)。<br>

全ての値が <code>NULL</code> の場合、集約関数は <code>NULL</code> を返す。(<code>COUNT(*)</code> は、0を返す)<br>

MySQL 8.0以降では、集約関数を <code>OVER()</code> 句と組み合わせてウインドウ関数として使用できる。<br>

ウインドウ関数として使用した場合、行を削減せず各行が保持される。<br>

<code>GROUP BY</code> 句を使用する際は、MySQL 8.0のデフォルトSQLモードである <code>ONLY_FULL_GROUP_BY</code> に注意が必要である。<br>

このモードでは、<code>SELECT</code> リストの非集約列は全て <code>GROUP BY</code> 句に含まれるか、関数従属である必要がある。<br>

集約関数のパフォーマンスは、インデックスの有無、データ量、<code>DISTINCT</code> の使用などに影響される。<br>

<code>EXPLAIN</code> で実行計画を確認し、<code>Using temporary</code>、<code>Using filesort</code> をチェックすることが推奨される。<br>

== 基本構文 ==

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

<center>

{| class="wikitable"

| JSON_OBJECTAGG(key, value) || キーと値のペアをJSONオブジェクトとして集約 (MySQL 5.7.22+) || JSON

</center>

<code>COUNT</code> 関数は、行数をカウントする集約関数である。<br>

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

  <syntaxhighlight lang="mysql">

  SELECT COUNT(*) FROM employees;

  SELECT COUNT(department_id) FROM employees;

  -- 重複を除いたカウント

  SELECT COUNT(DISTINCT department_id) FROM employees;

  SELECT COUNT(DISTINCT department_id, job_id) FROM employees;

<code>COUNT(*)</code> と <code>COUNT(column)</code> の違いを以下に示す。<br>

<center>

{| class="wikitable"

|+ <code>COUNT(*)</code> と <code>COUNT(column)</code> の違い

| COUNT(*)

| COUNT(column) || 指定列の <code>NULL</code> 以外の値のみをカウントする。<br><code>NULL</code> 値は自動的に除外される。

</center>

|+ パフォーマンス特性

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

| MyISAM || 内部的に行数を保持しているため、<code>WHERE</code> 句なしの <code>COUNT(*)</code> は高速に実行される。

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

  <syntaxhighlight lang="mysql">

  SELECT SUM(salary) FROM employees;

  SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;

  -- 重複を除いた合計

  SELECT SUM(DISTINCT salary) FROM employees;

<code>NULL</code> 値の処理を以下に示す。<br>

*: <code>DECIMAL</code> 型を返す。

*: <code>DOUBLE</code> 型を返す。

<u>※注意</u><br>

  <syntaxhighlight lang="mysql">

  -- TIME型の合計 (秒に変換)

  SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(work_time))) FROM daily_records;

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

  <syntaxhighlight lang="mysql">

  SELECT AVG(salary) FROM employees;

  SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

  -- 重複を除いた平均

  SELECT AVG(DISTINCT salary) FROM employees;

* <code>AVG(column)</code> は、<code>SUM(column) / COUNT(column)</code> と同等である。

* <code>NULL</code> 値は自動的に除外される。

*: <code>DECIMAL(14, 4)</code> 型を返す。

*: <code>DOUBLE</code> 型を返す。

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

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

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

  <syntaxhighlight lang="mysql">

  SELECT ROUND(AVG(salary), 2) FROM employees;

  -- 整数に切り捨て

  SELECT FLOOR(AVG(salary)) FROM employees;

  SELECT CEIL(AVG(salary)) FROM employees;

  -- 最大給与

SELECT MAX(salary) FROM employees;

  -- 最小給与

  SELECT MIN(salary) FROM employees;

  SELECT department_id, MAX(salary), MIN(salary)

  FROM employees

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

<br>

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

<code>NULL</code> 値の処理を以下に示す。<br>

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

  <syntaxhighlight lang="mysql">

  -- 辞書順で最初の名前

SELECT MAX(first_name) FROM employees;

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

<br>

<syntaxhighlight lang="mysql">

</syntaxhighlight>

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

  <syntaxhighlight lang="mysql">

  SELECT department_id, GROUP_CONCAT(first_name)

  FROM employees

  SELECT department_id, GROUP_CONCAT(first_name SEPARATOR '; ')

  FROM employees

  SELECT department_id, GROUP_CONCAT(first_name ORDER BY first_name ASC)

  FROM employees

  -- 重複を除外

SELECT department_id, GROUP_CONCAT(DISTINCT job_id)

GROUP BY department_id;

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

*: <code>GROUP_CONCAT</code> の出力最大長を制御する (デフォルト1024バイト)。

*: <code>SET SESSION group_concat_max_len = 1000000;</code> で拡張可能である。

<code>NULL</code> 値の処理を以下に示す。<br>

  <syntaxhighlight lang="mysql">

  -- 名前と役職を組み合わせて連結

        GROUP_CONCAT(CONCAT(first_name, ' (', job_id, ')') ORDER BY first_name)

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

  <syntaxhighlight lang="mysql">

  -- セッション単位で制限を拡張

  SET SESSION group_concat_max_len = 1000000;

  -- グローバルに制限を拡張

  SET GLOBAL group_concat_max_len = 1000000;

==== JSON_ARRAYAGG / JSON_OBJECTAGG ====

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

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

  <syntaxhighlight lang="mysql">

  -- 部署ごとの従業員名をJSON配列として集約

SELECT department_id, JSON_ARRAYAGG(first_name)

FROM employees

  -- 結果例

  -- {"department_id": 10, "names": ["John", "Jane", "Bob"]}

<code>GROUP_CONCAT</code> との違いを以下に示す。<br>

*: <code>JSON_ARRAYAGG</code> は、<code>NULL</code> 値も配列要素として含める。

*: <code>GROUP_CONCAT</code> は、<code>NULL</code> 値を無視する。

*: <code>JSON_ARRAYAGG</code> は、JSON配列型を返す。

*: <code>GROUP_CONCAT</code> は、文字列 (TEXT型) を返す。

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

  <syntaxhighlight lang="mysql">

  -- NULL値も配列要素として含まれる

  SELECT JSON_ARRAYAGG(commission_pct) FROM employees;

  -- 結果例: [0.2, 0.3, null, null, 0.15]

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

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

  <syntaxhighlight lang="mysql">

  -- 従業員IDと名前のペアをJSONオブジェクトとして集約

SELECT department_id, JSON_OBJECTAGG(employee_id, first_name)

  FROM employees

-- {"department_id": 10, "employees": {"100": "John", "101": "Jane"}}

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

* 明示的に <code>ORDER BY</code> 句を使用することで、順序を制御できる。

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

  <syntaxhighlight lang="mysql">

  SELECT JSON_OBJECTAGG(job_id, first_name)

  FROM employees;

  -- 結果例: {"CLERK": "Sarah", "MANAGER": "Bob"}

  -- (CLERKやMANAGERが複数いる場合、最後の行の値が使用される)

== GROUP BYとの組み合わせ ==

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

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

  <syntaxhighlight lang="mysql">

  -- 部署ごとの従業員数

  SELECT department_id, COUNT(*)

  FROM employees

  SELECT department_id, AVG(salary)

  FROM employees

  SELECT job_id, MAX(salary), MIN(salary)

  FROM employees

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

<syntaxhighlight lang="mysql">

</syntaxhighlight>

<code>WHERE</code> 句との組み合わせを以下に示す。<br>

  <syntaxhighlight lang="mysql">

  -- 給与が5000以上の従業員を対象に部署ごとの平均給与を計算

SELECT department_id, AVG(salary)

  FROM employees

  GROUP BY department_id;

実行順序を以下に示す。<br>

# HAVING句でフィルタリング (グループ化後)

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

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

  <syntaxhighlight lang="mysql">

  -- 部署ごとの従業員数と総計

  SELECT department_id, COUNT(*)

  FROM employees

  GROUP BY department_id WITH ROLLUP;

  -- 結果例:

-- | department_id | COUNT(*) |

  -- +---------------+----------+

-- |            20 |        8 |

  -- |            30 |      12 |

  -- |          NULL |      25 |  -- 総計行

  -- +---------------+----------+

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

<syntaxhighlight lang="mysql">

</syntaxhighlight>

<code>GROUPING</code> 関数は、<code>NULL</code> 値が通常のグループ値か、小計行かを区別するために使用する。<br>

  <syntaxhighlight lang="mysql">

  -- GROUPING関数で小計行を識別

        COUNT(*),

-- 1 = 小計行 (ROLLUPによって生成されたNULL)

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

  <syntaxhighlight lang="mysql">

  SELECT IF(GROUPING(department_id), 'Total', department_id) AS department,

        IF(GROUPING(job_id), 'Subtotal', job_id) AS job,

  FROM employees

  GROUP BY department_id, job_id WITH ROLLUP;

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

* <code>SELECT</code> リストの非集約列は、全て <code>GROUP BY</code> 句に含まれる必要がある。

* または、非集約列が <code>GROUP BY</code> 列に関数従属である必要がある。

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

  <syntaxhighlight lang="mysql">

  -- エラー: first_name が GROUP BY に含まれていない

SELECT department_id, first_name, COUNT(*)

-- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause

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

SELECT department_id, first_name, COUNT(*)

</syntaxhighlight>

<code>ONLY_FULL_GROUP_BY</code> モードでも、<code>ANY_VALUE</code> を使用することで非集約列を <code>SELECT</code> リストに含めることができる。<br>

  <syntaxhighlight lang="mysql">

  -- ANY_VALUE関数で回避

  SELECT department_id, ANY_VALUE(first_name), COUNT(*)

  FROM employees

  GROUP BY department_id;

  -- 注意: どの値が返されるかは不定である

<code>ONLY_FULL_GROUP_BY</code> モードの無効化を以下に示す。<br>

  <syntaxhighlight lang="mysql">

  -- セッション単位で無効化

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

  SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

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

  <syntaxhighlight lang="mysql">

  SELECT employee_id, first_name, last_name, COUNT(*)

  FROM employees

  -- employee_id がプライマリキーであるため、first_name と last_name は

  -- employee_id に関数従属しており、エラーにならない

== HAVING句との組み合わせ ==

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

<code>WHERE</code> 句 と <code>HAVING</code> 句の違いを以下に示す。<br>

|+ WHERE句とHAVING句の違い

| フィルタリングのタイミング || グループ化前 || グループ化後

| 条件の対象 || 個々の行 || グループ

| 集約関数の使用 || 使用できない || 使用できる

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

  <syntaxhighlight lang="mysql">

  SELECT department_id, COUNT(*)

  FROM employees

HAVING COUNT(*) >= 5;

  -- 平均給与が7000以上の部署のみを抽出

  SELECT department_id, AVG(salary)

  FROM employees

  GROUP BY department_id

  HAVING AVG(salary) >= 7000;

<code>WHERE</code> 句 と <code>HAVING</code> 句の組み合わせを以下に示す。<br>

  <syntaxhighlight lang="mysql">

  SELECT department_id, COUNT(*), AVG(salary)

FROM employees

WHERE salary >= 5000

HAVING COUNT(*) >= 3;

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

<syntaxhighlight lang="mysql">

-- 従業員数が5人以上、かつ平均給与が7000以上の部署

SELECT department_id, COUNT(*), AVG(salary)

HAVING COUNT(*) >= 5 AND AVG(salary) >= 7000;

</syntaxhighlight>

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

  <syntaxhighlight lang="mysql">

  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;

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

  <syntaxhighlight lang="mysql">

  SELECT employee_id,

        AVG(salary) OVER() AS avg_salary

  FROM employees;

  -- 結果例:

  -- | employee_id | first_name | salary | avg_salary |

<code>PARTITION BY</code> 句は、ウインドウを部分的に分割して、部分ごとの集約を行う。<br>

  <syntaxhighlight lang="mysql">

  SELECT employee_id,

        salary,

        AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary

FROM employees;

  -- 結果例:

<code>ORDER BY</code> 句は、ウインドウ内の行順序を指定する。<br>

これにより、累積合計や移動平均などの計算が可能になる。<br>

  <syntaxhighlight lang="mysql">

  SELECT employee_id,

        SUM(salary) OVER(PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary

  FROM employees;

  -- 結果例:

  -- |        101 | Jane      |            10 |  6000 |            14000 |

  -- |        102 | Bob        |            20 |  5000 |              5000 |

  -- |        103 | Alice      |            20 |  6000 |            11000 |

  -- +-------------+------------+---------------+--------+-------------------+

<code>ROWS</code> 関数 または <code>RANGE</code> 句を使用して、ウインドウ内の特定の行範囲に対する集約を行うことができる。<br>

  <syntaxhighlight lang="mysql">

  -- 現在行と直前の2行 (合計3行) の移動平均

  SELECT employee_id,

        AVG(salary) OVER(ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg

  FROM employees;

  SELECT employee_id,

        AVG(salary) OVER(ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg

  FROM employees;

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

<br>

<center>

</center>

*: <syntaxhighlight lang="mysql">

  -- 部署ごとの給与の累積合計 (明示的なフレーム指定)

  SELECT employee_id,

        SUM(salary) OVER(

        ) AS cumulative_salary

  FROM employees;

*: <br>

*: <syntaxhighlight lang="mysql">

  -- 直近3件の給与の移動平均

  SELECT employee_id,

        salary,

        AVG(salary) OVER(

        ) AS moving_avg_3

  FROM employees;

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

|+ ウインドウ関数としてサポートされる集約関数 (MySQL 8.0.12以降)