12.19 Aggregate (GROUP BY) Functions

「爱情、让人受尽委屈。」 2021-11-11 03:42 194阅读 0赞

12.19.1 Aggregate (GROUP BY) Function Descriptions  
12.19.2 GROUP BY Modifiers  
12.19.3 MySQL Handling of GROUP BY

### 12.19.1 Aggregate (GROUP BY) Function Descriptions ###

本节描述对值集进行操作的组(聚合)函数。

Table 12.24 Aggregate (GROUP BY) Functions

<table> 
 <thead> 
  <tr> 
   <th style="text-align:left;">Name</th> 
   <th style="text-align:left;">Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:left;">AVG()</td> 
   <td style="text-align:left;">Return the average value of the argument</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">BIT_AND()</td> 
   <td style="text-align:left;">Return bitwise AND</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">BIT_OR()</td> 
   <td style="text-align:left;">Return bitwise OR</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">BIT_XOR()</td> 
   <td style="text-align:left;">Return bitwise XOR</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">COUNT()</td> 
   <td style="text-align:left;">Return a count of the number of rows returned</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">COUNT(DISTINCT)</td> 
   <td style="text-align:left;">Return the count of a number of different values</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">GROUP_CONCAT()</td> 
   <td style="text-align:left;">Return a concatenated string</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">MAX()</td> 
   <td style="text-align:left;">Return the maximum value</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">MIN()</td> 
   <td style="text-align:left;">Return the minimum value</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">STD()</td> 
   <td style="text-align:left;">Return the population standard deviation</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">STDDEV()</td> 
   <td style="text-align:left;">Return the population standard deviation</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">STDDEV_POP()</td> 
   <td style="text-align:left;">Return the population standard deviation</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">STDDEV_SAMP()</td> 
   <td style="text-align:left;">Return the sample standard deviation</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">SUM()</td> 
   <td style="text-align:left;">Return the sum</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">VAR_POP()</td> 
   <td style="text-align:left;">Return the population standard variance</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">VAR_SAMP()</td> 
   <td style="text-align:left;">Return the sample variance</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">VARIANCE()</td> 
   <td style="text-align:left;">Return the population standard variance</td> 
  </tr> 
 </tbody> 
</table>

除非另有说明，否则组函数忽略空值。

如果在不包含group BY子句的语句中使用GROUP函数，它相当于对所有行进行分组。有关更多信息，请参见第12.19.3节“MySQL Handling of GROUP BY”. 。

对于数值参数，方差和标准差函数返回一个DOUBLE值。SUM()和AVG()函数为精确值参数(整数或十进制)返回十进制值，为近似值参数(浮点或双精度)返回双精度值。

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并可能发生截断。

*  AVG(\[DISTINCT\] expr)
    
    返回expr的平均值。 DISTINCT选项可用于返回expr的不同值的平均值。
    
    如果没有匹配的行，AVG()返回NULL。
    
        mysql> SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;
 *  BIT\_AND(expr)
    
    返回expr中所有位的按位AND。 计算以64位（BIGINT）精度执行。
    
    如果没有匹配的行，BIT\_AND()将返回一个中间值(所有位都设置为1)。
 *  BIT\_OR(expr)
    
    返回expr中所有位的按位OR。 计算以64位（BIGINT）精度执行。
    
    如果没有匹配的行，BIT\_OR()将返回一个中间值(所有位都设置为0)。
 *  BIT\_XOR(expr)
    
    返回expr中所有位的按位异或。 计算以64位（BIGINT）精度执行。
    
    如果没有匹配的行，则BIT\_XOR（）返回中间值（所有位都设置为0）。
 *  COUNT(expr)
    
    返回SELECT语句检索的行中expr的非空值的数量的计数。结果是一个BIGINT值。
    
    如果没有匹配的行，COUNT()返回0。
    
        mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;
    
    COUNT(\*)有所不同，它返回检索到的行数的计数，不管它们是否包含空值。
    
    对于InnoDB等事务存储引擎，存储精确的行数是有问题的。多个事务可能同时发生，每个事务都可能影响计数。
    
    InnoDB不保留表中的内部行数，因为并发事务可能同时“看到”不同数量的行。 因此，SELECT COUNT（\*）语句只计算当前事务可见的行。
    
    为了处理SELECT COUNT（\*）语句，InnoDB扫描表的索引，如果索引不完全在缓冲池中，则需要一些时间。 为了更快地计算，请创建一个计数器表，让应用程序根据插入和删除更新它。 但是，在数千个并发事务正在启动对同一计数器表的更新的情况下，此方法可能无法很好地扩展。 如果大概的行数足够，请使用SHOW TABLE STATUS。
    
    InnoDB以相同的方式处理SELECT COUNT（\*）和SELECT COUNT（1）操作。 没有性能差异。
    
    对于MyISAM表，COUNT(\*)被优化为如果SELECT从一个表中检索，没有检索其他列，并且没有WHERE子句，则返回非常快。例如:
    
        mysql> SELECT COUNT(*) FROM student;
    
    这种优化仅适用于MyISAM表，因为存储引擎存储了精确的行数，并且可以非常快地访问。仅当第一列定义为非空时，count(1)才会进行相同的优化。
 *  COUNT(DISTINCT expr,\[expr...\])
 *  GROUP\_CONCAT(expr)
 *  MAX(\[DISTINCT\] expr)
 *  MIN(\[DISTINCT\] expr)
 *  STD(expr)
 *  STDDEV(expr)
 *  STDDEV\_POP(expr)
 *  STDDEV\_SAMP(expr)
 *  SUM(\[DISTINCT\] expr)
 *  VAR\_POP(expr)
 *  VAR\_SAMP(expr)
 *  VARIANCE(expr)

