Movatterモバイル変換

Table 14.29 Aggregate Functions

• AVG([DISTINCT]expr) [over_clause]

  Returns the average value ofexpr. TheDISTINCT option can be used to return the average of the distinct values ofexpr.

  If there are no matching rows,AVG() returnsNULL. The function also returnsNULL ifexpr isNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”; it cannot be used withDISTINCT.

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

• BIT_AND(expr) [over_clause]

  Returns the bitwiseAND of all bits inexpr.

  The result type depends on whether the function argument values are evaluated as binary strings or numbers:

  • Binary-string evaluation occurs when the argument values have a binary string type, and the argument is not a hexadecimal literal, bit literal, orNULL literal. Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.

  • Binary-string evaluation produces a binary string of the same length as the argument values. If argument values have unequal lengths, anER_INVALID_BITWISE_OPERANDS_SIZE error occurs. If the argument size exceeds 511 bytes, anER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE error occurs. Numeric evaluation produces an unsigned 64-bit integer.

  If there are no matching rows,BIT_AND() returns a neutral value (all bits set to 1) having the same length as the argument values.

  NULL values do not affect the result unless all values areNULL. In that case, the result is a neutral value having the same length as the argument values.

  For more information discussion about argument evaluation and result types, see the introductory discussion inSection 14.12, “Bit Functions and Operators”.

  IfBIT_AND() is invoked from within themysql client, binary string results display using hexadecimal notation, depending on the value of the--binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.

  As of MySQL 8.0.12, this function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• BIT_OR(expr) [over_clause]

  Returns the bitwiseOR of all bits inexpr.

  The result type depends on whether the function argument values are evaluated as binary strings or numbers:

  • Binary-string evaluation occurs when the argument values have a binary string type, and the argument is not a hexadecimal literal, bit literal, orNULL literal. Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.

  • Binary-string evaluation produces a binary string of the same length as the argument values. If argument values have unequal lengths, anER_INVALID_BITWISE_OPERANDS_SIZE error occurs. If the argument size exceeds 511 bytes, anER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE error occurs. Numeric evaluation produces an unsigned 64-bit integer.

  If there are no matching rows,BIT_OR() returns a neutral value (all bits set to 0) having the same length as the argument values.

  NULL values do not affect the result unless all values areNULL. In that case, the result is a neutral value having the same length as the argument values.

  For more information discussion about argument evaluation and result types, see the introductory discussion inSection 14.12, “Bit Functions and Operators”.

  IfBIT_OR() is invoked from within themysql client, binary string results display using hexadecimal notation, depending on the value of the--binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.

  As of MySQL 8.0.12, this function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• BIT_XOR(expr) [over_clause]

  Returns the bitwiseXOR of all bits inexpr.

  The result type depends on whether the function argument values are evaluated as binary strings or numbers:

  • Binary-string evaluation occurs when the argument values have a binary string type, and the argument is not a hexadecimal literal, bit literal, orNULL literal. Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.

  • Binary-string evaluation produces a binary string of the same length as the argument values. If argument values have unequal lengths, anER_INVALID_BITWISE_OPERANDS_SIZE error occurs. If the argument size exceeds 511 bytes, anER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE error occurs. Numeric evaluation produces an unsigned 64-bit integer.

  If there are no matching rows,BIT_XOR() returns a neutral value (all bits set to 0) having the same length as the argument values.

  NULL values do not affect the result unless all values areNULL. In that case, the result is a neutral value having the same length as the argument values.

  For more information discussion about argument evaluation and result types, see the introductory discussion inSection 14.12, “Bit Functions and Operators”.

  IfBIT_XOR() is invoked from within themysql client, binary string results display using hexadecimal notation, depending on the value of the--binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.

  As of MySQL 8.0.12, this function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• COUNT(expr) [over_clause]

  Returns a count of the number of non-NULL values ofexpr in the rows retrieved by aSELECT statement. The result is aBIGINT value.

  If there are no matching rows,COUNT() returns0.COUNT(NULL) returns 0.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

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

  COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they containNULL values.

  For transactional storage engines such asInnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.

  InnoDB does not keep an internal count of rows in a table because concurrent transactions might“see” different numbers of rows at the same time. Consequently,SELECT COUNT(*) statements only count rows visible to the current transaction.

  As of MySQL 8.0.13,SELECT COUNT(*) FROMtbl_name query performance forInnoDB tables is optimized for single-threaded workloads if there are no extra clauses such asWHERE orGROUP BY.

  InnoDB processesSELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present,InnoDB processesSELECT COUNT(*) statements by scanning the clustered index.

  Processing ofSELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, useSHOW TABLE STATUS.

  InnoDB handlesSELECT COUNT(*) andSELECT COUNT(1) operations in the same way. There is no performance difference.

  ForMyISAM tables,COUNT(*) is optimized to return very quickly if theSELECT retrieves from one table, no other columns are retrieved, and there is noWHERE clause. For example:

  mysql> SELECT COUNT(*) FROM student;

  This optimization only applies toMyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly.COUNT(1) is only subject to the same optimization if the first column is defined asNOT NULL.

