AVG([DISTINCT] expr) [over_clause]

expr の平均値を返します。 DISTINCT オプションを使用すると、個別の expr 値の平均を返すことができます。

一致する行がない場合、AVG() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は、セクション12.21.2「Window 関数の概念と構文」 で説明されているように、DISTINCT では使用できません。

mysql> SELECT student_name, AVG(test_score)
       FROM student
       GROUP BY student_name;

BIT_AND(expr) [over_clause]

expr 内のすべてのビットのビット単位の AND を返します。

結果の型は、関数の引数値がバイナリ文字列として評価されるか、数値として評価されるかによって異なります:

一致する行がない場合、BIT_AND() は引数値と同じ長さのニュートラル値 (すべて 1 に設定されたビット) を返します。

すべての値が NULL でないかぎり、NULL 値は結果に影響しません。 その場合、結果は引数値と同じ長さの中立値になります。

引数の評価および結果タイプの詳細は、セクション12.13「ビット関数と演算子」 の概要に関する説明を参照してください。

MySQL 8.0.12 では、over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は セクション12.21.2「Window 関数の概念と構文」 で説明されているとおりです。

BIT_OR(expr) [over_clause]

expr 内のすべてのビットのビット単位の OR を返します。

結果の型は、関数の引数値がバイナリ文字列として評価されるか、数値として評価されるかによって異なります:

一致する行がない場合、BIT_OR() は引数値と同じ長さのニュートラル値 (すべて 0 に設定されたビット) を返します。

すべての値が NULL でないかぎり、NULL 値は結果に影響しません。 その場合、結果は引数値と同じ長さの中立値になります。

引数の評価および結果タイプの詳細は、セクション12.13「ビット関数と演算子」 の概要に関する説明を参照してください。

MySQL 8.0.12 では、over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は セクション12.21.2「Window 関数の概念と構文」 で説明されているとおりです。

BIT_XOR(expr) [over_clause]

expr 内のすべてのビットのビット単位の XOR を返します。

結果の型は、関数の引数値がバイナリ文字列として評価されるか、数値として評価されるかによって異なります:

一致する行がない場合、BIT_XOR() は引数値と同じ長さのニュートラル値 (すべて 0 に設定されたビット) を返します。

すべての値が NULL でないかぎり、NULL 値は結果に影響しません。 その場合、結果は引数値と同じ長さの中立値になります。

引数の評価および結果タイプの詳細は、セクション12.13「ビット関数と演算子」 の概要に関する説明を参照してください。

MySQL 8.0.12 では、over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は セクション12.21.2「Window 関数の概念と構文」 で説明されているとおりです。

COUNT(expr) [over_clause]

SELECT ステートメントで取得された行に含まれる expr の非 NULL 値の数を返します。 結果は BIGINT 値になります。

一致する行がない場合、COUNT() は 0 を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

mysql> SELECT student.student_name,COUNT(*)
       FROM student,course
       WHERE student.student_id=course.student_id
       GROUP BY student_name;

COUNT(*) は、NULL 値が含まれるかどうかに関係なく、取得された行の数を返すという点で少し異なります。

InnoDB などのトランザクションストレージエンジンでは、正確な行数の格納に問題があります。 複数のトランザクションが同時に発生する可能性があり、それぞれがカウントに影響する可能性があります。

並列トランザクションでは同時にさまざまな数の行が「参照」される可能性があるため、InnoDB のテーブルには、行の内部的なカウントが保持されません。 したがって、SELECT COUNT(*) ステートメントでは、現在のトランザクションで参照可能な行のみがカウントされます。

MySQL 8.0.13 では、WHERE、GROUP BY などの追加句がない場合、InnoDB テーブルの SELECT COUNT(*) FROM tbl_name クエリーパフォーマンスはシングルスレッドワークロード用に最適化されます。

InnoDB は、インデックスまたはオプティマイザヒントがオプティマイザに別のインデックスを使用するように指示しないかぎり、使用可能な最小のセカンダリインデックスを横断することで SELECT COUNT(*) ステートメントを処理します。 セカンダリインデックスが存在しない場合、InnoDB はクラスタインデックスをスキャンして SELECT COUNT(*) ステートメントを処理します。

インデックスレコードがバッファプールに完全に含まれていない場合、SELECT COUNT(*) ステートメントの処理には時間がかかります。 カウントを高速化するには、カウンタテーブルを作成し、挿入および削除に従ってカウンタテーブルを更新できるようにします。 ただし、何千もの同時トランザクションが同じカウンタテーブルへの更新を開始している状況では、この方法は適切にスケーリングされない場合があります。 概算の行数で十分な場合は、SHOW TABLE STATUS を使用します。

InnoDB は、SELECT COUNT(*) と SELECT COUNT(1) の操作を同じ方法で処理します。 パフォーマンスに違いはありません。

