MySQL - 集約関数のソースを表示

== 概要 ==
MySQLの集約関数 (Aggregate Functions) は、複数の行から単一の値を計算する関数である。<br>
集約関数は、<code>GROUP BY</code> 句と組み合わせて使用することで、データをグループ化して集計を行うことができる。<br>
<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>
<br>
集約関数は、<code>NULL</code> 値を自動的に無視する特性を持つ (一部例外を除く)。<br>
全ての値が <code>NULL</code> の場合、集約関数は <code>NULL</code> を返す。(<code>COUNT(*)</code> は、0を返す)<br>
<br>
MySQL 8.0以降では、集約関数を <code>OVER()</code> 句と組み合わせてウインドウ関数として使用できる。<br>
ウインドウ関数として使用した場合、行を削減せず各行が保持される。<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>
<br>
集約関数のパフォーマンスは、インデックスの有無、データ量、<code>DISTINCT</code> の使用などに影響される。<br>
<code>EXPLAIN</code> で実行計画を確認し、<code>Using temporary</code>、<code>Using filesort</code> をチェックすることが推奨される。<br>
<br><br>

== 基本構文 ==
MySQLで提供される主要な集約関数を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要な集約関数一覧
! 関数名 !! 説明 !! 戻り値型
|-
| 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
|}
</center>
<br>
==== COUNT関数 ====
<code>COUNT</code> 関数は、行数をカウントする集約関数である。<br>
<br>
基本的な使用例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- 全ての行数をカウント (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;
 </syntaxhighlight>
<br>
<code>COUNT(*)</code> と <code>COUNT(column)</code> の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ COUNT(*) と COUNT(column) の違い
|-
! 関数 !! 説明
|-
| COUNT(*) || 全ての行数をカウントする。<br><code>NULL</code> 値を含む全ての行を対象とする。
|-
| COUNT(column) || 指定列の <code>NULL</code> 以外の値のみをカウントする。<br><code>NULL</code> 値は自動的に除外される。
|}
</center>
<br>
<center>
{| class="wikitable"
|+ パフォーマンス特性
|-
! ストレージエンジン !! 説明
|-
| InnoDB || <code>COUNT(*)</code> は、最小の利用可能なセカンダリインデックスをスキャンする。<br>プライマリキーより小さいセカンダリインデックスが存在する場合、そちらを優先的に使用する。
|-
| MyISAM || 内部的に行数を保持しているため、<code>WHERE</code> 句なしの <code>COUNT(*)</code> は高速に実行される。
|}
</center>
<br>
戻り値の型は、MySQL 8.0以降では常に <code>BIGINT</code> である。<br>
<br>
<code>COUNT(DISTINCT column1, column2)</code> のように複数列を指定した場合、列の組み合わせでユニークな行をカウントする。<br>
<br>
==== SUM関数 ====
<code>SUM</code> 関数は、指定列の合計値を計算する集約関数である。<br>
<br>
基本的な使用例を以下に示す。<br>
<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;
 </syntaxhighlight>
<br>
<code>NULL</code> 値の処理を以下に示す。<br>
* <code>NULL</code> 値は自動的に除外される。
* 全ての値が <code>NULL</code> の場合、<code>SUM</code> は <code>NULL</code> を返す。
* <code>COUNT(column)</code> が0の場合、<code>SUM</code> は <code>NULL</code> を返す。
<br>
戻り値の型を以下に示す。<br>
* 整数型または DECIMAL型の引数
*: <code>DECIMAL</code> 型を返す。
* FLOAT型または DOUBLE型の引数
*: <code>DOUBLE</code> 型を返す。
<br>
<u>※注意</u><br>
* <u>数値型の列にのみ適用可能</u>
*: 時間型 (TIME、DATE、DATETIME) に直接適用できない。
*: <code>TIME_TO_SEC()</code> 等で事前に数値に変換する必要がある。
* <u>オーバーフロー</u>
*: 整数型の合計が型の範囲を超える場合、オーバーフローが発生する可能性がある。
*: <code>DECIMAL</code> 型または <code>BIGINT</code> 型を使用することを推奨する。
<br>
<code>SUM(DISTINCT column)</code> を使用すると、重複する値を除いて合計を計算する。<br>
<br>
時間型の合計を計算する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- TIME型の合計 (秒に変換)
 SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(work_time))) FROM daily_records;
 </syntaxhighlight>
