MySQL 聚合函数

名称	描述	介绍
MAX()	最大值
MIN()	最小值
COUNT()	行数
SUM()	总和
AVG()	返回参数的平均值
GROUP_CONCAT()	拼接字符串
BIT_AND()	按位返回 AND
BIT_OR()	按位或返回
BIT_XOR()	返回按位异或
JSON_ARRAYAGG()	将结果集作为单个 JSON 数组返回	5.7.22
JSON_OBJECTAGG()	将结果集作为单个 JSON 对象返回	5.7.22
STD()	返回总体标准差
STDDEV()	返回总体标准差
STDDEV_POP()	返回总体标准差
STDDEV_SAMP()	返回样本标准差
VAR_POP()	返回总体标准方差
VAR_SAMP()	返回样本方差
VARIANCE()	返回总体标准方差

除非另有说明，聚合函数会忽略 NULL值。

对于数字参数，方差和标准差函数返回一个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.13章节，“位函数和操作符”。

MAX([DISTINCT] expr)

返回 expr 的最大值 。DISTINCT 是可选项。如果没有匹配的行，则返回 NULL。

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

对于MAX()，MySQL 目前比较的是ENUM和 SET 列的字符串值，而不是字符串在集合中的相对位置。这与ORDER BY 比较它们的方式不同。

MIN([DISTINCT] expr)

返回 expr 的最小值 。其他同上。

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

返回表达式 expr 的非 NULL 的行数。返回值是 BIGINT。

如果没有匹配的行，则返回 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不保存表中行的内部计数，因为并发事务可能同时 “看到”不同数量的行。因此，SELECT COUNT(*) 语句只计算对当前事务可见的行。

优化：在 MySQL 5.7.18 之前，通过扫描聚集索引来InnoDB处理 SELECT COUNT(*)语句。从 MySQL 5.7.18 开始， 除非索引或优化器提示指示优化器使用不同的索引，否则通过遍历最小的可用二级索引来InnoDB处理SELECT COUNT(*)语句。如果二级索引不存在，则扫描聚集索引。

如果索引记录不完全在缓冲池中，则处理 SELECT COUNT(*) 语句需要一些时间。为了更快地计数，创建一个计数器表并让您的应用程序根据它所做的插入和删除来更新它。但是，在数千个并发事务启动对同一计数器表的更新的情况下，此方法可能无法很好地扩展。如果近似行数就足够了，请使用 SHOW TABLE STATUS。

InnoDB处理SELECT COUNT(*)和SELECT COUNT(1) 操作方式相同。没有性能差异。

优化：对于MyISAM表，执行 SELECT COUNT(*) FROM t 非常快，因为存储引擎存储了精确的行数，并且可以非常快速地访问。只有当第一列定义为 NOT NULL 时，COUNT(1) 才会进行相同的优化。

SUM([DISTINCT] expr)

求和。DISTINCT是可选的。如果没有匹配的行，则返回 NULL。

AVG([DISTINCT] expr)

返回 expr 的平均值 。DISTINCT是可选的。

如果没有匹配的行，则返回 NULL。

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

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：要消除重复值，请使用 DISTINCT子句。
• ORDER BY：要对结果中的值进行排序，请使用该ORDER BY子句。要以相反的顺序排序，添加DESC 。默认为升序。
• SEPARATOR：组中值之间的默认分隔符是逗号 ,。要明确指定分隔符，请使用SEPARATOR。要完全消除分隔符，请指定 SEPARATOR ''。

结果被截断为系统变量 group_concat_max_len 给定的最大长度，其默认值为 1024。尽管返回值的有效最大长度受 max_allowed_packet 的值约束，但该值可以设置得更大。在运行时更改值的语法如下，其中*val* 是无符号整数：

SET [GLOBAL | SESSION] group_concat_max_len = val;

返回值是非二进制或二进制字符串，具体取决于参数是非二进制字符串还是二进制字符串。结果类型是TEXT 或 BLOB。除非 group_concat_max_len小于或等于 512，在这种情况下，结果类型是 VARCHARor VARBINARY。

如果GROUP_CONCAT()从mysql客户端中调用，则二进制字符串结果使用十六进制表示法显示，具体取决于 --binary-as-hex. 有关该选项的更多信息，请参阅第 4.5.1 节，“mysql — MySQL 命令行客户端”。

另请参阅CONCAT()和 CONCAT_WS()： 第 12.8 节，“字符串函数和运算符”。

BIT_AND(expr)

按位与。计算以 64 位 ( BIGINT) 精度执行。

如果没有匹配的行，则返回一个中性值（所有位都设置为 1）。

BIT_OR(expr)

按位或。计算以 64 位 ( BIGINT) 精度执行。

如果没有匹配的行，则返回一个中性值（所有位都设置为 0）。

示例：计算用户每月访问网页的天数。

CREATE TABLE t1 (year YEAR, month INT UNSIGNED, day INT UNSIGNED);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);

示例表包含表示用户对页面访问次数的年-月-日值。要确定每个月这些访问发生的天数，可以使用这个查询：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+

该查询计算每个年/月组合在表中出现的不同天数，并自动删除重复项。

BIT_XOR(expr)

按位异或。计算以 64 位 ( BIGINT) 精度执行。

如果没有匹配的行，则返回一个中性值（所有位都设置为 0）。

JSON_ARRAYAGG(col_or_expr)

将结果集聚合为单个 JSON 数组，其元素由行组成。此数组中元素的顺序未定义。该函数作用于计算结果为单个值的列或表达式。如果结果不包含任何行，或出现错误，返回 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 中添加。

JSON_OBJECTAGG(key, value)

将两个列名或表达式作为参数，其中第一个用作键，第二个用作值，并返回包含键值对的 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中的列上使用此函数的结果可能取决于返回行的顺序，这是不能保证的。

考虑以下情况：

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 值的规范化、合并和自动包装。

STD(expr)

总体标准差。 STD()是标准 SQL 函数 STDDEV_POP()的同义词，作为 MySQL 扩展提供。

如果没有匹配的行，则 STD()返回 NULL。

STDDEV(expr)

总体标准差。 STDDEV()是标准 SQL 函数 STDDEV_POP() 的同义词 ，用于与 Oracle 兼容。

如果没有匹配的行，则 STDDEV()返回 NULL。

STDDEV_POP(expr)

总体标准差（ VAR_POP()的平方根） 。您也可以使用 STD()or STDDEV()，它们等效但不是标准 SQL。

如果没有匹配的行，则 STDDEV_POP()返回 NULL。

STDDEV_SAMP(expr)

样本标准差（ VAR_SAMP() 的平方根）。

VAR_POP(expr)

总体标准方差。它将行视为整个总体，而不是样本，因此将行数作为分母。您也可以使用 VARIANCE()，它等效但不是标准 SQL。

如果没有匹配的行，则 VAR_POP()返回 NULL。

VAR_SAMP(expr)

样本方差。也就是说，分母是行数减一。

如果没有匹配的行，则 VAR_SAMP()返回 NULL。

VARIANCE(expr)

总体标准方差。VARIANCE()是标准 SQL 函数的同义词 VAR_POP()，作为 MySQL 扩展提供。

如果没有匹配的行，则 VARIANCE()返回 NULL。

参考

• 12.20.1 Aggregate Function Descriptions
• 3.6.8 Calculating Visits Per Day