MyISAM テーブルの場合、COUNT(*) は、SELECT があるテーブルから取得し、他のカラムが取得されず、WHERE 句がない場合に非常に迅速に返すように最適化されます。 例:

mysql> SELECT COUNT(*) FROM student;

この最適化は MyISAM テーブルにのみ適用されます。これは、正確な行数がこのストレージエンジン用に格納され、非常に迅速にアクセスできるためです。 COUNT(1) は、最初のカラムが NOT NULL として定義されている場合にのみ、同じ最適化の対象となります。

COUNT(DISTINCT expr,[expr...])

さまざまな非 NULL expr 値を含む行の数を返します。

一致する行がない場合、COUNT(DISTINCT) は 0 を返します。

mysql> SELECT COUNT(DISTINCT results) FROM student;

MySQL では、式のリストを指定することで、NULL が含まれない個別の式の組み合わせ数を取得できます。 標準 SQL では、COUNT(DISTINCT ...) 内部で、すべての式の連結を行う必要があります。

GROUP_CONCAT(expr)

この関数は、グループから連結された非 NULL 値を含む文字列の結果を返します。 非 NULL 値がない場合は、NULL を返します。 完全な構文は次のとおりです。

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

mysql> SELECT student_name,
         GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

または:

mysql> SELECT student_name,
         GROUP_CONCAT(DISTINCT test_score
                      ORDER BY test_score DESC SEPARATOR ' ')
       FROM student
       GROUP BY student_name;

MySQL では、式の組み合わせを連結した値を取得できます。 重複する値を除去するには、DISTINCT 句を使用します。 結果の値をソートするには、ORDER BY 句を使用します。 逆順でソートするには、ORDER BY 句のソートするカラムの名前に DESC (降順) キーワードを追加します。 デフォルトは昇順です。これは、ASC キーワードを使用することで明示的に指定できます。 グループ内の値間のデフォルトの区切り文字は、カンマ (,) です。 区切り文字を明示的に指定するには、SEPARATOR に続けて、グループ値の間に挿入される文字列リテラル値を指定します。 区切り文字を完全に除去するには、SEPARATOR '' を指定します。

結果は、group_concat_max_len システム変数で指定された最大長まで切り捨てられます。その変数のデフォルト値は 1024 です。 さらに高い値にも設定できますが、戻り値の有効な最大長は、max_allowed_packet の値によって制約されます。 実行時に group_concat_max_len の値を変更するための構文は、次のとおりです。ここで、val は符号なし整数です。

SET [GLOBAL | SESSION] group_concat_max_len = val;

戻り値は、引数が非バイナリとバイナリのどちらの文字列であるのかに応じて、非バイナリ文字列またはバイナリ文字列になります。 結果の型は、group_concat_max_len が 512 以下の場合 (この場合、結果の型は VARCHAR または VARBINARY です) を除いて、TEXT または BLOB です。

CONCAT() および CONCAT_WS(): セクション12.8「文字列関数および演算子」も参照してください。

JSON_ARRAYAGG(col_or_expr) [over_clause]

結果セットを、要素が行で構成される単一の JSON 配列として集計します。 この配列の要素の順序が定義されていません。 この関数は、単一の値に評価されるカラムまたは式に対して機能します。 結果に行が含まれていない場合、またはエラーが発生した場合は、NULL を返します。

MySQL 8.0.14 では、over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は セクション12.21.2「Window 関数の概念と構文」 で説明されているとおりです。

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
     > FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

JSON_OBJECTAGG(key, value) [over_clause]

2 つのカラム名または式を引数として取ります。最初のカラム名または式はキーとして使用され、2 番目のカラム名または式は値として使用され、キーと値のペアを含む JSON オブジェクトを返します。 結果に行が含まれていない場合、またはエラーが発生した場合は、NULL を返します。 いずれかのキー名が NULL であるか、引数の数が 2 でない場合、エラーが発生します。

MySQL 8.0.14 では、over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は セクション12.21.2「Window 関数の概念と構文」 で説明されているとおりです。

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
     > FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

重複キーの処理.  この関数の結果が正規化されると、重複するキーを持つ値は破棄されます。 重複キーを許可しない MySQL JSON データ型指定に従うと、返されるオブジェクト (「「最後の重複キー優先」」) でそのキーで最後に検出された値のみが使用されます。 つまり、SELECT のカラムでこの関数を使用した結果は、戻される行の順序に依存する可能性がありますが、これは保証されていません。

ウィンドウ関数として使用する場合、フレーム内に重複するキーがあると、そのキーの最後の値のみが結果に表示されます。 ORDER BY 仕様で値が特定の順序であることが保証されている場合、フレームの最後の行のキーの値は決定的です。 そうでない場合、キーの結果値は非決定的です。

次について考えます。