<br>
==== AVG関数 ====
<code>AVG</code> 関数は、指定列の平均値を計算する集約関数である。<br>
<br>
基本的な使用例を以下に示す。<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;
 </syntaxhighlight>
<br>
内部的な計算方法を以下に示す。<br>
* <code>AVG(column)</code> は、<code>SUM(column) / COUNT(column)</code> と同等である。
* <code>NULL</code> 値は自動的に除外される。
<br>
戻り値の型を以下に示す。<br>
* 整数型または DECIMAL型の引数
*: <code>DECIMAL(14, 4)</code> 型を返す。
* FLOAT型または DOUBLE型の引数
*: <code>DOUBLE</code> 型を返す。
<br>
精度に関する注意を以下に示す。<br>
* 整数型の平均を計算する場合、小数点以下が切り捨てられない
*: <code>DECIMAL(14, 4)</code> で正確な小数値が返される。
* 丸め処理
*: <code>ROUND(AVG(column), 2)</code> で任意の桁数に丸めることができる。
<br>
<code>AVG(DISTINCT column)</code> を使用すると、重複する値を除いて平均を計算する。<br>
<br>
丸め処理の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 小数点以下2桁に丸める
 SELECT ROUND(AVG(salary), 2) FROM employees;
 
 -- 整数に切り捨て
 SELECT FLOOR(AVG(salary)) FROM employees;
 
 -- 整数に切り上げ
 SELECT CEIL(AVG(salary)) FROM employees;
 </syntaxhighlight>
<br>
==== MAX / MIN関数 ====
<code>MAX</code> 関数 および <code>MIN</code> 関数は、指定列の最大値および最小値を取得する集約関数である。<br>
<br>
基本的な使用例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- 最大給与
 SELECT MAX(salary) FROM employees;
 
 -- 最小給与
 SELECT MIN(salary) FROM employees;
 
 -- 部署ごとの最大給与・最小給与
 SELECT department_id, MAX(salary), MIN(salary)
 FROM employees
 GROUP BY department_id;
 </syntaxhighlight>
<br>
数値型以外への適用を以下に示す。<br>
* 文字列型
*: 辞書順 (照合順序に依存) で比較される。
*: 照合順序 (COLLATION) によって結果が異なる場合がある。
* 日付型 (DATE、DATETIME、TIMESTAMP)
*: 時系列順で比較される。
*: 最も古い日付 (<code>MIN</code>)、最も新しい日付 (<code>MAX</code>) を取得できる。
<br>
インデックスとの関係を以下に示す。<br>
* インデックスが存在する列に対して <code>MAX</code> / <code>MIN</code> を使用する場合、インデックスを活用した高速な検索が可能である。
* これは、<code>ORDER BY column DESC LIMIT 1</code> (<code>MAX</code>) や <code>ORDER BY column ASC LIMIT 1</code> (<code>MIN</code>) と同等の最適化が行われるためである。
<br>
<code>NULL</code> 値の処理を以下に示す。<br>
* <code>NULL</code> 値は常に無視される。
* 全ての値が <code>NULL</code> の場合、<code>MAX</code> / <code>MIN</code> は <code>NULL</code> を返す。
<br>
文字列型への適用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 辞書順で最初の名前
 SELECT MIN(first_name) FROM employees;
 
 -- 辞書順で最後の名前
 SELECT MAX(first_name) FROM employees;
 </syntaxhighlight>
<br>
日付型への適用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 最も古い入社日
 SELECT MIN(hire_date) FROM employees;
 
 -- 最も新しい入社日
 SELECT MAX(hire_date) FROM employees;
 </syntaxhighlight>
<br>
==== GROUP_CONCAT関数 ====
<code>GROUP_CONCAT</code> 関数は、グループ内の値を連結した文字列を生成する集約関数である。<br>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署ごとの従業員名を連結
 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;
 </syntaxhighlight>