• COUNT(DISTINCTexpr,[expr...])

  Returns a count of the number of rows with different non-NULLexpr values.

  If there are no matching rows,COUNT(DISTINCT) returns0.

  mysql> SELECT COUNT(DISTINCT results) FROM student;

  In MySQL, you can obtain the number of distinct expression combinations that do not containNULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions insideCOUNT(DISTINCT ...).

• GROUP_CONCAT(expr)

  This function returns a string result with the concatenated non-NULL values from a group. It returnsNULL if there are no non-NULL values. The full syntax is as follows:

  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;

  Or:

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

  In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use theDISTINCT clause. To sort values in the result, use theORDER BY clause. To sort in reverse order, add theDESC (descending) keyword to the name of the column you are sorting by in theORDER BY clause. The default is ascending order; this may be specified explicitly using theASC keyword. The default separator between values in a group is comma (,). To specify a separator explicitly, useSEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specifySEPARATOR ''.

  The result is truncated to the maximum length that is given by thegroup_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value ofmax_allowed_packet. The syntax to change the value ofgroup_concat_max_len at runtime is as follows, whereval is an unsigned integer:

  SET [GLOBAL | SESSION] group_concat_max_len =val;

  The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type isTEXT orBLOB unlessgroup_concat_max_len is less than or equal to 512, in which case the result type isVARCHAR orVARBINARY.

  IfGROUP_CONCAT() is invoked from within themysql client, binary string results display using hexadecimal notation, depending on the value of the--binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.

  See alsoCONCAT() andCONCAT_WS():Section 14.8, “String Functions and Operators”.

• JSON_ARRAYAGG(col_or_expr) [over_clause]

  Aggregates a result set as a singleJSON array whose elements consist of the rows. The order of elements in this array is undefined. The function acts on a column or an expression that evaluates to a single value. ReturnsNULL if the result contains no rows, or in the event of an error. Ifcol_or_expr isNULL, the function returns an array of JSON[null] elements.

  As of MySQL 8.0.14, this function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

  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]

  Takes two column names or expressions as arguments, the first of these being used as a key and the second as a value, and returns a JSON object containing key-value pairs. ReturnsNULL if the result contains no rows, or in the event of an error. An error occurs if any key name isNULL or the number of arguments is not equal to 2.

  As of MySQL 8.0.14, this function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

  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)

  Duplicate key handling.  When the result of this function is normalized, values having duplicate keys are discarded. In keeping with the MySQLJSON data type specification that does not permit duplicate keys, only the last value encountered is used with that key in the returned object (“last duplicate key wins”). This means that the result of using this function on columns from aSELECT can depend on the order in which the rows are returned, which is not guaranteed.

  When used as a window function, if there are duplicate keys within a frame, only the last value for the key is present in the result. The value for the key from the last row in the frame is deterministic if theORDER BY specification guarantees that the values have a specific order. If not, the resulting value of the key is nondeterministic.

  Consider the following:

  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)

  The key chosen from the last query is nondeterministic. If the query does not useGROUP BY (which usually imposes its own ordering regardless) and you prefer a particular key ordering, you can invokeJSON_OBJECTAGG() as a window function by including anOVER clause with anORDER BY specification to impose a particular order on frame rows. The following examples show what happens with and withoutORDER BY for a few different frame specifications.

  WithoutORDER BY, the frame is the entire partition:

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

  WithORDER BY, where the frame is the default ofRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (in both ascending and descending order):

  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}  |+-------------+

  WithORDER BY and an explicit frame of the entire partition:

  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}  |+-------------+

  To return a particular key value (such as the smallest or largest), include aLIMIT clause in the appropriate query. For example:

  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}  |+-------------+

  SeeNormalization, Merging, and Autowrapping of JSON Values, for additional information and examples.

