• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 聚合功能描述(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为近似值参数(值FLOATDOUBLE)。

    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()期望数字参数的函数在必要时将参数强制转换为数字。对于SETENUM值,强制转换操作将导致使用基础数字值。

    BIT_AND()BIT_OR()BIT_XOR()聚合函数执行位操作。在MySQL 8.0之前,位函数和运算符需要参数BIGINT(64位整数)和返回BIGINT值,因此它们的最大范围为64位。在执行该操作之前,将非BIGINT参数转换为BIGINT,并且可能会发生截断。

    在MySQL 8.0中,位函数和运算符允许使用二进制字符串类型的参数(BINARYVARBINARYBLOB类型)并返回类似类型的值,这使它们能够采用参数并产生大于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_packetgroup_concat_max_len在运行时更改value的语法如下,其中val是一个无符号整数:

      SET [GLOBAL | SESSION] group_concat_max_len = val;
      

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

      另请参见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]

      返回的最大值exprMAX()可以采用字符串参数;在这种情况下,它将返回最大字符串值。请参见“ 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当前根据字符串值而不是字符串在集合中的相对位置来比较ENUMSET列。这与ORDER BY比较它们的方式不同。

    • MIN([DISTINCT]expr)[over_clause]

      返回的最小值exprMIN()可以采用字符串参数;在这种情况下,它将返回最小字符串值。请参见“ 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当前根据字符串值而不是字符串在集合中的相对位置来比较ENUMSET列。这与ORDER BY比较它们的方式不同。

    • STD(expr)[over_clause]

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

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

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

    • STDDEV(expr)[over_clause]

      返回的总体标准差exprSTDDEV()是标准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]

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

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

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