# 12.20.2 GROUP BY 修饰符

通过...分组条款允许带汇总导致汇总输出包含表示更高级别(即超聚合)汇总操作的额外行的修饰符。卷起因此,您可以通过单个查询回答多个分析级别的问题。例如,卷起可用于为 OLAP(在线分析处理)操作提供支持。

假设一个销售量表有,国家,产品, 和利润用于记录销售盈利能力的列:

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

要每年汇总表内容,请使用简单的通过...分组像这样:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

输出显示每年的总(总)利润。要确定所有年份的总利润,您必须自己将各个值相加或运行附加查询。或者你可以使用卷起,它通过单个查询提供两个级别的分析。添加一个带汇总修饰符通过...分组子句使查询生成另一个(超聚合)行,显示所有年份值的总计:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

空值中的价值列标识总计超聚合行。

卷起当有多个时具有更复杂的效果通过...分组列。在这种情况下,每次除最后一个分组列之外的任何值发生变化时,查询都会生成一个额外的超聚合汇总行。

例如,没有卷起, 总结销售量表基于,国家, 和产品可能看起来像这样,其中输出仅表示分析的年份/国家/产品级别的汇总值:

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

卷起添加后,查询会产生几个额外的行:

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

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

  • 在给定年份和国家/地区的每组产品行之后,会出现一个额外的超级汇总摘要行,显示所有产品的总数。这些行具有产品列设置为空值.

  • 在给定年份的每组行之后,会出现一个额外的超级汇总行,显示所有国家和产品的总数。这些行具有国家产品列设置为空值.

  • 最后,在所有其他行之后,出现一个额外的超级汇总行,显示所有年份、国家和产品的总计。这一行有,国家, 和产品列设置为空值.

    空值每个超聚合行中的指标是在该行发送到客户端时生成的。服务器查看在通过...分组最左边的已更改值之后的子句。对于结果集中任何名称与这些名称匹配的列,其值设置为空值.(如果您指定按列位置对列进行分组,服务器会识别要设置的列空值按位置。)

    因为空值超级聚合行中的值在查询处理的后期放入结果集中,您可以将它们测试为空值仅在选择列表中的值或拥有条款。您不能将它们测试为空值连接条件中的值或在哪里子句来确定要选择的行。例如,您不能添加产品为空到查询以从输出中消除除超聚合行之外的所有行。

    空值值确实显示为空值在客户端,可以使用任何 MySQL 客户端编程接口进行测试。但是,在这一点上,您无法区分是否空值表示常规分组值或超聚合值。要测试区别,请使用分组()功能,稍后介绍。

    以前,MySQL 不允许使用清楚的要么订购方式在具有带汇总选项。在 MySQL 8.0.12 及更高版本中取消了此限制。(错误 #87450、错误 #86311、错误 #26640100、错误 #26073513)

    为了GROUP BY ... 与汇总查询,测试是否空值结果中的值表示超聚合值,分组()功能可在选择列表中使用,拥有子句和(从 MySQL 8.0.12 开始)订购方式条款。例如,分组(年)返回 1 时空值在里面column 出现在超聚合行中,否则为 0。相似地,分组(国家)分组(产品)为超聚合返回 1空值中的值国家产品列,分别:

mysql> SELECT
         year, country, product, SUM(profit) AS profit,
         GROUPING(year) AS grp_year,
         GROUPING(country) AS grp_country,
         GROUPING(product) AS grp_product
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+----------+-------------+-------------+
| year | country | product    | profit | grp_year | grp_country | grp_product |
+------+---------+------------+--------+----------+-------------+-------------+
| 2000 | Finland | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | Finland | Phone      |    100 |        0 |           0 |           0 |
| 2000 | Finland | NULL       |   1600 |        0 |           0 |           1 |
| 2000 | India   | Calculator |    150 |        0 |           0 |           0 |
| 2000 | India   | Computer   |   1200 |        0 |           0 |           0 |
| 2000 | India   | NULL       |   1350 |        0 |           0 |           1 |
| 2000 | USA     | Calculator |     75 |        0 |           0 |           0 |
| 2000 | USA     | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | USA     | NULL       |   1575 |        0 |           0 |           1 |
| 2000 | NULL    | NULL       |   4525 |        0 |           1 |           1 |
| 2001 | Finland | Phone      |     10 |        0 |           0 |           0 |
| 2001 | Finland | NULL       |     10 |        0 |           0 |           1 |
| 2001 | USA     | Calculator |     50 |        0 |           0 |           0 |
| 2001 | USA     | Computer   |   2700 |        0 |           0 |           0 |
| 2001 | USA     | TV         |    250 |        0 |           0 |           0 |
| 2001 | USA     | NULL       |   3000 |        0 |           0 |           1 |
| 2001 | NULL    | NULL       |   3010 |        0 |           1 |           1 |
| NULL | NULL    | NULL       |   7535 |        1 |           1 |           1 |
+------+---------+------------+--------+----------+-------------+-------------+

而不是显示分组()结果直接,你可以使用分组()用标签替换超聚合空值价值观:

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS 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       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

使用多个表达式参数,分组()返回一个表示位掩码的结果,将每个表达式的结果组合在一起,最低位对应于最右边表达式的结果。例如,分组(年份、国家、产品)评估如下:

  result for GROUPING(product)
+ result for GROUPING(country) << 1
+ result for GROUPING(year) << 2

这样的结果分组()如果任何表达式表示超聚合,则为非零空值,因此您可以只返回超级聚合行并过滤掉常规分组行,如下所示:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

销售量表不包含空值价值观,所以所有空值a中的值卷起结果表示超聚合值。当数据集包含空值价值观,卷起摘要可能包含空值值不仅在超聚合行中,而且在常规分组行中。分组()可以区分这些。假设那张桌子t1包含一个简单的数据集,其中包含一组数量值的两个分组因子,其中空值表示“其他”或“未知”之类的东西:

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | small |       10 |
| ball | large |       20 |
| ball | NULL  |        5 |
| hoop | small |       15 |
| hoop | large |        5 |
| hoop | NULL  |        3 |
+------+-------+----------+

一个简单的卷起操作会产生这些结果,其中不容易区分空值超聚合行​​中的值来自空值常规分组行中的值:

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | NULL  |        5 |
| ball | large |       20 |
| ball | small |       10 |
| ball | NULL  |       35 |
| hoop | NULL  |        3 |
| hoop | large |        5 |
| hoop | small |       15 |
| hoop | NULL  |       23 |
| NULL | NULL  |       58 |
+------+-------+----------+

使用分组()用标签替换超聚合空值values 使结果更容易解释:

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
         SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name      | size      | quantity |
+-----------+-----------+----------+
| ball      | NULL      |        5 |
| ball      | large     |       20 |
| ball      | small     |       10 |
| ball      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+

# 使用 ROLLUP 时的其他注意事项

以下讨论列出了特定于 MySQL 实现的一些行为卷起.

在 MySQL 8.0.12 之前,当您使用卷起, 你也不能使用订购方式子句对结果进行排序。换一种说法,卷起订购方式在 MySQL 中是互斥的。但是,您仍然可以控制排序顺序。解决阻止使用的限制卷起订购方式并实现分组结果的特定排序顺序,将分组结果集生成为派生表并应用订购方式给它。例如:

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

从 MySQL 8.0.12 开始,订购方式卷起可以一起使用,这使得使用订购方式分组()以实现分组结果的特定排序顺序。例如:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP
       ORDER BY GROUPING(year) DESC;
+------+--------+
| year | profit |
+------+--------+
| NULL |   7535 |
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

在这两种情况下,超聚合汇总行都根据计算它们的行进行排序,它们的位置取决于排序顺序(升序排序在末尾,降序排序在开头)。

限制可用于限制返回给客户端的行数。限制后应用卷起,因此限制适用于添加的额外行卷起.例如:

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 扩展允许没有出现在通过...分组要在选择列表中命名的列表。(有关非聚合列的信息和通过...分组, 看第 12.20.3 节,“MySQL 对 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 模式。如果启用了该模式,服务器将拒绝该查询为非法的,因为国家未列在通过...分组条款。和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 8.0.28 及更高版本中,汇总列不能用作匹配()(并因错误而被拒绝)除非在 a 中调用在哪里条款。看第 12.10 节,“全文搜索功能”, 了解更多信息。