### 12.19.2 GROUP BY Modifie ###

GROUP BY子句允许一个WITH ROLLUP修饰符，该修饰符使汇总输出包含表示更高级别(即超级汇总)汇总操作的额外行。因此，ROLLUP使您能够用一个查询在多个分析级别回答问题。例如，汇总可用于为OLAP(在线分析处理)运营提供支持。

假设一个 sales 表中有 year，country， product 和用于记录销售利润的 profit 列：

CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT );

要总结每年的表格内容，请使用简单的GROUP BY，如下所示：

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | +------+--------+

输出显示每年的总（总）利润。 要确定所有年份的总利润总额，您必须自己添加单个值或运行其他查询。 或者您可以使用ROLLUP，它使用单个查询提供两种级别的分析。 将WITH ROLLUP修饰符添加到GROUP BY子句会导致查询生成另一个（超级聚合）行，该行显示所有年份值的总计：

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+--------+

year 列中的 NULL 值标识超级聚合行的总计数。

当存在多个GROUP BY列时，ROLLUP具有更复杂的效果。 在这种情况下，每次除最后一个 grouping column之外的任何值发生更改时，查询都会生成一个额外的超级聚合摘要行。

例如，如果没有ROLLUP，则基于 year，country 和 product 的销售表摘要可能如下所示，其中输出仅在year/country/product 分析级别指示汇总值：

mysql> SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product; +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2001 | Finland | Phone | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | +------+---------+------------+--------+

添加ROLLUP后，查询会生成几个额外的行：

mysql> SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP; +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | India | NULL | 1350 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Finland | Phone | 10 | | 2001 | Finland | NULL | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | +------+---------+------------+--------+

现在输出包括四个层次的分析的摘要信息，而不仅仅是一个层次

*  在给定 year 和 country 的每一组产品行之后，将出现一个额外的超级汇总汇总行，显示所有产品的总和。这些行将product列设置为NULL。
 *  在给定 year 的每组行之后，将显示一个额外的超级汇总摘要行，显示所有 country 和 product 的总计。 这些行的country和products列设置为NULL。
 *  最后，在所有其他行之后，将出现一个额外的超级汇总汇总行，显示所有年份、国家和产品的总金额。这一行将year、country和products列设置为NULL。

将行发送到客户端时，将生成每个超级聚合行中的NULL指示符。 服务器查看GROUP BY子句中指定的列，这些列位于最左侧的已更改值的子句之后。 对于结果集中名称与其中任何名称匹配的任何列，其值设置为NULL。 （如果按列位置指定分组列，则服务器会按位置标识要设置为NULL的列。）

由于超级聚合行中的NULL值在查询处理的后期阶段放入结果集中，因此只能在选择列表或HAVING子句中将它们作为NULL值进行测试。 您无法在连接条件或WHERE子句中将它们作为NULL值进行测试，以确定要选择的行。 例如，您不能将WHERE product IS NULL添加到查询中以从输出中消除除超级聚合行之外的所有内容。

NULL值在客户端显示为NULL，可以使用任何MySQL客户端编程接口进行测试。 但是，此时，您无法区分NULL是表示常规分组值还是超聚合值。

### 使用ROLLUP时的其他注意事项 ###

下面的讨论列出了一些特定于ROLLUP的MySQL实现的行为。

使用ROLLUP时，不能使用ORDER BY子句对结果进行排序。 换句话说，ROLLUP和ORDER BY在MySQL中是互斥的。 但是，您仍然可以控制排序顺序。 默认情况下，MySQL中的GROUP BY隐式排序结果（在没有ASC或DESC指示符的情况下）。 但是，不推荐使用MySQL中的隐式GROUP BY排序。 要实现分组结果的特定排序顺序：

*  将明确的ASC和DESC关键字与GROUP BY列表中指定的列一起使用，以指定各列的排序顺序。 在这种情况下，ROLLUP添加的超级聚合摘要行仍会显示在计算它们的行之后，而不管排序顺序如何。
 *  要解决使用ROLLUP 和 ORDER BY的限制，可以将分组结果集生成为派生表，并对其应用ORDER BY。例如
    
        mysql> SELECT * FROM (SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP) AS dt ORDER BY year DESC; +------+--------+ | year | profit | +------+--------+ | 2001 | 3010 | | 2000 | 4525 | | NULL | 7535 | +------+--------+
    
    在这种情况下，超级聚合摘要行按计算它们的行进行排序，它们的位置取决于排序顺序（在开头用于升序排序，在结尾用于降序排序）

