聚合功能描述(GROUP BY)
本节介绍对值集进行操作的组(汇总)函数。
表汇总(GROUP BY)函数
名称 | 描述 |
---|---|
AVG() | 返回参数的平均值 |
BIT_AND() | 按位返回AND |
BIT_OR() | 按位返回或 |
BIT_XOR() | 返回按位异或 |
COUNT() | 返回计数返回的行数 |
COUNT(DISTINCT) | 返回多个不同值的计数 |
GROUP_CONCAT() | 返回串联的字符串 |
JSON_ARRAYAGG() | 将结果集作为单个JSON数组返回 |
JSON_OBJECTAGG() | 将结果集作为单个JSON对象返回 |
MAX() | 返回最大值 |
MIN() | 返回最小值 |
STD() | 返回人口标准差 |
STDDEV() | 返回人口标准差 |
STDDEV_POP() | 返回人口标准差 |
STDDEV_SAMP() | 返回样品标准偏差 |
SUM() | 返回总和 |
VAR_POP() | 返回总体标准方差 |
VAR_SAMP() | 返回样本方差 |
VARIANCE() | 返回总体标准方差 |
除非另有说明,否则组函数将忽略NULL
值。
如果在不包含任何GROUP BY
子句的语句中使用组函数,则等效于对所有行进行分组。有关更多信息,请参见“ GROUP BY的MySQL处理”。
大多数聚合函数都可以用作窗口函数。可以以这种方式使用的语法在其语法描述中由表示,表示可选子句。“窗口函数的概念和语法”中对此进行了描述,该文档还包括有关窗口函数用法的其他信息。[over_clause]
OVER
over_clause
对于数字参数,方差和标准偏差函数返回一个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()
聚合函数执行位操作。在MySQL 8.0之前,位函数和运算符需要参数BIGINT
(64位整数)和返回BIGINT
值,因此它们的最大范围为64位。在执行该操作之前,将非BIGINT
参数转换为BIGINT
,并且可能会发生截断。
在MySQL 8.0中,位函数和运算符允许使用二进制字符串类型的参数(BINARY
,VARBINARY
和BLOB
类型)并返回类似类型的值,这使它们能够采用参数并产生大于64位的返回值。有关位运算的参数求值和结果类型的讨论,请参见“位函数和运算符”中的介绍性讨论。
AVG([DISTINCT]expr)[over_clause]
返回的平均值
expr
。该DISTINCT
选项可用于返回的不同值的平均值expr
。如果没有匹配的行,则
AVG()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述;不能与一起使用DISTINCT
。mysql>
SELECT student_name, AVG(test_score)FROM studentGROUP BY student_name;BIT_AND(expr)[over_clause]
返回
AND
中所有位的按位排序expr
。结果类型取决于函数参数值是二进制字符串还是数字:
- 当参数值具有二进制字符串类型并且参数不是十六进制文字,位文字或
NULL
文字时,将进行二进制字符串求值。否则会进行数值计算,并根据需要将参数值转换为无符号的64位整数。 - 二进制字符串求值将生成长度与参数值相同的二进制字符串。如果参数值的长度不相等,
ER_INVALID_BITWISE_OPERANDS_SIZE
则会发生错误。如果参数大小超过511个字节,ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE
则会发生错误。数值评估产生一个无符号的64位整数。
如果没有匹配的行,则
BIT_AND()
返回长度与参数值相同的中性值(所有位均设置为1)。NULL
除非所有值均为,否则这些值不会影响结果NULL
。在那种情况下,结果是一个中性值,其长度与自变量值相同。有关参数评估和结果类型的更多信息,请参见“位函数和运算符”中的介绍性讨论。
从MySQL 8.0.12开始,此函数作为窗口函数(如果
over_clause
存在)执行。over_clause
如“窗口函数的概念和语法”中所述。- 当参数值具有二进制字符串类型并且参数不是十六进制文字,位文字或
BIT_OR(expr)[over_clause]
返回
OR
中所有位的按位排序expr
。结果类型取决于函数参数值是二进制字符串还是数字:
- 当参数值具有二进制字符串类型并且参数不是十六进制文字,位文字或
NULL
文字时,将进行二进制字符串求值。否则会进行数值计算,并根据需要将参数值转换为无符号的64位整数。 - 二进制字符串求值将生成长度与参数值相同的二进制字符串。如果参数值的长度不相等,
ER_INVALID_BITWISE_OPERANDS_SIZE
则会发生错误。如果参数大小超过511个字节,ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE
则会发生错误。数值评估产生一个无符号的64位整数。
如果没有匹配的行,则
BIT_OR()
返回长度与参数值相同的中性值(所有位均设置为0)。NULL
除非所有值均为,否则这些值不会影响结果NULL
。在那种情况下,结果是一个中性值,其长度与自变量值相同。有关参数评估和结果类型的更多信息,请参见“位函数和运算符”中的介绍性讨论。
从MySQL 8.0.12开始,此函数作为窗口函数(如果
over_clause
存在)执行。over_clause
如“窗口函数的概念和语法”中所述。- 当参数值具有二进制字符串类型并且参数不是十六进制文字,位文字或
BIT_XOR(expr)[over_clause]
返回
XOR
中所有位的按位排序expr
。结果类型取决于函数参数值是二进制字符串还是数字:
- 当参数值具有二进制字符串类型并且参数不是十六进制文字,位文字或
NULL
文字时,将进行二进制字符串求值。否则会进行数值计算,并根据需要将参数值转换为无符号的64位整数。 - 二进制字符串求值将生成长度与参数值相同的二进制字符串。如果参数值的长度不相等,
ER_INVALID_BITWISE_OPERANDS_SIZE
则会发生错误。如果参数大小超过511个字节,ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE
则会发生错误。数值评估产生一个无符号的64位整数。
如果没有匹配的行,则
BIT_XOR()
返回长度与参数值相同的中性值(所有位均设置为0)。NULL
除非所有值均为,否则这些值不会影响结果NULL
。在那种情况下,结果是一个中性值,其长度与自变量值相同。有关参数评估和结果类型的更多信息,请参见“位函数和运算符”中的介绍性讨论。
从MySQL 8.0.12开始,此函数作为窗口函数(如果
over_clause
存在)执行。over_clause
如“窗口函数的概念和语法”中所述。- 当参数值具有二进制字符串类型并且参数不是十六进制文字,位文字或
COUNT(expr)[over_clause]
返回由语句检索的行中非
NULL
值的数量的计数。结果是一个值。expr
SELECT
BIGINT
如果没有匹配的行,则
COUNT()
返回0
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。mysql>
SELECT student.student_name,COUNT(*)FROM student,courseWHERE student.student_id=course.student_idGROUP BY student_name;COUNT(*)
有点不同,因为它返回获取的行数的计数,无论它们是否包含NULL
值。对于诸如的事务性存储引擎
InnoDB
,存储准确的行数是有问题的。可能同时发生多个事务,每个事务都可能影响计数。InnoDB
不会在表中保留内部行数,因为并发事务可能同时“看到”不同数量的行。因此,SELECT COUNT(*)
语句仅对当前事务可见的行进行计数。从MySQL 8.0.13开始,如果没有诸如或的额外子句,则针对单线程工作负载优化了表的查询性能。
SELECT COUNT(*)FROM tbl_name
InnoDB
WHERE
GROUP BY
InnoDB
SELECT COUNT(*)
除非索引或优化器提示指示优化器使用其他索引,否则通过遍历最小的可用二级索引来处理语句。如果不存在二级索引,则通过扫描聚集索引来InnoDB
处理SELECT COUNT(*)
语句。SELECT COUNT(*)
如果索引记录不完全在缓冲池中,则处理语句将花费一些时间。为了获得更快的计数,请创建一个计数器表,并让您的应用程序根据其插入和删除操作对其进行更新。但是,在成千上万的并发事务正在启动对同一计数器表的更新的情况下,此方法可能无法很好地扩展。如果大概的行数足够,请使用SHOW TABLE STATUS
。InnoDB
以相同的方式处理SELECT COUNT(*)
和SELECT COUNT(1)
操作。没有性能差异。对于
MyISAM
表,COUNT(*)
如果SELECT
从一个表中进行检索,没有其他列被检索,并且没有WHERE
子句,则优化后可以非常快速地返回。例如:mysql>
SELECT COUNT(*)FROM student;此优化仅适用于
MyISAM
表,因为为该存储引擎存储了准确的行数,并且可以非常快速地对其进行访问。COUNT(1)
如果第一列定义为,则仅进行相同的优化NOT NULL
。COUNT(DISTINCT expr,[expr...])
返回具有不同非
NULL
expr
值的行数的计数。如果没有匹配的行,则
COUNT(DISTINCT)
返回0
。mysql>
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])mysql>
SELECT student_name, GROUP_CONCAT(test_score)FROM studentGROUP BY student_name;要么:
mysql>
SELECT student_name, GROUP_CONCAT(DISTINCT test_scoreORDER BY test_scoreDESC SEPARATOR ' ')FROM studentGROUP BY student_name;在MySQL中,您可以获取表达式组合的串联值。要消除重复值,请使用该
DISTINCT
子句。要对结果中的值进行排序,请使用ORDER BY
子句。要以相反的顺序DESC
排序,请在ORDER BY
子句中将(降序)关键字添加到要作为排序依据的列的名称。默认为升序;这可以使用ASC
关键字明确指定。组中值之间的默认分隔符是逗号(,
)。要明确指定分隔符,请使用SEPARATOR
后跟应该在组值之间插入的字符串文字值。要完全消除分隔符,请指定SEPARATOR ''
。结果将被截断为
group_concat_max_len
系统变量给定的最大长度,该默认值的默认值为1024。尽管返回值的有效最大长度受的限制,但可以将值设置得更高max_allowed_packet
。group_concat_max_len
在运行时更改value的语法如下,其中val
是一个无符号整数:SET [GLOBAL |SESSION ] group_concat_max_len = val;返回值是非二进制或二进制字符串,具体取决于参数是非二进制还是二进制字符串。结果类型为
TEXT
或,BLOB
除非group_concat_max_len
小于或等于512,在这种情况下,结果类型为VARCHAR
或VARBINARY
。另请参见
CONCAT()
和CONCAT_WS()
:“字符串函数和运算符”。JSON_ARRAYAGG(col_or_expr)[over_clause]
将结果集聚合为单个
JSON
数组,其元素由行组成。此数组中元素的顺序未定义。该函数作用于计算为单个值的列或表达式。返回NULL
如果结果不包含任何行,或在错误的事件。从MySQL 8.0.14开始,此函数作为窗口函数(如果
over_clause
存在)执行。over_clause
如“窗口函数的概念和语法”中所述。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 attributesFROM t3GROUP 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]
将两个列名或表达式作为参数,其中第一个用作键,第二个用作值,并返回包含键值对的JSON对象。返回
NULL
如果结果不包含任何行,或在错误的事件。如果任何键名为NULL
或参数数目不等于2,则会发生错误。从MySQL 8.0.14开始,此函数作为窗口函数(如果
over_clause
存在)执行。over_clause
如“窗口函数的概念和语法”中所述。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 t3GROUP 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)重复密钥处理
当此功能的结果标准化后,具有重复键的值将被丢弃。与
JSON
不允许重复键的MySQL 数据类型规范保持一致,在返回的对象中,只有遇到的最后一个值与该键一起使用(“最后重复键获胜”)。这意味着在从SELECT
取决于行中返回的顺序,这不能保证。当用作窗口函数时,如果一帧中有重复的键,则结果中仅显示键的最后一个值。如果ORDER BY
规范保证这些值具有特定顺序,则帧中最后一行的键的值是确定性的。如果不是,则键的结果值不确定。考虑以下:mysql>
CREATE TABLE t(c VARCHAR(10), i INT); Query OK, 0 rows affected (0.03 sec) mysql>INSERT INTO tVALUES ('key', 3), ('key', 4), ('key', 5); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>SELECT c, iFROM 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.00 sec) mysql>INSERT INTO tVALUES ('key', 3), ('key', 5), ('key', 4); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>SELECT c, iFROM 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_OBJECTAGG()
通过在OVER
子句中添加一个带有ORDER BY
规范的子句来将特定的键强加到帧行上,从而将其作为窗口函数来调用。以下示例显示了ORDER BY
在几种不同的框架规格下有无情况下会发生什么。如果没有
ORDER BY
,则框架是整个分区:mysql>
SELECT JSON_OBJECTAGG(c, i)OVER ()AS json_objectFROM t; +------------- + | json_object | +------------- + | {"key": 4} | | {"key": 4} | | {"key": 4} | +------------- +使用
ORDER BY
,其中框架是默认框架RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
(按升序和降序排列):mysql>
SELECT JSON_OBJECTAGG(c, i)OVER (ORDER BY i)AS json_objectFROM t; +------------- + | json_object | +------------- + | {"key": 3} | | {"key": 4} | | {"key": 5} | +------------- + mysql>SELECT JSON_OBJECTAGG(c, i)OVER (ORDER BY iDESC )AS json_objectFROM t; +------------- + | json_object | +------------- + | {"key": 5} | | {"key": 4} | | {"key": 3} | +------------- +使用
ORDER BY
和整个分区的显式框架:mysql>
SELECT JSON_OBJECTAGG(c, i)OVER (ORDER BY iROWS BETWEENUNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING )AS json_objectFROM t; +------------- + | json_object | +------------- + | {"key": 5} | | {"key": 5} | | {"key": 5} | +------------- +要返回特定的键值(例如最小或最大),请
LIMIT
在适当的查询中包括一个子句。例如:mysql>
SELECT JSON_OBJECTAGG(c, i)OVER (ORDER BY i)AS json_objectFROM tLIMIT 1; +------------- + | json_object | +------------- + | {"key": 3} | +------------- + mysql>SELECT JSON_OBJECTAGG(c, i)OVER (ORDER BY iDESC )AS json_objectFROM tLIMIT 1; +------------- + | json_object | +------------- + | {"key": 5} | +------------- +有关其他信息和示例,请参见JSON值的规范化,合并和自动包装。
MAX([DISTINCT]expr)[over_clause]
返回的最大值
expr
。MAX()
可以采用字符串参数;在这种情况下,它将返回最大字符串值。请参见“ MySQL如何使用索引”。的DISTINCT
关键字可用于以找到最大的不同值中的expr
,然而,这会产生相同的结果省略DISTINCT
。如果没有匹配的行,则
MAX()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述;不能与一起使用DISTINCT
。mysql>
SELECT student_name, MIN(test_score), MAX(test_score)FROM studentGROUP BY student_name;对于
MAX()
,MySQL当前根据字符串值而不是字符串在集合中的相对位置来比较ENUM
和SET
列。这与ORDER BY
比较它们的方式不同。MIN([DISTINCT]expr)[over_clause]
返回的最小值
expr
。MIN()
可以采用字符串参数;在这种情况下,它将返回最小字符串值。请参见“ MySQL如何使用索引”。的DISTINCT
关键字可用来找到最小的不同值中的expr
,然而,这会产生相同的结果省略DISTINCT
。如果没有匹配的行,则
MIN()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述;不能与一起使用DISTINCT
。mysql>
SELECT student_name, MIN(test_score), MAX(test_score)FROM studentGROUP BY student_name;对于
MIN()
,MySQL当前根据字符串值而不是字符串在集合中的相对位置来比较ENUM
和SET
列。这与ORDER BY
比较它们的方式不同。STD(expr)[over_clause]
返回的总体标准差
expr
。STD()
是标准SQL函数的同义词STDDEV_POP()
,作为MySQL扩展提供。如果没有匹配的行,则
STD()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。STDDEV(expr)[over_clause]
返回的总体标准差
expr
。STDDEV()
是标准SQL函数的同义词STDDEV_POP()
,为与Oracle兼容而提供。如果没有匹配的行,则
STDDEV()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。STDDEV_POP(expr)[over_clause]
返回
expr
(的平方根VAR_POP()
)的总体标准偏差。您也可以使用STD()
或STDDEV()
,它们等效但不是标准SQL。如果没有匹配的行,则
STDDEV_POP()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。STDDEV_SAMP(expr)[over_clause]
返回
expr
(的平方根的样本标准偏差VAR_SAMP()
。如果没有匹配的行,则
STDDEV_SAMP()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。SUM([DISTINCT]expr)[over_clause]
返回的总和
expr
。如果返回集没有行,则SUM()
返回NULL
。的DISTINCT
关键字可用来仅求和的不同的值expr
。如果没有匹配的行,则
SUM()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述;不能与一起使用DISTINCT
。VAR_POP(expr)[over_clause]
返回的总体标准方差
expr
。它把行视为整体,而不是样本,因此它以行数作为分母。您也可以使用VARIANCE()
,它等效但不是标准SQL。如果没有匹配的行,则
VAR_POP()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。VAR_SAMP(expr)[over_clause]
返回的样本方差
expr
。也就是说,分母是行数减一。如果没有匹配的行,则
VAR_SAMP()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。VARIANCE(expr)[over_clause]
返回的总体标准方差
expr
。VARIANCE()
是标准SQL函数的同义词VAR_POP()
,作为MySQL扩展提供。如果没有匹配的行,则
VARIANCE()
返回NULL
。如果
over_clause
存在,此功能将作为窗口功能执行。over_clause
如“窗口函数的概念和语法”中所述。