MySQL :: MySQL 8.4 参考手册 :: 14.19.2 GROUP BY 修饰符

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / GROUP BY 修饰符

14.19.2 GROUP BY 修饰符

GROUP BY 子句允许使用 WITH ROLLUP 修饰符，该修饰符会使汇总输出包含表示更高级别（即超聚合）汇总操作的额外行。ROLLUP 因此使您能够使用单个查询回答多个分析级别的查询。例如，ROLLUP 可用于提供对 OLAP（联机分析处理）操作的支持。

假设一个 sales 表包含 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 值标识了总计超聚合行。

ROLLUP 在存在多个 GROUP BY 列时具有更复杂的影响。在这种情况下，每当除最后一个分组列以外的任何分组列的值发生变化时，查询都会生成一个额外的超聚合汇总行。

例如，如果没有 ROLLUP，基于 year、country 和 product 的 sales 表的汇总可能如下所示，其中输出仅指示年份/国家/产品级别的汇总值

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

现在输出包含四个分析级别的汇总信息，而不仅仅是一个级别

在特定年份和国家/地区的每个产品行集之后，会出现一个额外的超聚合汇总行，显示所有产品的总计。这些行的 product 列设置为 NULL。
在特定年份的每一行集之后，会出现一个额外的超聚合汇总行，显示所有国家/地区和产品的总计。这些行的 country 和 products 列设置为 NULL。
最后，在所有其他行之后，会出现一个额外的超聚合汇总行，显示所有年份、国家/地区和产品的总计。此行的 year、country 和 products 列设置为 NULL。

超聚合行中的 NULL 指示器是在行发送到客户端时产生的。服务器会查看 GROUP BY 子句中命名的列，这些列紧跟在最左侧发生变化值的列之后。对于结果集中的任何具有与这些名称匹配的名称的列，其值将设置为 NULL。（如果按列位置指定分组列，则服务器会按位置识别要设置为 NULL 的列。）

由于超聚合行中的 NULL 值是在查询处理的非常晚的阶段放入结果集中的，因此您只能在 select 列表或 HAVING 子句中将它们作为 NULL 值进行测试。您不能在连接条件或 WHERE 子句中将它们作为 NULL 值进行测试，以确定要选择的行。例如，您不能在查询中添加 WHERE product IS NULL 以从输出中排除所有行，除了超聚合行。

这些 NULL 值确实会以 NULL 的形式出现在客户端，并且可以使用任何 MySQL 客户端编程接口以这种形式进行测试。但是，此时，您无法区分 NULL 表示常规分组值还是超聚合值。要测试这种区别，请使用 GROUPING() 函数，该函数将在后面介绍。

对于 GROUP BY ... WITH ROLLUP 查询，要测试结果中的 NULL 值是否表示超聚合值，GROUPING() 函数可用于 select 列表、HAVING 子句和 ORDER BY 子句中。例如，GROUPING(year) 在 year 列中的 NULL 出现在超聚合行中时返回 1，否则返回 0。类似地，GROUPING(country) 和 GROUPING(product) 分别为 country 和 product 列中的超聚合 NULL 值返回 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 |
+------+---------+------------+--------+----------+-------------+-------------+

您无需直接显示 GROUPING() 结果，而是可以使用 GROUPING() 用标签替换超聚合 NULL 值

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

使用多个表达式参数，GROUPING() 返回一个表示位掩码的结果，该位掩码组合了每个表达式的结果，最低有效位对应于最右侧表达式的结果。例如，GROUPING(year, country, product) 按以下方式进行评估

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

如果任何表达式表示超聚合 NULL，则此类 GROUPING() 的结果将非零，因此您可以仅返回超聚合行并过滤掉常规分组行，如下所示

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

sales 表不包含任何 NULL 值，因此 ROLLUP 结果中的所有 NULL 值都代表超聚合值。当数据集包含 NULL 值时，ROLLUP 摘要可能不仅在超聚合行中包含 NULL 值，而且还在常规分组行中包含 NULL 值。GROUPING() 使这些值能够区分。假设表 t1 包含一个简单的数据集，其中包含两组用于一组数量值的组合因素，其中 NULL 表示类似于 “其他” 或 “未知”

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

简单的 ROLLUP 操作将产生以下结果，其中很难区分超聚合行中的 NULL 值和常规分组行中的 NULL 值

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

使用 GROUPING() 用标签替换超聚合 NULL 值，使结果更容易解释

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 中 ROLLUP 实现的一些特定行为。

ORDER BY 和 ROLLUP 可以一起使用，这使得使用 ORDER BY 和 GROUPING() 来实现分组结果的特定排序顺序成为可能。例如

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

在这两种情况下，超聚合汇总行都与计算它们的行列一起排序，它们的位置取决于排序顺序（对于升序排序，位于末尾；对于降序排序，位于开头）。

LIMIT 可以用来限制返回给客户端的行数。 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 扩展允许在 select 列表中命名不在 GROUP BY 列表中的列。（有关非聚合列和 GROUP BY 的信息，请参见第 14.19.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 模式未启用时，允许这种行为。如果该模式已启用，则服务器会将查询拒绝为非法，因为 country 未在 GROUP BY 子句中列出。启用 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 |
+------+---------+--------+

汇总列不能用作 MATCH() 的参数（并会引发错误），除非它在 WHERE 子句中被调用。有关详细信息，请参见第 14.9 节，“全文搜索函数”。