12.20.1 汇总功能说明
本节介绍对值集进行操作的聚合函数。它们通常与GROUP BY子句一起使用以将值分组为子集。
table12.26 汇总函数
| Name | Description |
|---|---|
| AVG() | 返回参数的平均值 |
| BIT_AND() | 按位返回 AND |
| BIT_OR() | 按位返回 OR |
| BIT_XOR() | 返回按位异或 |
| COUNT() | 返回计数返回的行数 |
| COUNT(DISTINCT) | 返回多个不同值的计数 |
| GROUP_CONCAT() | 返回串联的字符串 |
| JSON_ARRAYAGG()(引入 5.7.22) | 将结果集作为单个 JSON 数组返回 |
| JSON_OBJECTAGG()(引入 5.7.22) | 将结果集作为单个 JSON 对象返回 |
| MAX() | 返回最大值 |
| MIN() | 返回最小值 |
| STD() | 返回人口标准差 |
| STDDEV() | 返回人口标准差 |
| STDDEV_POP() | 返回人口标准差 |
| STDDEV_SAMP() | 返回 sample 标准偏差 |
| SUM() | 返回总和 |
| VAR_POP() | 返回总体标准方差 |
| VAR_SAMP() | 返回 samples 方差 |
| VARIANCE() | 返回总体标准方差 |
除非另有说明，否则聚合函数将忽略NULL值。
如果在不包含GROUP BY子句的语句中使用聚合函数，则等效于对所有行进行分组。有关更多信息，请参见第 12.20.3 节“ MySQL BY GROUP BY 的处理”。
对于数字参数，方差和标准偏差函数返回DOUBLE值。 SUM()和AVG()函数为精确值参数(整数或DECIMAL)返回DECIMAL值，为近似值参数(FLOAT或DOUBLE)返回DOUBLE值。
SUM()和AVG()聚合函数不适用于时间值。 (它们将值转换为数字，第一个非数字字符后会丢失所有内容.)要解决此问题，请转换为数字单位，执行合计运算，然后转换回时间值。例子：
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
诸如SUM()或AVG()之类的期望数字参数的函数在必要时将其强制转换为数字。对于SET或ENUM值，强制转换操作导致使用基础数字值。
BIT_AND()，BIT_OR()和BIT_XOR()聚合函数执行位操作。它们需要BIGINT(64 位整数)参数，并返回BIGINT值。其他类型的参数将转换为BIGINT并可能发生截断。有关 MySQL 8.0 中允许位操作采用二进制字符串类型参数(BINARY，VARBINARY和BLOB类型)的更改的信息，请参见第 12.12 节“位函数和运算符”。
返回expr的平均值。 DISTINCT选项可用于返回* expr *的不同值的平均值。
如果没有匹配的行，则AVG()返回NULL。
mysql> SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;
返回* expr *中所有位的按位AND。以 64 位(BIGINT)精度执行计算。
如果没有匹配的行，则BIT_AND()返回中性值(所有位均设置为 1)。
返回* expr *中所有位的按位OR。以 64 位(BIGINT)精度执行计算。
如果没有匹配的行，则BIT_OR()返回中性值(所有位均设置为 0)。
返回* expr *中所有位的按位XOR。以 64 位(BIGINT)精度执行计算。
如果没有匹配的行，则BIT_XOR()返回中性值(所有位均设置为 0)。
返回由SELECT语句检索的行中非NULL值* expr *的数量的计数。结果是BIGINT值。
如果没有匹配的行，则COUNT()返回0。
mysql> SELECT student.student_name,COUNT(*)
FROM student,course
WHERE student.student_id=course.student_id
GROUP BY student_name;
COUNT(*)有所不同，它返回所检索的行数的计数，无论它们是否包含NULL值。
对于诸如InnoDB之类的事务存储引擎，存储准确的行数是有问题的。可能同时发生多个事务，每个事务都可能影响计数。
InnoDB不保留 table 中行的内部计数，因为并发事务可能同时“看到”不同数量的行。因此，SELECT COUNT(*)语句仅计算当前事务可见的行。
在 MySQL 5.7.18 之前，InnoDB通过扫描聚集索引来处理SELECT COUNT(*)语句。从 MySQL 5.7.18 开始，InnoDB通过遍历最小的可用二级索引来处理SELECT COUNT(*)语句，除非索引或优化器提示指示优化器使用其他索引。如果不存在二级索引，则将扫描聚集索引。
如果索引记录不完全在缓冲池中，则处理SELECT COUNT(*)语句会花费一些时间。为了获得更快的计数，请创建一个计数器 table，然后让您的应用程序根据其插入和删除操作对其进行更新。但是，在成千上万的并发事务正在启动对同一计数器 table 的更新的情况下，此方法可能无法很好地扩展。如果大概的行数足够，请使用显示 table 格状态。
InnoDB以相同方式处理SELECT COUNT(*)和SELECT COUNT(1)操作。没有性能差异。
对于MyISAMtable，如果SELECT从一个 table 中检索，没有其他列被检索，并且没有WHERE子句，则COUNT(*)可以快速返回。例如：
mysql> SELECT COUNT(*) FROM student;
此优化仅适用于MyISAM个 table，因为为此存储引擎存储了准确的行数，并且可以非常快速地对其进行访问。如果第一列定义为NOT NULL，则COUNT(1)只能进行相同的优化。
COUNT(DISTINCT expr,[expr...])
返回具有不同非NULL * expr *值的行数的计数。
如果没有匹配的行，则COUNT(DISTINCT)返回0。
mysql> SELECT COUNT(DISTINCT results) FROM student;
在 MySQL 中，您可以通过提供 table 达式列 table 来获得不包含NULL的不同 table 达式组合的数量。在标准 SQL 中，您必须对COUNT(DISTINCT ...)内的所有 table 达式进行串联。
该函数返回一个字符串结果，该字符串结果具有来自组的串联的非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;
Or:
mysql> SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;
在 MySQL 中，您可以获取 table 达式组合的串联值。要消除重复的值，请使用DISTINCT子句。要对结果中的值进行排序，请使用ORDER BY子句。要以相反的 Sequences 排序，请在ORDER BY子句中将DESC(降序)关键字添加到要作为排序依据的列的名称。默认为升序；可以使用ASC关键字明确指定。组中值之间的默认分隔符是逗号(,)。要明确指定分隔符，请使用SEPARATOR，后跟应在组值之间插入的字符串 Literals 值。要完全消除分隔符，请指定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，否则结果类型为TEXT或BLOB，在这种情况下，结果类型为VARCHAR或VARBINARY。
另请参见CONCAT()和CONCAT_WS()：第 12.7 节“字符串函数和运算符”。
将结果集聚合为单个JSON数组，其元素由行组成。此数组中元素的 Sequences 未定义。该函数作用于计算为单个值的列或 table 达式。如果结果不包含任何行，或者发生错误，则返回NULL。
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)
在 MySQL 5.7.22 中添加。
将两个列名称或 table 达式用作参数，其中第一个用作键，第二个用作值，并返回包含键值对的 JSON 对象。如果结果不包含任何行，或者发生错误，则返回NULL。如果任何键名称为NULL或参数个数不等于 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)
重复键处理. 当对该函数的结果进行标准化时，具有重复键的值将被丢弃。与不允许重复键的 MySQL JSON数据类型规范保持一致，在返回的对象中，只有遇到的最后一个值与该键一起使用(“最后重复的键获胜”)。这意味着在SELECT的列上使用此函数的结果可能取决于行返回的 Sequences，但不能保证。
考虑以下：
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)
有关其他信息和示例，请参见JSON 值的规范化，合并和自动包装。
在 MySQL 5.7.22 中添加。
返回* expr 的最大值。 MAX()可以使用字符串参数；在这种情况下，它将返回最大字符串值。参见第 8.3.1 节“ MySQL 如何使用索引”。关键字DISTINCT可用于查找 expr *的最大不同值，但是，这产生与省略DISTINCT相同的结果。
如果没有匹配的行，则MAX()返回NULL。
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
FROM student
GROUP BY student_name;
对于MAX()，MySQL 当前通过它们的字符串值而不是字符串在集合中的相对位置来比较ENUM和SET列。这与ORDER BY比较它们的方式不同。
返回* expr 的最小值。 MIN()可以使用字符串参数；在这种情况下，它将返回最小字符串值。参见第 8.3.1 节“ MySQL 如何使用索引”。关键字DISTINCT可用于查找 expr *的不同值中的最小值，但是，这产生与省略DISTINCT相同的结果。
如果没有匹配的行，则MIN()返回NULL。
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
FROM student
GROUP BY student_name;
对于MIN()，MySQL 当前通过它们的字符串值而不是字符串在集合中的相对位置来比较ENUM和SET列。这与ORDER BY比较它们的方式不同。
返回* expr *的总体标准偏差。 STD()是标准 SQL 函数STDDEV_POP()的同义词，作为 MySQL 扩展提供。
如果没有匹配的行，则STD()返回NULL。
返回* expr *的总体标准偏差。 STDDEV()是标准 SQL 函数STDDEV_POP()的同义词，为与 Oracle 兼容而提供。
如果没有匹配的行，则STDDEV()返回NULL。
返回* expr *(VAR_POP()的平方根)的总体标准偏差。您还可以使用STD()或STDDEV()，它们等效但不是标准 SQL。
如果没有匹配的行，则STDDEV_POP()返回NULL。
返回* expr *(VAR_SAMP()的平方根)的 samples 标准偏差。
如果没有匹配的行，则STDDEV_SAMP()返回NULL。
返回* expr 的总和。如果返回集没有行，则SUM()返回NULL。关键字DISTINCT仅可用于对 expr *的不同值求和。
如果没有匹配的行，则SUM()返回NULL。
返回* expr *的总体标准方差。它把行视为整体，而不是 samples，因此它以行数作为分母。您也可以使用VARIANCE()，它等效但不是标准 SQL。
如果没有匹配的行，则VAR_POP()返回NULL。
返回* expr *的 samples 方差。也就是说，分母是行数减一。
如果没有匹配的行，则VAR_SAMP()返回NULL。
返回* expr *的总体标准方差。 VARIANCE()是标准 SQL 函数VAR_POP()的同义词，作为 MySQL 扩展提供。
如果没有匹配的行，则VARIANCE()返回NULL。
