SQL中的GROUP BY子句是否是多余的？

Question

无论何时我们在SQL中使用聚合函数(MIN, MAX, AVG等)，我们必须始终对所有非聚合列进行GROUP BY，例如：

无论何时我们在SQL中使用聚合函数(MIN, MAX, AVG等)，我们必须始终对所有非聚合列进行GROUP BY，例如：

SELECT storeid, storename, SUM(revenue), COUNT(*)
FROM Sales 
GROUP BY storeid, storename

当我们在SELECT语句中使用函数或其他计算时，情况变得更加侵入式，因为这也必须复制到GROUP BY子句中。

SELECT (2 * (x + y)) / z + 1, MyFunction(x, y), SUM(z)
FROM AnotherTable
GROUP BY (2 * (x + y)) / z + 1, MyFunction(x, y)

如果我们要更改SELECT语句，我们必须记得对GROUP BY子句进行相同的更改。

那么GROUP BY子句是多余的吗？

• 如果确实如此，那么为什么SQL中还有GROUP BY子句？
• 如果不是这样，那么GROUP BY能够给我们提供什么额外的功能？

- Mike Chamberlain

---

程序员社区可能更适合询问豹子如何得到它的斑点。 - bmargulies

选择一个数据库，否则这将涉及主观因素，因为ANSI标准和实现的内容是不同的。ANSI标准的实现落后于现有功能，并且这些功能在不同的供应商之间不能保证一致性。例如：Mark Byers使用MySQL作为理由，但只有SQLite共享该功能 - DB2、Oracle、SQL Server和PostgreSQL并没有。 - OMG Ponies

在海军学院（哼哼！）多年前，海军部长下来发表演讲，并随后回答了问题。一位年轻的海军中尉站起来，提出了一个长而详细的问题，涉及到SECNAV所讨论的主题，他回答说：“好吧，年轻人，很明显你比我知道更多关于这个。你能写一份5000字的报告，明天早上交到我的桌子上吗？谢谢。”据说房间里爆发出一阵喧闹的笑声。我的观点是 - 请实现一个关系数据库，然后评论GROUP BY的有用性。谢谢。 - Bob Jarvis - Слава Україні

Answer 1

每当我们在SQL中使用聚合函数(MIN、MAX、AVG等)时，必须始终对所有非聚合列进行分组。

一般情况下并非如此。例如，MySQL不需要这样做，而SQL标准也没有规定这一点。

参见：Debunking GROUP BY myths 当我们在SELECT语句中使用函数或其他计算时，这种情况变得更加麻烦，因为这也必须复制到GROUP BY子句中。

同样，并非一般情况。MySQL（以及其他数据库）允许在GROUP BY子句中使用列别名。

SELECT (2 * (x + y)) / z + 1 AS a, MyFunction(x, y) AS b, SUM(z)
FROM AnotherTable
GROUP BY a, b

如果不是这种情况，GROUP BY 提供了什么额外的功能呢？

唯一指定分组的方法是使用 GROUP BY 子句。您不能从 SELECT 中提到的列中必然推断出它。实际上，您甚至不必选择在 GROUP BY 中提到的所有列：

SELECT MAX(col2)
FROM foo
GROUP BY col1
HAVING COUNT(*) = 2

- Mark Byers

---

标准很好，但是Oracle、SQL Server和PostgreSQL要求所有非聚合列在GROUP BY中定义--SQLite是我所知道的唯一与MySQL共享缺少GROUP BY的数据库。MySQL文档还指出，未在GROUP BY中的非聚合列的值是任意的，不能保证值始终一致。 - OMG Ponies

@OMG - 我不相信SQL Server支持在GROUP BY子句中使用列别名 - 我刚刚尝试了这个查询语句："SELECT name AS n, COUNT(*) FROM Types GROUP BY n"，但是却收到了错误信息"Invalid column name 'n'." - Mike Chamberlain

@Mikey Cee：我以为SQL Server可以，但在2005上的测试证实——不能在GROUP BY或HAVING子句中使用列别名。 - OMG Ponies

@Mikey Cee：“我不相信它支持你的说法，即“SQL标准也没有这样说”。事实上，它似乎支持相反的观点。”那么我认为你和我必须理解有所不同-我不知道是什么，也不知道谁是对的。在我看来，作者认为标准被故意更改以允许MySQL目前使用的行为方式。我简直无法想象我们如何能够理解文章的意思完全相反。我想我们将不得不同意不同的看法。 :) - Mark Byers

你是对的Mark - 我不得不再读几遍才能理解，但现在我明白了，新标准规定SELECT中的非聚合列只需要在函数上依赖于GROUP BY即可，即使它们没有这样做，查询仍然是合法的，但可能会返回不一致的结果。看来MySQL是唯一一个不试图保护你的数据库，允许你发出任意的GROUP BY查询，从而打开了被返回无意义数据的可能性。 - Mike Chamberlain

Answer 2

我可能同意你所说的，但在所有情况下都不是多余的。

考虑以下情况：

SELECT FirstName 
       + ' (' + REPLACE(Address1, ',', ' ') + ' '
       + REPLACE(Address2, ',', ' ') + ', '
       + UPPER(State) + ' '
       + 'USA)',
       COUNT(*)
FROM Profiles
GROUP BY FirstName, Address1, Address2, State

在这种情况下，我只需要相同名字和地址的档案数量。
正如您所看到的，我不必在GROUP BY语句中重复SELECT的“复杂”操作。
我认为为了允许这种“有时像这样，有时像那样”，您大多数时候都要做重复的工作。

- BeemerGuy

Answer 3

GROUP BY子句并非多余，其功能在于定义聚合函数作用的范围。虽然您认为优化器应从SELECT子句中读取以确定分组的范围，但在ORDER BY子句中可以访问列别名（MySQL除外，其中GROUP BY和HAVING子句支持列别名），这是最早的时间。目前没有任何方法支持您的期望。ANSI标准很好，但现实是供应商没有完全实施ANSI标准。这是寻找和支持，就像PostgreSQL 8.4+支持比Oracle（肯定比SQL Server）更多的分析函数一样。

MySQL和SQLite支持省略GROUP BY中的列，但根据文档，这些列值是任意的--不能保证始终返回相同的值。此外，分组的范围也不同，这可能会极大地影响结果集。还有一个问题，就是需要依赖特定于供应商的语法，同时需要将其移植到其他数据库，因为DB2、Oracle、SQL Server和PostgreSQL都不支持该功能。

但是随着分析/窗口/排名功能的出现，您可以在没有GROUP BY的情况下获得聚合功能。例如：

SELECT t.id,
       COUNT(t.column) OVER(PARTITION BY t.id) AS num,
       SUM(t.column) OVER(PARTITION BY t.id) AS sum
  FROM YOUR_TABLE t

虽然更冗长且容易出错，但是你无法定义适用于查询中所有分析函数的PARTITION BY/ORDER BY。目前... 但是分析功能不会很快取代聚合功能 - 支持始于Oracle 9i、SQL Server 2005+和PostgreSQL 8.4+。我知道DB2支持分析功能，但除此之外我不了解更多细节。

- OMG Ponies

Answer 4

1. GROUP BY 后面的主要内容是 (2 * (x + y)) / z + 1, MyFunction(x, y)，需要用于汇总求和。
2. 但是 SELECT 后面的 (2 * (x + y)) / z + 1, MyFunction(x, y) 是可选的。你想要什么结果都不会影响到 sum()。
   就像 BeemerGuy 说的，第二点并不总是和第一点一样。

- pinichi