• MAX([DISTINCT]expr) [over_clause]

  Returns the maximum value ofexpr.MAX() may take a string argument; in such cases, it returns the maximum string value. SeeSection 10.3.1, “How MySQL Uses Indexes”. TheDISTINCT keyword can be used to find the maximum of the distinct values ofexpr, however, this produces the same result as omittingDISTINCT.

  If there are no matching rows, or ifexpr isNULL,MAX() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”; it cannot be used withDISTINCT.

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

  ForMAX(), MySQL currently comparesENUM andSET columns by their string value rather than by the string's relative position in the set. This differs from howORDER BY compares them.

• MIN([DISTINCT]expr) [over_clause]

  Returns the minimum value ofexpr.MIN() may take a string argument; in such cases, it returns the minimum string value. SeeSection 10.3.1, “How MySQL Uses Indexes”. TheDISTINCT keyword can be used to find the minimum of the distinct values ofexpr, however, this produces the same result as omittingDISTINCT.

  If there are no matching rows, or ifexpr isNULL,MIN() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”; it cannot be used withDISTINCT.

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

  ForMIN(), MySQL currently comparesENUM andSET columns by their string value rather than by the string's relative position in the set. This differs from howORDER BY compares them.

• STD(expr) [over_clause]

  Returns the population standard deviation ofexpr.STD() is a synonym for the standard SQL functionSTDDEV_POP(), provided as a MySQL extension.

  If there are no matching rows, or ifexpr isNULL,STD() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• STDDEV(expr) [over_clause]

  Returns the population standard deviation ofexpr.STDDEV() is a synonym for the standard SQL functionSTDDEV_POP(), provided for compatibility with Oracle.

  If there are no matching rows, or ifexpr isNULL,STDDEV() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• STDDEV_POP(expr) [over_clause]

  Returns the population standard deviation ofexpr (the square root ofVAR_POP()). You can also useSTD() orSTDDEV(), which are equivalent but not standard SQL.

  If there are no matching rows, or ifexpr isNULL,STDDEV_POP() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• STDDEV_SAMP(expr) [over_clause]

  Returns the sample standard deviation ofexpr (the square root ofVAR_SAMP().

  If there are no matching rows, or ifexpr isNULL,STDDEV_SAMP() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• SUM([DISTINCT]expr) [over_clause]

  Returns the sum ofexpr. If the return set has no rows,SUM() returnsNULL. TheDISTINCT keyword can be used to sum only the distinct values ofexpr.

  If there are no matching rows, or ifexpr isNULL,SUM() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”; it cannot be used withDISTINCT.

• VAR_POP(expr) [over_clause]

  Returns the population standard variance ofexpr. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. You can also useVARIANCE(), which is equivalent but is not standard SQL.

  If there are no matching rows, or ifexpr isNULL,VAR_POP() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• VAR_SAMP(expr) [over_clause]

  Returns the sample variance ofexpr. That is, the denominator is the number of rows minus one.

  If there are no matching rows, or ifexpr isNULL,VAR_SAMP() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.

• VARIANCE(expr) [over_clause]

  Returns the population standard variance ofexpr.VARIANCE() is a synonym for the standard SQL functionVAR_POP(), provided as a MySQL extension.

  If there are no matching rows, or ifexpr isNULL,VARIANCE() returnsNULL.

  This function executes as a window function ifover_clause is present.over_clause is as described inSection 14.20.2, “Window Function Concepts and Syntax”.