12.20.2 GROUP BY 修饰符

GROUP BY子句允许使用WITH ROLLUP修饰符,该修饰符使摘要输出包括代 table 更高级别(即超级聚合)摘要操作的额外行。 ROLLUP从而使您可以通过单个查询回答多个分析级别的问题。例如,ROLLUP可用于提供对 OLAP(在线分析处理)操作的支持。

假设salestable 具有yearcountryproductprofit列以记录销售获利能力:

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

要汇总每年的 table 格内容,请使用简单的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,则基于yearcountryproductsalestable 的摘要可能看起来像这样,其中输出仅指示分析的年份/国家/产品级别的摘要值:

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

现在,输出包括四个分析级别的摘要信息,而不仅仅是一个:

  • 在给定年份和国家/locale 的每组产品行之后,都会出现一个额外的超级汇总摘要行,显示所有产品的总计。这些行的product列设置为NULL

  • 在给定年份的每组行之后,将出现一个额外的超级汇总行,显示所有国家和产品的总计。这些行的countryproducts列设置为NULL

  • 最后,在所有其他行之后,将出现一个额外的超级汇总摘要行,显示所有年份,国家和产品的总计。该行的yearcountryproducts列设置为NULL

当超级汇总行中的NULL指示符发送到 Client 端时,它们就会产生。服务器查看在GROUP BY子句中命名的列,该列紧随其后的是更改了值的最左列。对于结果集中名称与任何名称匹配的任何列,其值均设置为NULL。 (如果您指定按列位置分组的列,则服务器会识别要按位置设置为NULL的列.)

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

NULL的值在 Client 端确实显示为NULL,并且可以使用任何 MySQLClient 端编程接口进行测试。但是,在这一点上,您无法区分NULL是 table 示常规分组值还是超汇总值。在 MySQL 8.0 中,可以使用GROUPING()函数测试区别。

使用 ROLLUP 时的其他注意事项

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

使用ROLLUP时,也不能使用ORDER BY子句对结果进行排序。换句话说,ROLLUPORDER BY在 MySQL 中是互斥的。但是,您仍然可以控制排序 Sequences。要解决防止将ROLLUPORDER BY结合使用并实现特定的分组结果排序 Sequences 的限制,请生成分组结果集作为派生 table 并将ORDER BY应用于该 table。例如:

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 端的行数。 LIMITROLLUP之后应用,因此该限制适用于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 |
+------+---------+------------+--------+

LIMITROLLUP一起使用可能会产生更难以解释的结果,因为了解超级聚集行的上下文较少。

MySQL 扩展允许在选择列 table 中命名不在GROUP BY列 table 中出现的列。 (有关未聚合的列和GROUP BY的信息,请参阅第 12.20.3 节“ MySQL BY GROUP BY 的处理”。)在这种情况下,服务器可以从摘要行中的该未聚合的列中自由选择任何值,其中包括WITH ROLLUP添加的多余行。例如,在以下查询中,country是未聚合的列,不会出现在GROUP BY列 table 中,并且为此列选择的值是不确定的:

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