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

来自Wikioe
Eijux（讨论 | 贡献）2021年4月3日 (六) 03:21的版本 （→‎GROUP BY）
跳到导航 跳到搜索


关于

汇总函数（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 |
+------+---------+--------+
      【？？？，还是没啥意义嘛？】

MySQL 对 GROUP BY 的处理

检测函数依赖性

取自“http://wiki.eijux.com/index.php?title=MySQL_函数和运算符：汇总函数&oldid=2243”