12.20.2 GROUP BY 修饰符
GROUP BY
子句允许使用WITH ROLLUP
修饰符,该修饰符使摘要输出包括代 table 更高级别(即超级聚合)摘要操作的额外行。 ROLLUP
从而使您可以通过单个查询回答多个分析级别的问题。例如,ROLLUP
可用于提供对 OLAP(在线分析处理)操作的支持。
假设sales
table 具有year
,country
,product
和profit
列以记录销售获利能力:
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
,则基于year
,country
和product
的sales
table 的摘要可能看起来像这样,其中输出仅指示分析的年份/国家/产品级别的摘要值:
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
。 -
在给定年份的每组行之后,将出现一个额外的超级汇总行,显示所有国家和产品的总计。这些行的
country
和products
列设置为NULL
。 -
最后,在所有其他行之后,将出现一个额外的超级汇总摘要行,显示所有年份,国家和产品的总计。该行的
year
,country
和products
列设置为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
子句对结果进行排序。换句话说,ROLLUP
和ORDER BY
在 MySQL 中是互斥的。但是,您仍然可以控制排序 Sequences。要解决防止将ROLLUP
与ORDER 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 端的行数。 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 扩展允许在选择列 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 |
+------+---------+--------+