<br>
オプション句を以下に示す。<br>
* SEPARATOR句
*: 連結時のセパレータを指定する。
*: デフォルトはカンマ (<code>,</code>) である。
*: <code>SEPARATOR ''</code> でセパレータなしの連結も可能である。
* ORDER BY句
*: 連結前に値をソートする。
*: <code>ORDER BY column ASC</code> または <code>ORDER BY column DESC</code> を指定できる。
* DISTINCT
*: 重複する値を除外してから連結する。
<br>
出力制限に関する注意を以下に示す。<br>
* group_concat_max_len システム変数
*: <code>GROUP_CONCAT</code> の出力最大長を制御する (デフォルト1024バイト)。
*: この制限を超える場合、無警告で切り詰められる。
*: <code>SET SESSION group_concat_max_len = 1000000;</code> で拡張可能である。
* max_allowed_packet
*: 最大パケットサイズも制限要因となる。
<br>
<code>NULL</code> 値の処理を以下に示す。<br>
* <code>NULL</code> 値は自動的に除外される。
* 全ての値が <code>NULL</code> の場合、<code>GROUP_CONCAT</code> は <code>NULL</code> を返す。
<br>
複数列の連結例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 名前と役職を組み合わせて連結
 SELECT department_id,
        GROUP_CONCAT(CONCAT(first_name, ' (', job_id, ')') ORDER BY first_name)
 FROM employees
 GROUP BY department_id;
 </syntaxhighlight>
<br>
出力制限の設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- セッション単位で制限を拡張
 SET SESSION group_concat_max_len = 1000000;
 
 -- グローバルに制限を拡張
 SET GLOBAL group_concat_max_len = 1000000;
 </syntaxhighlight>
<br>
==== JSON_ARRAYAGG / JSON_OBJECTAGG ====
<code>JSON_ARRAYAGG</code> 関数 および <code>JSON_OBJECTAGG</code> 関数は、MySQL 5.7.22以降で使用可能なJSON集約関数である。<br>
<br>
===== JSON_ARRAYAGG =====
<code>JSON_ARRAYAGG</code> 関数は、グループ内の値をJSON配列として集約する。<br>
<br>
基本的な使用例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- 部署ごとの従業員名をJSON配列として集約
 SELECT department_id, JSON_ARRAYAGG(first_name)
 FROM employees
 GROUP BY department_id;
 
 -- 結果例
 -- {"department_id": 10, "names": ["John", "Jane", "Bob"]}
 </syntaxhighlight>
<br>
<code>GROUP_CONCAT</code> との違いを以下に示す。<br>
* NULL値の扱い
*: <code>JSON_ARRAYAGG</code> は、<code>NULL</code> 値も配列要素として含める。
*: <code>GROUP_CONCAT</code> は、<code>NULL</code> 値を無視する。
* 戻り値の型
*: <code>JSON_ARRAYAGG</code> は、JSON配列型を返す。
*: <code>GROUP_CONCAT</code> は、文字列 (TEXT型) を返す。
<br>
NULL値を含む例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- NULL値も配列要素として含まれる
 SELECT JSON_ARRAYAGG(commission_pct) FROM employees;
 
 -- 結果例: [0.2, 0.3, null, null, 0.15]
 </syntaxhighlight>
<br>
===== JSON_OBJECTAGG =====
<code>JSON_OBJECTAGG</code> 関数は、キーと値のペアをJSONオブジェクトとして集約する。<br>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 従業員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"}}
 </syntaxhighlight>
<br>
キー重複時の動作を以下に示す。<br>
* 同じキーが複数回現れる場合、最後の値で上書きされる。
* どの値が最後になるかは、行の順序に依存する。
* 明示的に <code>ORDER BY</code> 句を使用することで、順序を制御できる。
<br>
キー重複の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 同じキーが複数回現れる場合、最後の値で上書き
 SELECT JSON_OBJECTAGG(job_id, first_name)
 FROM employees;
 
 -- 結果例: {"CLERK": "Sarah", "MANAGER": "Bob"}
 -- (CLERKやMANAGERが複数いる場合、最後の行の値が使用される)
 </syntaxhighlight>