mysql> CREATE TABLE t(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t;
Query OK, 3 rows affected (0.08 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    5 |
| key  |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 4}           |
+----------------------+
1 row in set (0.00 sec)

最後のクエリーから選択されたキーは非決定的です。 特定のキーの順序付けが必要な場合は、フレーム行に特定の順序を適用する ORDER BY 仕様に OVER 句を含めることで、JSON_OBJECTAGG() をウィンドウ関数として起動できます。 次の例は、いくつかの異なるフレーム仕様について、ORDER BY の有無に応じて何が起こるかを示しています。

ORDER BY がない場合、フレームはパーティション全体です:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER () AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 4}  |
| {"key": 4}  |
| {"key": 4}  |
+-------------+

ORDER BY では、フレームはデフォルトの RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (昇順と降順の両方) です:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
| {"key": 4}  |
| {"key": 5}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 4}  |
| {"key": 3}  |
+-------------+

ORDER BY およびパーティション全体の明示的なフレームを使用する場合:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS json_object
       FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 5}  |
| {"key": 5}  |
+-------------+

特定のキー値 (最小値や最大値など) を返すには、適切なクエリーに LIMIT 句を含めます。 例:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
+-------------+

追加情報および例については、JSON 値の正規化、マージおよび自動ラップを参照してください。

MAX([DISTINCT] expr) [over_clause]

expr の最大値を返します。 MAX() には、文字列の引数を指定できます。このような場合は、最大の文字列値が返されます。 セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。 DISTINCT キーワードを使用すると、個別の expr 値の最大を検索できます。ただし、DISTINCT を省略した場合と同じ結果が生成されます。

一致する行がない場合、MAX() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は、セクション12.21.2「Window 関数の概念と構文」 で説明されているように、DISTINCT では使用できません。

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

現在、MySQL の MAX() では、ENUM と SET カラムが、セット内の文字列の相対位置ではなく、文字列値について比較されます。 これは、ORDER BY による比較方法とは異なります。

MIN([DISTINCT] expr) [over_clause]

expr の最小値を返します。 MIN() には、文字列の引数を指定できます。このような場合は、最小の文字列値が返されます。 セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。 DISTINCT キーワードを使用すると、個別の expr 値の最小を検索できます。ただし、DISTINCT を省略した場合と同じ結果が生成されます。

一致する行がない場合、MIN() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は、セクション12.21.2「Window 関数の概念と構文」 で説明されているように、DISTINCT では使用できません。

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

現在、MySQL の MIN() では、ENUM と SET カラムが、セット内の文字列の相対位置ではなく、文字列値について比較されます。 これは、ORDER BY による比較方法とは異なります。

STD(expr) [over_clause]

expr の母標準偏差を返します。 STD() は、MySQL 拡張機能として提供される標準 SQL 関数 STDDEV_POP() のシノニムです。

一致する行がない場合、STD() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

STDDEV(expr) [over_clause]

expr の母標準偏差を返します。 STDDEV() は、Oracle との互換性のために提供されている標準 SQL 関数 STDDEV_POP() のシノニムです。

一致する行がない場合、STDDEV() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

STDDEV_POP(expr) [over_clause]

expr の母標準偏差 (VAR_POP() の平方根) を返します。 STD() または STDDEV() を使用することもできます。これらは同等ですが、標準 SQL ではありません。

一致する行がない場合、STDDEV_POP() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

STDDEV_SAMP(expr) [over_clause]

expr の標本標準偏差 (VAR_SAMP() の平方根) を返します。

一致する行がない場合、STDDEV_SAMP() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

SUM([DISTINCT] expr) [over_clause]

expr の集計を返します。 戻り値のセットに行が含まれていない場合、SUM() は NULL を返します。 DISTINCT キーワードを使用すると、個別の expr 値のみを集計できます。

一致する行がない場合、SUM() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause は、セクション12.21.2「Window 関数の概念と構文」 で説明されているように、DISTINCT では使用できません。

VAR_POP(expr) [over_clause]

expr の母標準分散を返します。 行は標本ではなく、母集団全体とみなされるため、行の数が分母とみなされます。 また、VARIANCE() を使用することもできます。これは同等ですが、標準 SQL ではありません。

一致する行がない場合、VAR_POP() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

VAR_SAMP(expr) [over_clause]

expr の標本分散を返します。 つまり、分母は行の数から 1 を引いたものです。

一致する行がない場合、VAR_SAMP() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。

VARIANCE(expr) [over_clause]

expr の母標準分散を返します。 VARIANCE() は、MySQL 拡張機能として提供される標準 SQL 関数 VAR_POP() のシノニムです。

一致する行がない場合、VARIANCE() は NULL を返します。

over_clause が存在する場合、この関数はウィンドウ関数として実行されます。over_clause については、セクション12.21.2「Window 関数の概念と構文」 を参照してください。