MySQL的函数和运算符 - 聚合函数 - MySQL 处理 GROUP BY

数据库杂货铺 2021-09-21

635

MySQL 处理 GROUP BY

SQL-92 及更早版本不允许选择列表、HAVING 条件或 ORDER BY 列表引用 GROUP BY 子句中未指定的非聚合列的查询。例如，以下这个查询在标准 SQL-92 中是非法的，因为选择列表中的非聚合 name 列没有出现在 GROUP BY 中：

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;
复制

为了使查询在 SQL-92 中合法，name 列必须从选择列表中删除，或者在 GROUP BY 子句中指定。

SQL:1999 和以后的版本中，如果功能上依赖于 GROUP BY 列，基于可选特性 T301，允许这样的非聚合：如果 name 和 custid 之间存在这样的关系，则查询是合法的。例如，如果 custid 是 customers 的主键，情况就是这样。

MySQL 实现了功能依赖的检测。如果启用了 ONLY_FULL_GROUP_BY SQL 模式(默认情况下启用)，MySQL 将拒绝查询列表、HAVING 条件或 ORDER BY 列表引用非聚合列的查询，这些列既不在 GROUP BY 子句中指定，在功能上也不依赖于它们。

当启用 ONLY_FULL_GROUP_BY SQL 模式时，MySQL 也允许不在 GROUP BY 子句中指定非聚合列，只要该列限制为单个值，如下例所示：

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+
复制

当使用 ONLY_FULL_GROUP_BY 时，也可能在 SELECT 列表中有多个非聚合列。在这种情况下，每个这样的列必须限制在 WHERE 子句中的单个值，并且所有这样的限制条件必须通过逻辑 AND 连接，如下所示：

mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b VARCHAR(10),
    ->    c INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
    ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+
复制

如果禁用了 ONLY_FULL_GROUP_BY，那么 MySQL 对标准 SQL 使用 GROUP BY 的扩展将允许选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列，即使这些列在功能上并不依赖于 GROUP BY 列。这将导致 MySQL 接受前面的查询。在这种情况下，服务端可以自由地从每个组中选择任何值，所以除非它们是相同的，否则所选择的值是不确定的，这可能不是您想要的。此外，不能通过添加 ORDER BY 子句来影响从每个组中选择值。结果集排序发生在选择值之后，ORDER BY 不会影响服务端选择每个组中的哪个值。

通过使用 ANY_VALUE() 引用非聚合列，可以在不禁用 ONLY_FULL_GROUP_BY 的情况下实现相同的效果。

下面的讨论演示了功能依赖，当没有功能依赖时 MySQL 产生的错误消息，以及在没有功能依赖时导致 MySQL 接受查询的方法。

当启用了 ONLY_FULL_GROUP_BY 时，此查询可能无效，因为选择列表中的非聚合 address 列没有在 GROUP BY 子句中指定：

SELECT name, address, MAX(age) FROM t GROUP BY name;
复制

如果 name 是 t 的主键或者是唯一的 NOT NULL 列，查询是有效的。在这种情况下，MySQL 识别出所选的列在功能上依赖于一个分组列。例如，如果 name 是一个主键，那么它的值就决定了 address 的值，因为每个组只有一个主键值，因此只有一行。因此，在组中 address 值的选择没有随机性，不需要拒绝查询。

如果 name 不是 t 的主键或唯一的 NOT NULL 列，则查询无效。在这种情况下，无法推断功能依赖关系，会发生错误：

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
复制

如果知道，对于给定的数据集，每个 name 值实际上唯一地决定了 address 值，那么 address 在功能上实际上依赖于 name。要告诉 MySQL 接受查询，可以使用 ANY_VALUE() 函数：

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
复制

另外一种方式，禁用 ONLY_FULL_GROUP_BY。

前面的示例非常简单。特别是，不太可能对单个主键列进行分组，因为每个组只包含一行。

如果查询有聚合函数且没有 GROUP BY 子句，则在启用 ONLY_FULL_GROUP_BY 时，选择列表、HAVING 条件或 ORDER BY 列表中不能有非聚合的列：

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by
复制

如果没有 GROUP BY，则只有一个组，为组选择哪个 name 值是不确定的。如果 MySQL 选择哪个 name 值无关紧要，这里也可使用 ANY_VALUE() ：

SELECT ANY_VALUE(name), MAX(age) FROM t;
复制

ONLY_FULL_GROUP_BY 也影响使用 DISTINCT 和 ORDER BY 的查询处理。考虑表 t，它有三个列 c1、c2 和 c3，包含以下行：

c1 c2 c3
1  2  A
3  4  B
1  2  C
复制

假设执行以下查询，期望结果按照 c3 排序：

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;
复制

为了使结果有序，必须先消除重复的部分。但要这样做，我们应该保留第一行还是第三行？这种任意的选择会影响 c3 的保留值，而保留值又会影响排序，使得排序也变得任意。为了防止这个问题，如果 ORDER BY 表达式不满足以下条件中的至少一个，那么具有 DISTINCT 和 ORDER BY 的查询将被视为无效而被拒绝：

● 选择列表中的表达式等于 1

● 表达式引用并属于查询所选表的所有列都是选择列表的元素

另一个对标准 SQL 的 MySQL 扩展允许在 HAVING 子句中引用选择列表中的别名表达式。例如，以下查询返回的 name 值在表 orders 中只出现一次：

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;
复制

MySQL 扩展允许在 HAVING 子句中使用聚合列的别名：

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;
复制

标准 SQL 只允许在 GROUP BY 子句中使用列表达式，所以以下这样的语句是无效的，因为 FLOOR(value/100) 是一个非列表达式：

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);
复制

MySQL 扩展了标准 SQL，允许在 GROUP BY 子句中使用非列表达式，并且认为前面的语句是有效的。

标准 SQL 也不允许在 GROUP BY 子句中使用别名。MySQL 扩展了标准 SQL 以允许使用别名，所以另一种写查询的方法如下：

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;
复制

别名 val 被认为是 GROUP BY 子句中的列表达式。

在 GROUP BY 子句中存在非列表达式时，MySQL 会识别该表达式与选择列表中的表达式之间的相等性。这意味着在启用了 ONLY_FULL_GROUP_BY SQL 模式时，包含 GROUP BY id、FLOOR(value/100) 的查询是有效的，因为相同的 FLOOR() 表达式出现在选择列表中。然而，MySQL 不尝试识别 GROUP BY 非列表达式的功能依赖性，因此下列查询在启用 ONLY_FULL_GROUP_BY 时无效，即使第三个选择的表达式是 GROUP BY 子句中 id 列和 FLOOR() 表达式的简单公式：

SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);
复制

一个解决方案是使用派生表：

SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM tbl_name
     GROUP BY id, FLOOR(value/100)) AS dt;
复制

官方文档：

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

mysql

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

MySQL的函数和运算符 - 聚合函数 - MySQL 处理 GROUP BY

评论

相关阅读