<br><br>

== GROUP BYとの組み合わせ ==
==== 基本的な使い方 ====
<code>GROUP BY</code> 句は、集約関数と組み合わせて使用することにより、データをグループ化して集計を行う。<br>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署ごとの従業員数
 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;
 </syntaxhighlight>
<br>
複数列でのグループ化を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署と役職の組み合わせでグループ化
 SELECT department_id, job_id, COUNT(*), AVG(salary)
 FROM employees
 GROUP BY department_id, job_id;
 </syntaxhighlight>
<br>
<code>WHERE</code> 句との組み合わせを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 給与が5000以上の従業員を対象に部署ごとの平均給与を計算
 SELECT department_id, AVG(salary)
 FROM employees
 WHERE salary >= 5000
 GROUP BY department_id;
 </syntaxhighlight>
<br>
実行順序を以下に示す。<br>
# WHERE句でフィルタリング (グループ化前)
# GROUP BY句でグループ化
# 集約関数で集計
# HAVING句でフィルタリング (グループ化後)
# ORDER BY句でソート
# LIMIT句で件数制限
<br>
==== WITH ROLLUP ====
<code>WITH ROLLUP</code> 修飾子は、小計と総計を含む追加行を生成する。<br>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署ごとの従業員数と総計
 SELECT department_id, COUNT(*)
 FROM employees
 GROUP BY department_id WITH ROLLUP;
 
 -- 結果例:
 -- +---------------+----------+
 -- | department_id | COUNT(*) |
 -- +---------------+----------+
 -- |            10 |        5 |
 -- |            20 |        8 |
 -- |            30 |       12 |
 -- |          NULL |       25 |  -- 総計行
 -- +---------------+----------+
 </syntaxhighlight>
<br>
複数列でのロールアップを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署と役職の組み合わせでロールアップ
 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 |  -- 総計
 -- +---------------+---------+----------+
 </syntaxhighlight>
<br>
<code>GROUPING</code> 関数を以下に示す。<br>
<code>GROUPING</code> 関数は、<code>NULL</code> 値が通常のグループ値か、小計行かを区別するために使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 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)
 </syntaxhighlight>
<br>
小計行のラベル付け例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 小計行にラベルを付ける
 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;
 </syntaxhighlight>
<br>
==== ONLY_FULL_GROUP_BY モード ====
<code>ONLY_FULL_GROUP_BY</code> は、MySQL 8.0のデフォルトSQLモードに含まれる厳格なグループ化ルールである。<br>
<br>
基本的なルールを以下に示す。<br>
* <code>SELECT</code> リストの非集約列は、全て <code>GROUP BY</code> 句に含まれる必要がある。
* または、非集約列が <code>GROUP BY</code> 列に関数従属である必要がある。
<br>
エラーの例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- エラー: 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
 </syntaxhighlight>
<br>
正しい書き方を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 正: 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;
 </syntaxhighlight>
<br>
<code>ANY_VALUE</code> 関数を以下に示す。<br>
<code>ANY_VALUE</code> 関数は、グループ内の任意の値を返す関数である。<br>
<code>ONLY_FULL_GROUP_BY</code> モードでも、<code>ANY_VALUE</code> を使用することで非集約列を <code>SELECT</code> リストに含めることができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- ANY_VALUE関数で回避
 SELECT department_id, ANY_VALUE(first_name), COUNT(*)
 FROM employees
 GROUP BY department_id;
 
 -- 注意: どの値が返されるかは不定である
 </syntaxhighlight>
<br>
<code>ONLY_FULL_GROUP_BY</code> モードの無効化を以下に示す。<br>
<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', ''));
 </syntaxhighlight>
<br>
関数従属の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- employee_id がプライマリキーの場合、他の列は関数従属
 SELECT employee_id, first_name, last_name, COUNT(*)
 FROM employees
 GROUP BY employee_id;
 
 -- employee_id がプライマリキーであるため、first_name と last_name は
 -- employee_id に関数従属しており、エラーにならない
 </syntaxhighlight>
<br><br>

