MySQL 函数和运算符：汇总函数

关于

汇总函数（or 集合函数？？？）对值集进行操作。它们通常与“GROUP BY”子句一起使用以将值分组为子集。

Name	Description
AVG()	返回参数的平均值
BIT_AND()	按位返回 AND
BIT_OR()	按位返回 OR
BIT_XOR()	返回按位异或
COUNT()	返回计数返回的行数
COUNT(DISTINCT)	返回多个不同值的计数
GROUP_CONCAT()	返回串联的字符串
JSON_ARRAYAGG()(引入 5.7.22)	将结果集作为单个 JSON 数组返回
JSON_OBJECTAGG()(引入 5.7.22)	将结果集作为单个 JSON 对象返回
MAX()	返回最大值
MIN()	返回最小值
STD()	返回人口标准差
STDDEV()	返回人口标准差
STDDEV_POP()	返回人口标准差
STDDEV_SAMP()	返回 sample 标准偏差
SUM()	返回总和
VAR_POP()	返回总体标准方差
VAR_SAMP()	返回 samples 方差
VARIANCE()	返回总体标准方差

【说明：略】

GROUP BY 【WITH ROLLUP】

“GROUP BY”子句允许使用“WITH ROLLUP”修饰符，该修饰符使摘要输出包括代表更高级别（即超级汇总）摘要操作的额外行【？？？】。 ROLLUP 从而使您可以通过单个查询回答多个分析级别的问题。

例如，ROLLUP可用于提供对 OLAP(在线分析处理)操作的支持。

假设 salest 表具有 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，它通过一个查询提供两个分析级别。在GROUP BY子句中添加“WITH ROLLUP”修饰符会使查询产生另一行(超级汇总)，该行显示所有年份值的总计：

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 的作用更为复杂。在这种情况下，每当除最后一个分组列之外的任何其他列中的值发生变化时，查询都会产生一个额外的超级汇总摘要行。

例如，如果没有 ROLLUP，则基于 year，country 和 product 的 salest 表的摘要可能看起来像这样，其中输出仅指示分析的年份/国家/产品级别的摘要值：

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

当超级汇总行中的NULL指示符发送到 Client 端时，它们就会产生。服务器查看在GROUP BY子句中命名的列，该列紧随其后的是更改了值的最左列。对于结果集中名称与任何名称匹配的任何列，其值均设置为NULL。 (如果您指定按列位置分组的列，则服务器会识别要按位置设置为NULL的列.)【？？？】
由于超级聚合行中的 NULL 值是在查询处理的最后阶段放入结果集中的，因此只能在选择列表或“HAVING”子句中将它们作为 NULL 值进行测试。您不能在连接条件中或 WHERE 子句中将它们测试为NULL值来确定要选择的行。
例如，您不能将“WHERE product IS NULL”添加到查询中以从输出中消除除超级聚合行以外的所有行。
NULL 的值在 Client 端确实显示为 NULL，并且可以使用任何 MySQLClient 端编程接口进行测试。但是，在这一点上，您无法区分 NULL 是表示常规分组值还是超汇总值。在 MySQL 8.0 中，可以使用“GROUPING()”函数测试区别。【？？？】

使用 ROLLUP 时的其他注意事项

