12.19.1聚合(GROUP BY)功能描述
本节介绍对值集合进行操作的组(聚合)函数。
表12.25聚合(GROUP BY)函数
名称 | 描述 |
返回参数的平均值 | |
按位返回AND | |
按位返回OR | |
按位返回异或 | |
COUNT() | 返回返回的行数 |
COUNT(DISTINCT) | 返回许多不同值的计数 |
GROUP_CONCAT() | 返回一个连接的字符串 |
JSON_ARRAYAGG() | 将结果集作为单个JSON数组返回 |
JSON_OBJECTAGG() | 将结果集作为单个JSON对象返回 |
MAX() | 返回最大值 |
MIN() | 返回最小值 |
STD() | 返回人口标准差 |
STDDEV() | 返回人口标准差 |
返回人口标准差 | |
STDDEV_SAMP() | 返回样本标准差 |
归还总和 | |
返回人口标准差异 | |
返回样本方差 | |
返回人口标准差异 |
除非另有说明,否则组函数会忽略 NULL值。
如果在包含无GROUP BY子句的语句中使用组函数 ,则它等同于对所有行进行分组。有关更多信息,请参见 第12.19.3节“GROUP BY的MySQL处理”。
对于数字参数,方差和标准差函数返回一个DOUBLE值。的SUM()和 AVG()函数返回一个 DECIMAL为准确值参数(整数或值DECIMAL),以及DOUBLE为近似值参数(值FLOAT或 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节,“位函数和操作符”。
- AVG([DISTINCT] expr)返回的平均值 expr。该 DISTINCT选项可用于返回不同值的平均值 expr。如果没有匹配的行,则 AVG()返回 NULL。
SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;
- BIT_AND(expr)返回AND所有位的按位 expr。计算以64位(BIGINT)精度执行。如果没有匹配的行,则 BIT_AND()返回中性值(所有位设置为1)。
- BIT_OR(expr)返回OR所有位的按位 expr。计算以64位(BIGINT)精度执行。如果没有匹配的行,则 BIT_OR()返回中性值(所有位都设置为0)。
- BIT_XOR(expr)返回XOR所有位的按位expr。计算以64位(BIGINT)精度执行。如果没有匹配的行,则 BIT_XOR()返回中性值(所有位都设置为0)。
- COUNT(expr)返回 语句检索的行中非NULL 值数的计数。结果是一个 值。 exprSELECTBIGINT如果没有匹配的行,则 COUNT()返回 0。
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表,COUNT(*)如果SELECT从一个表检索,没有检索到其他列,并且没有 WHERE子句, 则优化为非常快速地返回 。例如:
SELECT COUNT(*) FROM student;
此优化仅适用于MyISAM 表,因为为此存储引擎存储了精确的行数,并且可以非常快速地访问。 COUNT(1)如果第一列定义为,则仅受相同优化的影响NOT NULL。
- COUNT(DISTINCT expr,[expr...])返回具有不同非NULLexpr如果没有匹配的行,则 COUNT(DISTINCT)返回 0。
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 ...]]
[SEPARATOR str_val])
SELECT student_name, GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
要么:
SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;
在MySQL中,您可以获得表达式组合的连接值。要消除重复值,请使用该 DISTINCT子句。要对结果中的值进行排序,请使用该ORDER BY子句。要按相反顺序排序,请将DESC (descending)关键字添加到要在ORDER BY子句中排序的列的名称中。默认为升序; 这可以使用ASC关键字明确指定。组中值之间的默认分隔符是逗号(,)。要明确指定分隔符,请使用SEPARATOR后跟应在组值之间插入的字符串文字值。要完全消除分隔符,请指定 SEPARATOR ''。
结果被截断为group_concat_max_len 系统变量给出的最大长度,其默认值为1024.尽管返回值的有效最大长度受值的约束,但该值可以设置得更高max_allowed_packet。group_concat_max_len在运行时更改值的语法 如下,其中val
SET [GLOBAL | SESSION] group_concat_max_len = val;
返回值是非二进制或二进制字符串,具体取决于参数是非二进制还是二进制字符串。结果类型是TEXT或者 BLOB除非 group_concat_max_len小于或等于512,在这种情况下结果类型是 VARCHAR或 VARBINARY。
另见CONCAT()和 CONCAT_WS(): 第12.5节“字符串函数”。
- 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, name) |
+------+----------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+----------------------------------------+
1 row in set (0.00 sec)
在MySQL 5.7.22中添加。
- MAX([DISTINCT] expr)返回的最大值 expr。 MAX()可以采用字符串参数; 在这种情况下,它返回最大字符串值。请参见第8.3.1节“MySQL如何使用索引”。的 DISTINCT关键字可用于以找到最大的不同值中的 expr,然而,这产生相同的结果作为遗漏DISTINCT。如果没有匹配的行,则 MAX()返回 NULL。
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。 MIN()可以采用字符串参数; 在这种情况下,它返回最小字符串值。请参见第8.3.1节“MySQL如何使用索引”。的 DISTINCT关键字可用来找到最小的不同值中的 expr,然而,这产生相同的结果作为遗漏DISTINCT。如果没有匹配的行,则 MIN()返回 NULL。
SELECT student_name, MIN(test_score), MAX(test_score)
FROM student
GROUP BY student_name;
因为MIN(),MySQL当前按字符串值比较ENUM和 SET列,而不是字符串在集合中的相对位置。这与ORDER BY 比较它们的方式不同。
- STD(expr)返回总体的人口标准差 expr。 STD()是标准SQL函数的同义词 STDDEV_POP(),作为MySQL扩展提供。如果没有匹配的行,则 STD()返回 NULL。
- STDDEV(expr)返回总体的人口标准差 expr。 STDDEV()是STDDEV_POP()与Oracle兼容的标准SQL函数的同义词 。如果没有匹配的行,则 STDDEV()返回 NULL。
- STDDEV_POP(expr)返回expr(平方根 VAR_POP())的总体标准差 。您也可以使用 STD()或 STDDEV(),它们是等效的但不是标准的SQL。如果没有匹配的行,则 STDDEV_POP()返回 NULL。
- STDDEV_SAMP(expr)返回样本标准差 expr(的平方根) VAR_SAMP()。如果没有匹配的行,则 STDDEV_SAMP()返回 NULL。
- SUM([DISTINCT] expr)返回的总和expr。如果返回集没有行,则SUM() 返回NULL。的 DISTINCT关键字可用来仅求和的不同的值expr。如果没有匹配的行,则 SUM()返回 NULL。
- VAR_POP(expr)返回人口标准方差 expr。它将行视为整个总体,而不是作为样本,因此它将行数作为分母。您也可以使用 VARIANCE(),这是等效的但不是标准SQL。如果没有匹配的行,则 VAR_POP()返回 NULL。
- VAR_SAMP(expr)返回样本方差 expr。也就是说,分母是行数减1。如果没有匹配的行,则 VAR_SAMP()返回 NULL。
- VARIANCE(expr)返回人口标准方差 expr。 VARIANCE()是标准SQL函数的同义词 VAR_POP(),作为MySQL扩展提供。如果没有匹配的行,则 VARIANCE()返回 NULL。