== HAVING句との組み合わせ ==
<code>HAVING</code> 句は、グループ化後の結果に対してフィルタリングを行う。<br>
<br>
<code>WHERE</code> 句 と <code>HAVING</code> 句の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ WHERE句とHAVING句の違い
|-
! 項目 !! WHERE句 !! HAVING句
|-
| フィルタリングのタイミング || グループ化前 || グループ化後
|-
| 条件の対象 || 個々の行 || グループ
|-
| 集約関数の使用 || 使用できない || 使用できる
|}
</center>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 従業員数が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;
 </syntaxhighlight>
<br>
<code>WHERE</code> 句 と <code>HAVING</code> 句の組み合わせを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 給与が5000以上の従業員を対象に、
 -- 従業員数が3人以上の部署の平均給与を計算
 SELECT department_id, COUNT(*), AVG(salary)
 FROM employees
 WHERE salary >= 5000
 GROUP BY department_id
 HAVING COUNT(*) >= 3;
 </syntaxhighlight>
<br>
複数の条件を指定する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 従業員数が5人以上、かつ平均給与が7000以上の部署
 SELECT department_id, COUNT(*), AVG(salary)
 FROM employees
 GROUP BY department_id
 HAVING COUNT(*) >= 5 AND AVG(salary) >= 7000;
 </syntaxhighlight>
<br>
エイリアスの使用を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 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;
 </syntaxhighlight>
<br><br>

== ウインドウ関数としての使用 ==
MySQL 8.0以降では、集約関数を <code>OVER()</code> 句と組み合わせてウインドウ関数として使用できる。<br>
ウインドウ関数として使用した場合、行を削減せず各行が保持される。<br>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 各従業員の給与と全体の平均給与を表示
 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 |
 -- +-------------+------------+--------+------------+
 </syntaxhighlight>
<br>
<code>PARTITION BY</code> 句を以下に示す。<br>
<code>PARTITION BY</code> 句は、ウインドウを部分的に分割して、部分ごとの集約を行う。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署ごとの平均給与を各行に表示
 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 |
 -- +-------------+------------+---------------+--------+------------------+
 </syntaxhighlight>
<br>
<code>ORDER BY</code> 句を以下に示す。<br>
<code>ORDER BY</code> 句は、ウインドウ内の行順序を指定する。<br>
これにより、累積合計や移動平均などの計算が可能になる。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 部署ごとの給与の累積合計
 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 |
 -- +-------------+------------+---------------+--------+-------------------+
 </syntaxhighlight>
<br>
フレーム指定を以下に示す。<br>
<code>ROWS</code> 関数 または <code>RANGE</code> 句を使用して、ウインドウ内の特定の行範囲に対する集約を行うことができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 現在行と直前の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;
 </syntaxhighlight>
<br>
下表に、フレーム指定のキーワードを示す。<br>
<br>
<center>
{| class="wikitable"
|+ フレーム指定のキーワード
|-
! キーワード !! 説明
|-
| ROWS || 物理的な行数でフレームを指定する
|-
| RANGE || 値の範囲でフレームを指定する
|-
| UNBOUNDED PRECEDING || パーティションの最初の行から
|-
| UNBOUNDED FOLLOWING || パーティションの最後の行まで
|-
| CURRENT ROW || 現在の行
|-
| n PRECEDING || 現在行からn行前
|-
| n FOLLOWING || 現在行からn行後
|}
</center>
<br>
* 累積合計の例
*: <syntaxhighlight lang="mysql">
 -- 部署ごとの給与の累積合計 (明示的なフレーム指定)
 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;
 </syntaxhighlight>
*: <br>
* 移動平均の例
*: <syntaxhighlight lang="mysql">
 -- 直近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;
 </syntaxhighlight>
<br>
下表に、サポートされる集約関数を示す。<br>
MySQL 8.0.12以降では、以下に示す集約関数がウインドウ関数としてサポートされている。<br>
<br>
<center>
{| class="wikitable"
|+ ウインドウ関数としてサポートされる集約関数 (MySQL 8.0.12以降)
|-
! 集約関数
|-
| COUNT
|-
| SUM
|-
| AVG
|-
| MAX
|-
| MIN
|-
| GROUP_CONCAT
|-
| JSON_ARRAYAGG
|-
| JSON_OBJECTAGG
|}
</center>
<br><br>