使用 ROLLUP 时，也不能使用 ORDER BY 子句对结果进行排序。换句话说，ROLLUP 和 ORDER BY 在 MySQL 中是互斥的。但是，您仍然可以控制排序顺序。要解决防止将 ROLLUP 与 ORDER BY 结合使用并实现特定的分组结果排序 Sequences 的限制，请生成分组结果集作为派生表并将 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 |
+------+--------+
```
在这种情况下，超级汇总摘要行将根据其进行计算的行进行排序，并且它们的位置取决于排序 Sequences(在开始时是升序排序，在结束时是降序排序)。【？？？】

LIMIT可用于限制返回给 Client 端的行数。 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 |
+------+---------+------------+--------+

MySQL 扩展允许在选择列表中命名不在 GROUP BY 列表中出现的列。在这种情况下，服务器可以从摘要行中的该未聚合的列中自由选择任何值，其中包括“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，因此服务器将查询拒绝为非法。启用“ONLY_FULL_GROUP_BY”后，您仍然可以通过对非确定值列使用“ANY_VALUE()”函数来执行查询：
  mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; +------+---------+--------+ | year | country | profit | +------+---------+--------+ | 2000 | India | 4525 | | 2001 | USA | 3010 | | NULL | USA | 7535 | +------+---------+--------+
  【？？？，还是没啥意义嘛？】

MySQL 对 GROUP BY 的处理

【接上一节最后，MySQL如何处理“选择列表，HAVING条件或 ORDER BY列表引用未在GROUP BY子句中命名的未聚合列”的查询】

SQL-92 和更早版本不允许选择列表，HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名的未聚合列的查询。

例如，此查询在标准 SQL-92 中是非法的，因为选择列表中未聚合的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;

为了使查询在 SQL-92 中合法，必须从选择列表中省略name列或在GROUP BY子句中命名。

SQL：1999 和更高版本允许在功能上依赖于GROUP BY列的每个非可选功能 T301 此类非聚合：如果name和custid之间存在这种关系，则查询合法。

例如，custid是customers的主键就是这种情况。

MySQL 5.7.5 及更高版本实现对功能依赖性的检测。如果启用了“ONLY_FULL_GROUP_BY” SQL 模式(默认情况下是默认设置)，则 MySQL 拒绝选择列表，HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名且在功能上不依赖于它们的未聚合列的查询。(在 5.7.5 之前，MySQL 不检测功能依赖关系，默认情况下未启用ONLY_FULL_GROUP_BY。)

启用ONLY_FULL_GROUP_BY SQL 模式时，MySQL 5.7.5 和更高版本还允许在GROUP BY子句中未命名的非聚合列，但前提是该列限于单个值，如以下示例所示：

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

使用ONLY_FULL_GROUP_BY时，也可能在SELECT列 table 中有多个非聚合列。在这种情况下，每个这样的列都必须限制为一个值，并且所有这样的限制条件必须由逻辑“AND”联接，如下所示：

mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b VARCHAR(10),
    ->    c INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
    ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+

如果禁用了“ONLY_FULL_GROUP_BY”，则对标准 SQL 使用GROUP BY的 MySQL 扩展允许选择列表，HAVING条件或ORDER BY列表引用未聚合的列，即使这些列在功能上不依赖于GROUP BY列也是如此。这使 MySQL 接受前面的查询。在这种情况下，服务器可以从每个组中自由选择任何值，因此，除非它们相同，否则选择的值是不确定的，这可能不是您想要的。

此外，通过添加ORDER BY子句不能影响每个组中值的选择。选择值之后，将进行结果集排序，并且ORDER BY不会影响服务器在每个组中选择哪个值。

禁用“ONLY_FULL_GROUP_BY”一般是有用的，因为您知道由于数据的某些属性，每个未聚合的列中在GROUP BY中未命名的所有值对于每个组都是相同的。
通过使用“ANY_VALUE()”引用未聚合的列，您可以在不禁用“ONLY_FULL_GROUP_BY”的情况下实现相同的效果。

关于功能依赖性

下面的讨论演示了功能依赖性，不存在功能依赖性时 MySQL 产生的错误消息，以及在没有功能依赖性的情况下使 MySQL 接受查询的方法。
【。。。。。略。。。。。】

检测函数依赖性

【。。。。。略。。。。。】

MySQL 函数和运算符：汇总函数

目录

关于

GROUP BY 【WITH ROLLUP】

使用 ROLLUP 时的其他注意事项

MySQL 对 GROUP BY 的处理

关于功能依赖性

检测函数依赖性

导航菜单

MySQL 函数和运算符：汇总函数

关于

GROUP BY 【WITH ROLLUP】

使用 ROLLUP 时的其他注意事项

MySQL 对 GROUP BY 的处理

关于功能依赖性

检测函数依赖性

导航菜单

搜索