MySQL的函数和运算符 - 聚合函数 - GROUP BY 修饰符

数据库杂货铺 2021-09-21

403

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 值标识了超聚合行。

当有多个 GROUP BY 列时，ROLLUP 的效果更复杂。在本例中，每次除最后一个分组列外的任何一列的值发生变化时，查询将生成一个额外的超级聚合摘要行。

例如，在没有 ROLLUP 的情况下，基于 year、country 和 product 的 sales 表汇总可能如下所示，其中输出仅指示分析的 year/country/product 级别的汇总值：

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 和 product 列设置为 NULL。

● 最后，在所有其他行之后，会出现一个额外的超汇总行，显示所有年份、国家和产品的总计。这一行将 year、country 和 product 列设置为 NULL。

每个超级聚合行的 NULL 指示器是在该行被发送到客户端时产生的。服务器查看 GROUP BY 子句中最左边已更改值的列后面的列。对于结果集中名称与这些名称相匹配的任何列，其值都设置为 NULL。(如果指定按列位置分组列，服务器将根据位置标识要将哪些列设置为 NULL。)

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

NULL 值在客户端显示为 NULL，可以使用任何 MySQL 客户端编程接口进行测试。然而，此时无法区分 NULL 表示的是常规分组值还是超聚合值。要测试这种区别，请使用稍后介绍的 GROUPING() 函数。

以前，MySQL 不允许在带有 WITH ROLLUP 选项的查询中使用 DISTINCT 或 ORDER BY。这个限制在 MySQL 8.0.12 和更高版本中被取消。

对于 GROUP BY ... WITH ROLLUP 查询，为了测试结果中的 NULL 值是否代表超聚合值，可以在选择列表、HAVING 子句和 ORDER BY (从 MySQL 8.0.12 开始)子句中使用 GROUPING() 函数。例如，当 year 列在超级聚合行中出现 NULL 时，GROUPING(year) 返回 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 表示类似于 “other” 或 “unknown”：

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 实现的行为。

在 MySQL 8.0.12 之前，当使用 ROLLUP 时，不能使用 ORDER BY 子句对结果进行排序。换句话说，在 MySQL 中 ROLLUP 和 ORDER BY 是互斥的。但是，仍然可以控制排序顺序。要解决不能将 ROLLUP 与 ORDER BY 一起使用并实现分组结果的特定排序顺序的限制，可以将分组结果集生成为派生表并对其应用 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 |
+------+--------+
复制

从 MySQL 8.0.12 开始，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 扩展允许在选择列表中指定未出现在 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 |
+------+---------+--------+
复制

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 聚合函数 - GROUP BY 修饰符

评论

相关阅读