== パフォーマンス ==
==== インデックスの活用 ====
集約関数のパフォーマンスは、インデックスの有無に大きく影響される。<br>
<br>
<code>MAX</code> / <code>MIN</code> 関数とインデックスの関係を以下に示す。<br>
* インデックスが存在する列に対して <code>MAX</code> / <code>MIN</code> を使用する場合、インデックスを活用した高速な検索が可能である。
* これは、<code>ORDER BY column DESC LIMIT 1</code> (<code>MAX</code>) や <code>ORDER BY column ASC LIMIT 1</code> (<code>MIN</code>) と同等の最適化が行われるためである。
<br>
 <syntaxhighlight lang="mysql">
 -- インデックスが存在する場合、高速に実行される
 SELECT MAX(employee_id) FROM employees;
 
 -- EXPLAIN で確認
 EXPLAIN SELECT MAX(employee_id) FROM employees;
 -- type: index (インデックススキャン)
 </syntaxhighlight>
<br>
<code>COUNT</code> 関数とインデックスの関係を以下に示す。<br>
* InnoDBでは、<code>COUNT(*)</code> は最小の利用可能なセカンダリインデックスをスキャンする。
* プライマリキーより小さいセカンダリインデックスが存在する場合、そちらを優先的に使用する。
<br>
 <syntaxhighlight lang="mysql">
 -- セカンダリインデックスが存在する場合、そちらをスキャン
 SELECT COUNT(*) FROM employees;
 
 -- EXPLAIN で確認
 EXPLAIN SELECT COUNT(*) FROM employees;
 -- key: index_name (セカンダリインデックス)
 </syntaxhighlight>
<br>
<code>GROUP BY</code> とインデックスの関係を以下に示す。<br>
* <code>GROUP BY</code> 列にインデックスが存在する場合、ソート処理が不要になる場合がある。
* <code>EXPLAIN</code> で <code>Using temporary; Using filesort</code> が表示されない場合、インデックスが活用されている。
<br>
 <syntaxhighlight lang="mysql">
 -- 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 (インデックスのみでクエリを実行)
 </syntaxhighlight>
<br>
==== 大量データの集計 ====
大量のデータを集計する場合、パフォーマンスに注意が必要である。<br>
<br>
一時テーブルの使用を以下に示す。<br>
* <code>GROUP BY</code> でグループ化を行う場合、内部的に一時テーブル (TEMPORARY TABLE) が使用される可能性がある。
* 一時テーブルのサイズが <code>tmp_table_size</code> および <code>max_heap_table_size</code> を超える場合、ディスク上の一時テーブルが使用される。
* ディスク上の一時テーブルは、メモリ上の一時テーブルより遅い。
<br>
 <syntaxhighlight sql">
 -- 一時テーブルのサイズ制限を確認
 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 (一時テーブルを使用)
 </syntaxhighlight>
<br>
パフォーマンス改善の方法を以下に示す。<br>
* インデックスの追加
*: <code>GROUP BY</code> 列にインデックスを追加する。
* 一時テーブルサイズの拡張
*: <code>tmp_table_size</code> および <code>max_heap_table_size</code> を増やす。
* パーティショニング
*: 大量データをパーティション分割して、集計対象を絞る。
<br>
==== GROUP_CONCATの制限 ====
<code>GROUP_CONCAT</code> は、出力制限に注意が必要である。<br>
<br>
出力制限を以下に示す。<br>
* group_concat_max_len (デフォルト1024バイト)
*: <code>GROUP_CONCAT</code> の出力最大長を制御する。
*: この制限を超える場合、無警告で切り詰められる。
* max_allowed_packet
*: 最大パケットサイズも制限要因となる。
<br>
切り詰め時の挙動を以下に示す。<br>
* 制限を超えた場合、無警告で切り詰められる。
* 警告 (WARNING) は生成されない。
* <code>SHOW WARNINGS;</code> でも確認できない。
<br>
推奨される対策を以下に示す。<br>
* セッション単位で <code>group_concat_max_len</code> を拡張する。
* または、グローバルに <code>group_concat_max_len</code> を拡張する。
<br>
 <syntaxhighlight lang="mysql">
 -- セッション単位で拡張
 SET SESSION group_concat_max_len = 1000000;
 
 -- グローバルに拡張
 SET GLOBAL group_concat_max_len = 1000000;
 </syntaxhighlight>