LIMIT可用于限制返回给客户端的行数。LIMIT在ROLLUP之后应用，因此该限制适用于ROLLUP添加的额外行。例如:

mysql> SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP LIMIT 5; +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | +------+---------+------------+--------+

将LIMIT与ROLLUP一起使用可能会产生更难解释的结果，因为用于理解超聚合行的上下文较少。

MySQL扩展允许在选择列表中命名未出现在GROUP BY列表中的列。 （有关非聚合列和GROUP BY的信息，请参见第12.19.3节“GROUP BY的MySQL处理”。）在这种情况下，服务器可以自由选择摘要行中此非聚合列的任何值，这包括额外的 WITH ROLLUP添加的行。 例如，在以下查询中，country是未出现在GROUP BY列表中的非聚合列，并且为此列选择的值是不确定的：

mysql> SELECT year, country, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; +------+---------+--------+ | year | country | profit | +------+---------+--------+ | 2000 | India | 4525 | | 2001 | USA | 3010 | | NULL | USA | 7535 | +------+---------+--------+

如果未启用ONLY\_FULL\_GROUP\_BY SQL模式，则允许此行为。 如果启用该模式，则服务器会将查询拒绝为非法，因为GROUP BY子句中未列出 country 。

### 12.19.3 Mysql对GROUP BY的处理 ###

在标准SQL中，包含GROUP BY子句的查询不能引用select列表中没有在GROUP BY子句中命名的非聚合列。例如，这个查询在标准SQL中是非法的，因为select列表中的非聚合name列未出现在GROUP BY中

SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;

要使查询合法，必须从选择列表中省略name列，或在GROUP BY子句中指定name列。

MySQL扩展了GROUP BY的标准SQL使用，因此选择列表可以引用GROUP BY子句中未指定的非聚合列。 这意味着前面的查询在MySQL中是合法的。 您可以通过避免不必要的列排序和分组来使用此功能来获得更好的性能。 但是，当GROUP BY中未指定的每个非聚合列中的所有值对于每个组都相同时，这非常有用。 服务器可以自由选择每个组中的任何值，因此除非它们相同，否则所选的值是不确定的。 此外，添加ORDER BY子句不会影响每个组中值的选择。 结果集排序发生在选择值之后，ORDER BY不会影响服务器选择的每个组中的值。

类似的MySQL扩展适用于HAVING子句。 在标准SQL中，查询中HAVING子句不能引用未在GROUP BY子句中指定的非聚合列。 为简化计算，MySQL扩展允许引用此类列。 此扩展假定非组合列具有相同的分组值。 否则，结果是不确定的。

要禁用MySQL GROUP BY扩展并启用标准SQL行为，请启用ONLY\_FULL\_GROUP\_BY SQL模式。 在这种情况下，GROUP BY子句中未命名的列不能在选择列表或HAVING子句中使用，除非包含在聚合函数中。

选择列表扩展也适用于ORDER BY。 也就是说，您可以在ORDER BY子句中引用未出现在GROUP BY子句中的非聚合列。 （但是，如前所述，ORDER BY不会影响从非聚合列中选择哪些值;它只会在选择它们后对它们进行排序。）如果启用了ONLY\_FULL\_GROUP\_BY SQL模式，则此扩展名不适用。

如果查询具有聚合函数但没有GROUP BY子句，启用了ONLY\_FULL\_GROUP\_BY的select列表、HAVING条件或ORDER BY列表中不能有未聚合的列：

mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

没有GROUP BY，只有一个组，为该组选择哪个 name 值是不确定的。

标准SQL的另一个MySQL扩展允许HAVING子句引用选择列表中的别名表达式。启用ONLY\_FULL\_GROUP\_BY会阻止此操作。 例如，以下查询返回在表顺序中仅出现一次的名称值; 无论是否启用ONLY\_FULL\_GROUP\_BY，都接受查询：

SELECT name, COUNT(name) FROM orders GROUP BY name HAVING COUNT(name) = 1;

仅当禁用ONLY\_FULL\_GROUP\_BY时，才接受以下查询。

SELECT name, COUNT(name) AS c FROM orders GROUP BY name HAVING c = 1;

如果您想要遵循标准SQL，则只能在GROUP BY子句中使用列表达式。 作为一种解决方法，请为表达式使用别名：

SELECT id, FLOOR(value/100) AS val FROM tbl_name GROUP BY id, val;

MySQL允许GROUP BY子句中的非列表达式，因此别名是不必要的

SELECT id, FLOOR(value/100) FROM tbl_name GROUP BY id, FLOOR(value/100);

转载于:https://www.cnblogs.com/BxScope/p/10861563.html