This section describes aggregate functions that operate on sets of values. 本节介绍对值集进行操作的聚合函数。They are often used with a 它们通常与GROUP BY clause to group values into subsets.GROUP BY子句一起用于将值分组到子集中。
Table 12.25 Aggregate Functions聚合函数
AVG() | |
BIT_AND() | |
BIT_OR() | |
BIT_XOR() | |
COUNT() | |
COUNT(DISTINCT) | |
GROUP_CONCAT() | |
JSON_ARRAYAGG() | |
JSON_OBJECTAGG() | |
MAX() | |
MIN() | |
STD() | |
STDDEV() | |
STDDEV_POP() | |
STDDEV_SAMP() | |
SUM() | |
VAR_POP() | |
VAR_SAMP() | |
VARIANCE() |
Unless otherwise stated, aggregate functions ignore 除非另有说明，否则聚合函数忽略NULL values.NULL值。
If you use an aggregate function in a statement containing no 如果在不包含GROUP BY clause, it is equivalent to grouping on all rows. GROUP BY子句的语句中使用聚合函数，则相当于对所有行进行分组。For more information, see Section 12.20.3, “MySQL Handling of GROUP BY”.有关更多信息，请参阅第12.20.3节，“GROUP BY的MySQL处理”。
Most aggregate functions can be used as window functions. 大多数聚合函数都可以用作窗口函数。Those that can be used this way are signified in their syntax description by 可以用这种方法的那些在语法描述中用[, representing an optional over_clause]OVER clause. [表示，表示可选的over_clause]OVER子句。over_clause is described in Section 12.21.2, “Window Function Concepts and Syntax”, which also includes other information about window function usage.over_clause在第12.21.2节，“窗口函数概念和语法”中进行了描述，其中还包括有关窗口函数用法的其他信息。
For numeric arguments, the variance and standard deviation functions return a 对于数值参数，方差和标准差函数返回一个DOUBLE value. DOUBLE值。The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).SUM()和AVG()函数为精确值参数（INTEGER或DECIMAL）返回一个DECIMAL值，为近似值参数（FLOAT或DOUBLE）返回一个DOUBLE值。
The SUM() and AVG() aggregate functions do not work with temporal values. SUM()和AVG()聚合函数不能处理时态值。(They convert the values to numbers, losing everything after the first nonnumeric character.) （它们将值转换为数字，丢失第一个非数字字符之后的所有内容。）To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. 要解决此问题，请转换为数字单位，执行聚合操作，然后转换回时间值。Examples:示例：
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROMtbl_name; SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROMtbl_name;
Functions such as 如果需要，SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. SUM()或AVG()等需要数字参数的函数会将参数转换为数字。For 对于SET or ENUM values, the cast operation causes the underlying numeric value to be used.SET或ENUM值，强制转换操作将使用基础数值。
The BIT_AND(), BIT_OR(), and BIT_XOR() aggregate functions perform bit operations. BIT_AND()、BIT_OR()和BIT_XOR()聚合函数执行位操作。Prior to MySQL 8.0, bit functions and operators required 在MySQL8.0之前，位函数和运算符需要BIGINT (64-bit integer) arguments and returned BIGINT values, so they had a maximum range of 64 bits. BIGINT（64位整数）参数并返回BIGINT值，因此它们的最大范围为64位。Non-非BIGINT arguments were converted to BIGINT prior to performing the operation and truncation could occur.BIGINT参数在执行操作之前已转换为BIGINT，可能会发生截断。
In MySQL 8.0, bit functions and operators permit binary string type arguments (在MySQL 8.0中，位函数和运算符允许二进制字符串类型的参数（BINARY, VARBINARY, and the BLOB types) and return a value of like type, which enables them to take arguments and produce return values larger than 64 bits. BINARY、VARBINARY和BLOB类型）并返回类似类型的值，这使它们能够获取参数并生成大于64位的返回值。For discussion about argument evaluation and result types for bit operations, see the introductory discussion in Section 12.13, “Bit Functions and Operators”.有关位运算的参数计算和结果类型的讨论，请参阅第12.13节，“位函数和运算符”中的介绍性讨论。
AVG([DISTINCT] expr) [over_clause]
Returns the average value of 返回exprexpr的平均值。The DISTINCT option can be used to return the average of the distinct values of expr.DISTINCT选项可用于返回expr的各不相同值的平均值。
If there are no matching rows, 如果没有匹配的行，AVG() returns NULL.AVG()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”; it cannot be used with DISTINCT.over_clause如第12.21.2节，“窗口函数概念和语法”所述；不能与DISTINCT一起使用。
mysql>SELECT student_name, AVG(test_score)FROM studentGROUP BY student_name;
Returns the bitwise 返回AND of all bits in expr.expr中所有位的按位与。
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, or 当参数值具有二进制字符串类型，并且参数不是十六进制文字、位文字或NULL literal. NULL文字时，将进行二进制字符串计算。Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.否则将进行数值计算，并根据需要将参数值转换为无符号64位整数。
Binary-string evaluation produces a binary string of the same length as the argument values. 二进制字符串求值生成与参数值长度相同的二进制字符串。If argument values have unequal lengths, an 如果参数值的长度不等，则会发生ER_INVALID_BITWISE_OPERANDS_SIZE error occurs. ER_INVALID_BITWISE_OPERANDS_SIZE错误。If the argument size exceeds 511 bytes, an 如果参数大小超过511字节，则会发生ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE error occurs. ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE错误。Numeric evaluation produces an unsigned 64-bit integer.数值计算产生一个无符号的64位整数。
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.BIT_AND()返回与参数值长度相同的中性值（所有位都设置为1）。
NULL values do not affect the result unless all values are NULL. NULL值不影响结果，除非所有值都为NULL。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 in Section 12.13, “Bit Functions and Operators”.有关参数求值和结果类型的更多信息，请参阅第12.13节，“位函数和运算符”中的介绍性讨论。
If 如果从mysql客户机中调用位BIT_AND() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. _AND()，则二进制字符串结果将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql-MySQL命令行客户端”。
As of MySQL 8.0.12, this function executes as a window function if 从MySQL8.0.12开始，如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
Returns the bitwise 返回OR of all bits in expr.expr中所有位的按位或。
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, or 当参数值具有二进制字符串类型，并且参数不是十六进制文字、位文字或NULL literal. NULL文字时，将进行二进制字符串计算。Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.否则将进行数值计算，并根据需要将参数值转换为无符号64位整数。
Binary-string evaluation produces a binary string of the same length as the argument values. 二进制字符串求值生成与参数值长度相同的二进制字符串。If argument values have unequal lengths, an 如果参数值的长度不等，则会发生ER_INVALID_BITWISE_OPERANDS_SIZE error occurs. ER_INVALID_BITWISE_OPERANDS_SIZE错误。If the argument size exceeds 511 bytes, an 如果参数大小超过511字节，则会发生ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE error occurs. ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE错误。Numeric evaluation produces an unsigned 64-bit integer.数值计算产生一个无符号的64位整数。
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.BIT_OR()返回与参数值长度相同的中性值（所有位都设置为0）。
NULL values do not affect the result unless all values are NULL. NULL值不影响结果，除非所有值都为NULL。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 in Section 12.13, “Bit Functions and Operators”.有关参数求值和结果类型的更多信息，请参阅第12.13节，“位函数和运算符”中的介绍性讨论。
If 如果从mysql客户机中调用BIT_OR() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. BIT_OR()，则二进制字符串结果将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql-MySQL命令行客户端”。
As of MySQL 8.0.12, this function executes as a window function if 从MySQL8.0.12开始，如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
Returns the bitwise 返回XOR of all bits in expr.expr中所有位的按位异或。
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, or 当参数值具有二进制字符串类型，并且参数不是十六进制文字、位文字或NULL literal. NULL文字时，将进行二进制字符串计算。Numeric evaluation occurs otherwise, with argument value conversion to unsigned 64-bit integers as necessary.否则将进行数值计算，并根据需要将参数值转换为无符号64位整数。
Binary-string evaluation produces a binary string of the same length as the argument values. 二进制字符串求值生成与参数值长度相同的二进制字符串。If argument values have unequal lengths, an 如果参数值的长度不等，则会发生ER_INVALID_BITWISE_OPERANDS_SIZE error occurs. ER_INVALID_BITWISE_OPERANDS_SIZE错误。If the argument size exceeds 511 bytes, an 如果参数大小超过511字节，则会发生ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE error occurs. ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE错误。Numeric evaluation produces an unsigned 64-bit integer.数值计算产生一个无符号的64位整数。
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.BIT_XOR()返回与参数值长度相同的中性值（所有位都设置为0）。
NULL values do not affect the result unless all values are NULL. NULL值不影响结果，除非所有值都为NULL。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 in Section 12.13, “Bit Functions and Operators”.有关参数求值和结果类型的更多信息，请参阅第12.13节，“位函数和运算符”中的介绍性讨论。
If 如果从mysql客户机中调用BIT_XOR() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. BIT_XOR()，则二进制字符串结果将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql-mysql命令行客户端”。
As of MySQL 8.0.12, this function executes as a window function if 从MySQL8.0.12开始，如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
Returns a count of the number of non-返回NULL values of expr in the rows retrieved by a SELECT statement. SELECT语句检索到的行中expr的非NULL值数的计数。The result is a 结果是一个BIGINT value.BIGINT值。
If there are no matching rows, 如果没有匹配的行，COUNT() returns 0.COUNT()返回0。
This function executes as a window function if 如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
mysql>SELECT student.student_name,COUNT(*)FROM student,courseWHERE student.student_id=course.student_idGROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.COUNT(*)有些不同，因为它返回检索到的行数的计数，不管它们是否包含空值。
For transactional storage engines such as 对于事务性存储引擎（如InnoDB, storing an exact row count is problematic. InnoDB），存储精确的行数是有问题的。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.SELECT COUNT(*)语句只对当前事务可见的行进行计数。
As of MySQL 8.0.13, 从MySQL8.0.13开始，如果没有诸如SELECT COUNT(*) FROM query performance for tbl_nameInnoDB tables is optimized for single-threaded workloads if there are no extra clauses such as WHERE or GROUP BY.WHERE或GROUP BY之类的额外子句，那么针对InnoDB表实施的SELECT COUNT(*) FROM 查询针对单线程工作负载进行了优化。tbl_name
InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. InnoDB通过遍历最小的可用辅助索引来处理SELECT COUNT (*)语句，除非索引或优化器提示指示优化器使用不同的索引。If a secondary index is not present, 如果没有辅助索引，InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.InnoDB将通过扫描聚集索引来处理SELECT COUNT (*)语句。
Processing 如果索引记录不完全在缓冲池中，则处理SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. SELECT COUNT (*)语句需要一些时间。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, use 如果近似行数足够，请使用SHOW TABLE STATUS.SHOW TABLE STATUS。
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. InnoDB以相同的方式处理SELECT COUNT (*)和SELECT COUNT(1)操作。There is no performance difference.没有性能差异。
For 对于MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. MyISAM表，COUNT(*)经过优化，可以在SELECT从一个表中检索、不检索其他列并且没有WHERE子句的情况下快速返回。For example:例如：
mysql> SELECT COUNT(*) FROM student;
This optimization only applies to 这种优化只适用于MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. MyISAM表，因为为这个存储引擎存储了精确的行计数，并且可以非常快速地访问。COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.COUNT(1)仅在第一列定义为NOT NULL时才进行相同的优化。
COUNT(DISTINCT expr,[expr...])
Returns a count of the number of rows with different non-返回具有不同非NULL expr values.NULL的expr值的行数的计数。
If there are no matching rows, 如果没有匹配的行，COUNT(DISTINCT) returns 0.COUNT(DISTINCT)返回0。
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can obtain the number of distinct expression combinations that do not contain 在MySQL中，通过给出表达式列表，可以获得不包含NULL by giving a list of expressions. NULL的不同表达式组合的数量。In standard SQL, you would have to do a concatenation of all expressions inside 在标准SQL中，必须将COUNT(DISTINCT ...).COUNT(DISTINCT ...)中的所有表达式串联起来。
This function returns a string result with the concatenated non-此函数返回一个字符串结果，其中包含组中串联的非NULL values from a group. NULL值。It returns 如果没有非NULL if there are no non-NULL values. NULL值，则返回NULL。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 studentGROUP BY student_name;
Or:或：
mysql>SELECT student_name,GROUP_CONCAT(DISTINCT test_scoreORDER BY test_score DESC SEPARATOR ' ')FROM studentGROUP BY student_name;
In MySQL, you can get the concatenated values of expression combinations. 在MySQL中，可以获得表达式组合的串联值。To eliminate duplicate values, use the 要消除重复值，请使用DISTINCT clause. DISTINCT子句。To sort values in the result, use the 要对结果中的值进行排序，请使用ORDER BY clause. ORDER BY子句。To sort in reverse order, add the 若要按相反顺序排序，请在DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. ORDER BY子句中将DESC（降序）关键字添加到要排序的列的名称中。The default is ascending order; this may be specified explicitly using the 默认为升序；这可以使用ASC keyword. ASC关键字显式指定。The default separator between values in a group is comma (组中值之间的默认分隔符是逗号（,). ,）。To specify a separator explicitly, use 要显式指定分隔符，请使用SEPARATOR followed by the string literal value that should be inserted between group values. SEPARATOR，后跟应在组值之间插入的字符串文字值。To eliminate the separator altogether, specify 若要完全消除分隔符，请指定SEPARATOR ''.SEPARATOR ''。
The result is truncated to the maximum length that is given by the 结果将被截断为group_concat_max_len system variable, which has a default value of 1024. group_concat_max_len系统变量给定的最大长度，该系统变量的默认值为1024。The value can be set higher, although the effective maximum length of the return value is constrained by the value of 虽然返回值的有效最大长度受max_allowed_packet. max_allowed_packet的值约束，但该值可以设置得更高。The syntax to change the value of 在运行时更改组group_concat_max_len at runtime is as follows, where val is an unsigned integer:group_concat_max_len的值的语法如下，其中val是无符号整数：
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 is 结果类型为TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.TEXT或BLOB，除非group_concat_max_len小于或等于512，在这种情况下，结果类型为VARCHAR或VARBINARY。
If 如果从mysql客户机中调用GROUP_CONCAT() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. GROUP_CONCAT()，则二进制字符串结果将使用十六进制表示法显示，具体取决于--binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql-mysql命令行客户端”。
See also 另请参见CONCAT() and CONCAT_WS(): Section 12.8, “String Functions and Operators”.CONCAT()和CONCAT_WS()：第12.8节，“字符串函数和运算符”。
JSON_ARRAYAGG(col_or_expr) [over_clause]
Aggregates a result set as a single 将结果集聚合为单个JSON array whose elements consist of the rows. JSON数组，其元素由行组成。The order of elements in this array is undefined. The function acts on a column or an expression that evaluates to a single value. 此数组中元素的顺序未定义。函数作用于计算结果为单个值的列或表达式。Returns 如果结果不包含任何行或出现错误，则返回NULL if the result contains no rows, or in the event of an error.NULL。
As of MySQL 8.0.14, this function executes as a window function if 从MySQL 8.0.14开始，如果存在over_clause is present. over_clause，该函数将作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
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. 将两个列名或表达式作为参数，第一个用作键，第二个用作值，并返回包含键值对的JSON对象。Returns 如果结果不包含任何行或出现错误，则返回NULL if the result contains no rows, or in the event of an error. NULL。An error occurs if any key name is 如果任何键名为NULL or the number of arguments is not equal to 2.NULL或参数数不等于2，则会发生错误。
As of MySQL 8.0.14, this function executes as a window function if 从MySQL 8.0.14开始，如果存在over_clause is present. over_clause，该函数将作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
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 MySQL 为了与不允许重复键的MySQL JSON 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”). JSON数据类型规范保持一致，只有遇到的最后一个值与返回对象中的该键一起使用（“最后的重复键赢”）。This means that the result of using this function on columns from a 这意味着，对SELECT can depend on the order in which the rows are returned, which is not guaranteed.SELECT中的列使用此函数的结果可能取决于返回行的顺序，这是不能保证的。
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 the 如果ORDER BY specification guarantees that the values have a specific order. ORDER BY规范保证值具有特定的顺序，那么来自帧中最后一行的键的值是确定的。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: 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)
The key chosen from the last query is nondeterministic. 从上一个查询中选择的键是不确定的。If you prefer a particular key ordering, you can invoke 如果您喜欢特定的键顺序，可以将JSON_OBJECTAGG() as a window function by including an OVER clause with an ORDER BY specification to impose a particular order on frame rows. JSON_OBJECTAGG()作为窗口函数调用，方法是包含OVER子句和ORDER BY规范，以便对帧行施加特定的顺序。The following examples show what happens with and without 下面的示例显示了对于一些不同的框架规范，在有ORDER BY for a few different frame specifications.ORDER BY和没有ORDER BY的情况下会发生什么。
Without 如果没有ORDER BY, the frame is the entire partition:ORDER BY，框架就是整个分区：
mysql>SELECT JSON_OBJECTAGG(c, i)OVER () AS json_object FROM t;+-------------+ | json_object | +-------------+ | {"key": 4} | | {"key": 4} | | {"key": 4} | +-------------+
With 对于ORDER BY, where the frame is the default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (in both ascending and descending order):ORDER BY，其中frame是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} | +-------------+
With 使用ORDER BY and an explicit frame of the entire partition:ORDER BY和整个分区的显式框架：
mysql>SELECT JSON_OBJECTAGG(c, i)OVER (ORDER BY iROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS json_objectFROM t;+-------------+ | json_object | +-------------+ | {"key": 5} | | {"key": 5} | | {"key": 5} | +-------------+
To return a particular key value (such as the smallest or largest), include a 要返回一个特定的键值（如最小值或最大值），请在相应的查询中包含一个LIMIT clause in the appropriate query. LIMIT子句。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} | +-------------+
See Normalization, Merging, and Autowrapping of JSON Values, for additional information and examples.有关更多信息和示例，请参见JSON值的规范化、合并和自动包装。
MAX([DISTINCT] expr) [over_clause]
Returns the maximum value of 返回expr. expr的最大值。MAX() may take a string argument; in such cases, it returns the maximum string value. MAX()可以接受字符串参数；在这种情况下，它返回最大字符串值。See Section 8.3.1, “How MySQL Uses Indexes”. 请参阅第8.3.1节，“MySQL如何使用索引”。The DISTINCT keyword can be used to find the maximum of the distinct values of expr, however, this produces the same result as omitting DISTINCT.DISTINCT关键字可用于查找expr的各不相同值的最大值，但是，这会产生与忽略DISTINCT相同的结果。
If there are no matching rows, 如果没有匹配的行，MAX() returns NULL.MAX()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”; it cannot be used with DISTINCT.over_clause如第12.21.2节，“窗口函数概念和语法”所述；不能与DISTINCT一起使用。
mysql>SELECT student_name, MIN(test_score), MAX(test_score)FROM studentGROUP BY student_name;
For 对于MAX(), MySQL currently compares ENUM and SET columns by their string value rather than by the string's relative position in the set. MAX()，MySQL当前通过字符串值而不是字符串在集合中的相对位置来比较ENUM列和SET列。This differs from how 这与ORDER BY compares them.ORDER BY比较它们的方式不同。
MIN([DISTINCT] expr) [over_clause]
Returns the minimum value of 返回expr. expr的最小值。MIN() may take a string argument; in such cases, it returns the minimum string value. MIN()可以接受字符串参数；在这种情况下，它返回最小字符串值。See Section 8.3.1, “How MySQL Uses Indexes”. 请参阅第8.3.1节，“MySQL如何使用索引”。The DISTINCT keyword can be used to find the minimum of the distinct values of expr, however, this produces the same result as omitting DISTINCT.DISTINCT关键字可用于查找expr的各不相同值的最小值，但是，这会产生与忽略DISTINCT相同的结果。
If there are no matching rows, 如果没有匹配的行，MIN() returns NULL.MIN()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”; it cannot be used with DISTINCT.over_clause如第12.21.2节，“窗口函数概念和语法”所述；不能与DISTINCT一起使用。
mysql>SELECT student_name, MIN(test_score), MAX(test_score)FROM studentGROUP BY student_name;
For 对于MIN(), MySQL currently compares ENUM and SET columns by their string value rather than by the string's relative position in the set. MIN()，MySQL当前通过字符串值而不是字符串在集合中的相对位置来比较ENUM列和SET列。This differs from how 这与ORDER BY compares them.ORDER BY比较它们的方式不同。
Returns the population standard deviation of 返回expr. expr的总体标准偏差。STD() is a synonym for the standard SQL function STDDEV_POP(), provided as a MySQL extension.STD()是标准SQL函数STDDEV_POP()的同义词，作为MySQL扩展提供。
If there are no matching rows, 如果没有匹配的行，STD() returns NULL.STD()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
Returns the population standard deviation of 返回expr. expr的总体标准偏差。STDDEV() is a synonym for the standard SQL function STDDEV_POP(), provided for compatibility with Oracle.STDDEV()是标准SQL函数STDDEV_POP()的同义词，用于与Oracle兼容。
If there are no matching rows, 如果没有匹配的行，STDDEV() returns NULL.STDDEV()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
STDDEV_POP(expr) [over_clause]
Returns the population standard deviation of 返回expr (the square root of VAR_POP()). expr的总体标准偏差（VAR_POP()的平方根）。You can also use 您还可以使用STD() or STDDEV(), which are equivalent but not standard SQL.STD()或STDDEV()，它们是等效的，但不是标准的SQL。
If there are no matching rows, 如果没有匹配的行，STDDEV_POP() returns NULL.STDDEV_POP()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
STDDEV_SAMP(expr) [over_clause]
Returns the sample standard deviation of 返回expr (the square root of VAR_SAMP().expr的样本标准偏差（VAR_SAMP()的平方根）。
If there are no matching rows, 如果没有匹配的行，STDDEV_SAMP() returns NULL.STDDEV_SAMP()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
SUM([DISTINCT] expr) [over_clause]
Returns the sum of 返回expr. expr的和。If the return set has no rows, 如果返回集没有行，SUM() returns NULL. SUM()将返回NULL。The DISTINCT keyword can be used to sum only the distinct values of expr.DISTINCT关键字只能用于对expr的各不相同值求和。
If there are no matching rows, 如果没有匹配的行，SUM() returns NULL.SUM()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clause，则此函数作为窗口函数执行。over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”; it cannot be used with DISTINCT.over_clause如第12.21.2节，“窗口函数概念和语法”所述；不能与DISTINCT一起使用。
Returns the population standard variance of 返回expr. expr的总体标准方差。It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. 它将行视为整个总体，而不是样本，因此它以行数作为分母。You can also use 您还可以使用VARIANCE(), which is equivalent but is not standard SQL.VARIANCE()，它是等效的，但不是标准的SQL。
If there are no matching rows, 如果没有匹配的行，VAR_POP() returns NULL.VAR_POP()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
Returns the sample variance of 返回expr. expr的样本方差。That is, the denominator is the number of rows minus one.也就是说，分母是行数减一。
If there are no matching rows, 如果没有匹配的行，VAR_SAMP() returns NULL.VAR_SAMP()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。
Returns the population standard variance of 返回expr. expr的总体标准方差。VARIANCE() is a synonym for the standard SQL function VAR_POP(), provided as a MySQL extension.VARIANCE()是作为MySQL扩展提供的标准SQL函数VAR_POP()的同义词。
If there are no matching rows, 如果没有匹配的行，VARIANCE() returns NULL.VARIANCE()将返回NULL。
This function executes as a window function if 如果存在over_clause is present. over_clauseover_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”.over_clause如第12.21.2节，“窗口函数概念和语法”所述。