MySQL 聚合函数（三）MySQL对GROUP BY的处理

　　原文来自MySQL 5.7 官方手册：12.20.3 MySQL Handling of GROUP BY

 

　　SQL-92和更早版本不允许SELECT列表，HAVING条件或ORDER BY列表引用未在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-1999以及更高版本允许将这种查询作为一个可选项，前提是这些列在功能上依赖GROUP BY列（if they are functionally dependent on GROUP BY columns）——如果name和custid之间存在这种关系，则查询是合法的，例如custid是customer的一个主键。

　　MySQL 5.7.5及更高版本实现了对功能依赖的检测。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下是这样），MySQL会拒绝在Select列别、Having条件或者ORDER BY列表中有引用既未在GROUP BY子句中命名也未在功能上依赖于它们的非聚合列。

　　在5.7.5之前，MySQL不检测功能依赖性，默认情况下不启用ONLY_FULL_GROUP_BY。（难怪，我的是5.7.21，默认不开启。）

　　那当不启用ONLY_FULL_GROUP_BY时，MySQL就不得不接受前面这种查询。在这种情况下，服务器可以自由选择每个组中的任何值，因此除非它们相同，否则所选的值是不确定的，这可能不是您想要的。

　　此外，添加ORDER BY子句不会影响每个组中值的选择。结果集的排序发生在值被选择之后，所以ORDER BY并不会影响服务器如何选择每个组中的值。

　　当你知道，由于数据的某些属性，每个未在GROUP BY中命名的非聚合列中的所有值对于每个组都是相同的。此时禁止ONLY_FULL_GROUP_BY可能是有用的。

 

　　以下的讨论展示功能性依赖、以及当功能性依赖缺失时MySQL产生的错误信息，以及让MySQL在功能性依赖缺失时接受查询的方式。

　　在ONLY_FULL_GROUP_BY模式下，下面的查询可能是非法的：

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

　　但，如果name是t的一个主键，又或者name是一个unique、NOT NULL字段，这个查询会变成合法的。在这种情况下，MySQL会识别出查询列address功能性依赖与group列。例如，若name是一个主键，则其值确定address的值，因为每个组只有一个主键值，因此只有一行。因此，MySQL对组中address值的选择并不会有随机性，也不需要拒绝查询。

　　反过来，如果name是并不是t的一个主键，又或者name也不是一个unique、NOT NULL字段，这个查询就是非法的了，因为在这种情况下，MySQL不能推断出功能依赖性并发生错误：

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

　　那如果非要MySQL接受这个查询，就可以使用ANY_VALUE()函数：

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

　　当然，也可以放大招，禁止ONLY_FULL_GROUP_BY模式。

　　然而，这里的例子非常简单。 特别是，我们不太可能在单个主键列上进行分组，因为每个组只包含一行。其它对于在更复杂的查询中演示“功能性依赖”的示例，参考12.20.4。

 

　　如果一个select查询中包含了聚合函数，却没有GROUP BY子句。那么在ONLY_FULL_GROUP_BY模式下，它不能在select子句的列表中、HAVING条件中、ORDER BY列表中包含非聚合列。如下所示：

/*sql_mode=ONLY_FULL_GROUP_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;

 

　　在MySQL 5.7.5及更高版本中，ONLY_FULL_GROUP_BY也会影响使用了DISTINCT和ORDER BY的查询。

　　假设具有三列c1，c2和c3的表t，其中包含以下行：

/*

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

*/

　　假设我们执行以下查询，期望结果按c3列进行排序：

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

　　为了对结果排序，必须先删除重复项。但是要这样做，我们应该保留第一行还是第三行？这种任意选择会影响c3的保留值，而反过来c3的保留值又会影响排序，使得排序也任意了。

　　为了防止这个问题，如果任何ORDER BY表达式不满足以下条件中的至少一个，则有DISTINCT和ORDER BY的查询将被拒绝为无效：

• 表达式与select列表中的某个相等；
• 所有被该表达式引用、并且属于查询所选表的列，都是select列表中国的元素

 

　　MySQL相对于标准SQL的另一个扩展是：允许在Having子句中引用在SELECT从句中命名的别名。

　　例如，以下查询返回name值出现一次的行：

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

　　但是MySQL扩展后可以如下使用：

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

NOTE：在MySQL 5.7.5之前，启用ONLY_FULL_GROUP_BY会禁用此扩展，因此需要使用非别名表达式来编写HAVING子句。

　　按前面的我的笔记，Having子句是在Select子句前被执行的，看起来似乎是错的？试验了一下，在我的版本（5.7.21）中，这样做没问题（猜想一下，和编译顺序相关？）:

select SID,count(SId) as n from sc group by SId having n=3;

/*

+------+---+
| SID  | n |
+------+---+
| 01   | 3 |
| 02   | 3 |
| 03   | 3 |
| 04   | 3 |
+------+---+

*/

 

　　还可以提一下，标准SQL在GROUP BY子句中仅允许有列表达式（column expressions），因此诸如此类的语句无效，因为FLOOR（value / 100）是非列表达式（ noncolumn expression）：

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

　　而MySQL对此进行了扩展，上述语句有效。

　　标准SQL也不允许GROUP BY子句中出现别名，MySQL则允许。所以上述查询也可以更改为：

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

　　这个val被视为列表达式。

 

　　当GROUP BY中出现非列表达式时，MySQL会识别该表达式与Select子句列表中的表达式之间的相等性。这意味着启用了ONLY_FULL_GROUP_BY SQL模式后，包含GROUP BY id，FLOOR(value/100)的查询是有效的，因为Select列表中出现了相同的FLOOR()表达式。

　　但是，MySQL不会尝试识别GROUP BY非列表达式的功能依赖（functional dependence），因此以下查询在启用ONLY_FULL_GROUP_BY时无效，即使Select列表中的第三个表达式是一个关于id列的简单公式：id与GROUP BY中的FLOOR()相加。（即id+FLOOR(value/100)与GROUP BY的列不存在功能依赖）

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;