<br>
==== DISTINCT性能 ====
<code>DISTINCT</code> を使用する集約関数は、追加の処理が必要なためパフォーマンスが低下する傾向がある。<br>
<br>
パフォーマンス特性を以下に示す。<br>
* COUNT(DISTINCT column)
*: <code>DISTINCT</code> 処理のため、<code>COUNT(column)</code> より遅い傾向がある。
*: 内部的に一時テーブルを使用する。
* SUM(DISTINCT column) / AVG(DISTINCT column)
*: 同様に、<code>DISTINCT</code> 処理のため遅い傾向がある。
<br>
推奨される対策を以下に示す。<br>
* インデックスの追加
*: <code>DISTINCT</code> 対象列にインデックスを追加する。
* サブクエリの使用
*: 大量データの場合、サブクエリで事前に <code>DISTINCT</code> を行うことで高速化できる場合がある。
<br>
 <syntaxhighlight lang="mysql">
 -- サブクエリで事前にDISTINCTを行う
 SELECT COUNT(*) FROM (
    SELECT DISTINCT department_id FROM employees
 ) AS distinct_depts;
 </syntaxhighlight>
<br>
==== WITH ROLLUPの影響 ====
<code>WITH ROLLUP</code> は、追加の処理が必要なためパフォーマンスに影響する。<br>
<br>
パフォーマンス特性を以下に示す。<br>
* <code>WITH ROLLUP</code> は、小計行を生成するため、追加の処理が必要である。
* 複数列でロールアップする場合、さらに処理が増加する。
<br>
推奨される対策を以下に示す。<br>
* インデックスの追加
*: <code>GROUP BY</code> 列にインデックスを追加する。
* UNION ALLの使用
*: 大量データの場合、<code>UNION ALL</code> で小計行を個別に計算する方が高速な場合がある。
<br>
 <syntaxhighlight lang="mysql">
 -- WITH ROLLUP の代わりに UNION ALL を使用
 SELECT department_id, COUNT(*) FROM employees GROUP BY department_id
 UNION ALL
 SELECT NULL, COUNT(*) FROM employees;
 </syntaxhighlight>
<br><br>

== バージョン別の機能差異 ==
MySQLのバージョンによって、集約関数の機能に差異がある。<br>
<br>
下表に、MySQL 5.7とMySQL 8.0の主要な違いを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 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
|}
</center>
<br>
MySQL 5.7での注意点を以下に示す。<br>
* ウインドウ関数は使用できない。
*: <code>OVER()</code> 句はサポートされていない。
*: 代替として、サブクエリや自己結合を使用する必要がある。
* GROUPING関数は使用できない。
*: <code>WITH ROLLUP</code> で小計行を識別するには、<code>NULL</code> チェックのみを使用する。
<br>
MySQL 8.0での新機能を以下に示す。<br>
* ウインドウ関数
*: 集約関数を <code>OVER()</code> 句と組み合わせて使用できる。
*: 累積合計、移動平均等の計算が可能になる。
* GROUPING関数
*: <code>WITH ROLLUP</code> で小計行を識別できる。
* 改善された集約関数のパフォーマンス
*: インデックスの活用が改善されている。
<br>
MySQL 5.7でウインドウ関数を代替する例を以下に示す。<br>
 <syntaxhighlight lang="mysql">
 -- 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;
 </syntaxhighlight>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,MySQL,SQL,Database,集約関数,Aggregate Functions,COUNT,SUM,AVG,MAX,MIN,GROUP_CONCAT,JSON_ARRAYAGG,JSON_OBJECTAGG,GROUP BY,HAVING,WITH ROLLUP,ONLY_FULL_GROUP_BY,ウインドウ関数,Window Functions,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLの集約関数の使い方とパフォーマンス最適化 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]