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 时的其他注意事项

1. 使用 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(在开始时是升序排序，在结束时是降序排序)。【？？？】

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

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

     【？？？，还是没啥意义嘛？】