Movatterモバイル変換

表 12.25 「集計関数」

• 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 を返します。

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

  • バイナリ文字列の評価は、引数値がバイナリ文字列型で、引数が 16 進数リテラル、ビットリテラルまたはNULL リテラルでない場合に発生します。 それ以外の場合は数値の評価が行われ、必要に応じて引数値が符号なし 64 ビット整数に変換されます。

  • バイナリ文字列の評価では、引数値と同じ長さのバイナリ文字列が生成されます。 引数値の長さが等しくない場合は、ER_INVALID_BITWISE_OPERANDS_SIZE エラーが発生します。 引数のサイズが 511 バイトを超えると、ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE エラーが発生します。 数値評価では符号なし 64 ビット整数が生成されます。

  一致する行がない場合、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 を返します。

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

  • バイナリ文字列の評価は、引数値がバイナリ文字列型で、引数が 16 進数リテラル、ビットリテラルまたはNULL リテラルでない場合に発生します。 それ以外の場合は数値の評価が行われ、必要に応じて引数値が符号なし 64 ビット整数に変換されます。

  • バイナリ文字列の評価では、引数値と同じ長さのバイナリ文字列が生成されます。 引数値の長さが等しくない場合は、ER_INVALID_BITWISE_OPERANDS_SIZE エラーが発生します。 引数のサイズが 511 バイトを超えると、ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE エラーが発生します。 数値評価では符号なし 64 ビット整数が生成されます。

  一致する行がない場合、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 を返します。

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

  • バイナリ文字列の評価は、引数値がバイナリ文字列型で、引数が 16 進数リテラル、ビットリテラルまたはNULL リテラルでない場合に発生します。 それ以外の場合は数値の評価が行われ、必要に応じて引数値が符号なし 64 ビット整数に変換されます。

  • バイナリ文字列の評価では、引数値と同じ長さのバイナリ文字列が生成されます。 引数値の長さが等しくない場合は、ER_INVALID_BITWISE_OPERANDS_SIZE エラーが発生します。 引数のサイズが 511 バイトを超えると、ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE エラーが発生します。 数値評価では符号なし 64 ビット整数が生成されます。

  一致する行がない場合、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(*) FROMtbl_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(DISTINCTexpr,[expr...])

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

  一致する行がない場合、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 ...]]             [SEPARATORstr_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)

  重複キーの処理.  この関数の結果が正規化されると、重複するキーを持つ値は破棄されます。 重複キーを許可しない MySQLJSON データ型指定に従うと、返されるオブジェクト (「「最後の重複キー優先」」) でそのキーで最後に検出された値のみが使用されます。 つまり、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: 0mysql> 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: 0mysql> 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 関数の概念と構文」 を参照してください。