• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
    •
    位置： MySQL 8 中文手册 -> MySQL 函数和运算符 -> 聚合函数（GROUP BY）

    聚合功能描述（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]OVERover_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 student
       GROUP 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值的数量的计数。结果是一个值。exprSELECTBIGINT

      如果没有匹配的行，则COUNT()返回0。

      如果over_clause存在，此功能将作为窗口功能执行。over_clause如“窗口函数的概念和语法”中所述。

      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 8.0.13开始，如果没有诸如或的额外子句，则针对单线程工作负载优化了表的查询性能。SELECT COUNT(*)FROM tbl_nameInnoDBWHEREGROUP BY

      InnoDBSELECT 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...])

      返回具有不同非NULLexpr值的行数的计数。

      如果没有匹配的行，则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 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子句。要对结果中的值进行排序，请使用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 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]

      将两个列名或表达式作为参数，其中第一个用作键，第二个用作值，并返回包含键值对的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 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)
      重复密钥处理

      当此功能的结果标准化后，具有重复键的值将被丢弃。与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 t VALUES
       ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.01 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.00 sec)

mysql> INSERT INTO t VALUES
       ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.00 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_OBJECTAGG()通过在OVER子句中添加一个带有ORDER BY规范的子句来将特定的键强加到帧行上，从而将其作为窗口函数来调用。以下示例显示了ORDER BY在几种不同的框架规格下有无情况下会发生什么。

      如果没有ORDER BY，则框架是整个分区：

      mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER () AS json_object FROM 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_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}  	|
+-------------	+

      使用ORDER BY和整个分区的显式框架：

      mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS json_object
       FROM t;
+-------------	+
| json_object 	|
+-------------	+
| {"key": 5}  	|
| {"key": 5}  	|
| {"key": 5}  	|
+-------------	+

      要返回特定的键值（例如最小或最大），请LIMIT在适当的查询中包括一个子句。例如：

      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}  	|
+-------------	+

      有关其他信息和示例，请参见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 student
       GROUP 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 student
       GROUP 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如“窗口函数的概念和语法”中所述。

    下篇：GROUP BY 修饰符