聚合函数在ORDER BY子句中可以做什么？

Lets say I have a plant table:

可以说我有一个植物表:

id fruit
1  banana
2  apple
3  orange

I can do these

我可以做到这些

SELECT * FROM plant ORDER BY id;
SELECT * FROM plant ORDER BY fruit DESC;

which does the obvious thing.

这是显而易见的事情。

But I was bitten by this, what does this do?

但我被这个咬了,这有什么作用?

SELECT * FROM plant ORDER BY SUM(id);
SELECT * FROM plant ORDER BY COUNT(fruit);
SELECT * FROM plant ORDER BY COUNT(*);
SELECT * FROM plant ORDER BY SUM(1) DESC;

All these return just the first row (which is with id = 1).

所有这些只返回第一行(id = 1)。

What's happening underhood?

发动机罩下发生了什么?

What are the scenarios where aggregate function will come in handy in ORDER BY?

在ORDER BY中聚合函数会派上用场的情况是什么?

3 个解决方案

#1

Your results are more clear if you actually select the aggregate values instead of columns from the table:

如果您实际选择聚合值而不是表中的列,则结果会更清晰:

SELECT SUM(id) FROM plant ORDER BY SUM(id)

This will return the sum of all id's. This is of course a useless example because the aggregation will always create only one row, hence no need for ordering. The reason you get a row qith columns in your query is because MySQL picks one row, not at random but not deterministic either. It just so happens that it is the first column in the table in your case, but others may get another row depending on storage engine, primary keys and so on. Aggregation only in the ORDER BY clause is thus not very useful.

这将返回所有id的总和。这当然是一个无用的例子,因为聚合总是只创建一行,因此不需要排序。你在查询中获得行qith列的原因是因为MySQL选择了一行,而不是随机但不是确定性的。它只是碰巧它是你的情况下表中的第一列,但其他人可能会得到另一行,具体取决于存储引擎,主键等。因此,仅在ORDER BY子句中的聚合不是非常有用。

What you usually want to do is grouping by a certain field and then order the result set in some way:

您通常要做的是按某个字段进行分组,然后以某种方式对结果集进行排序:

SELECT fruit, COUNT(*)
FROM plant
GROUP BY fruit
ORDER BY COUNT(*)

Now that's a more interesting query! This will give you one row for each fruit together with the total count for that fruit. Try adding some more apples and the ordering will actually start making sense:

现在这是一个更有趣的查询!这将为每个水果提供一行,以及该水果的总数。尝试添加更多苹果,订单实际上会开始有意义:

Complete table:

+----+--------+
| id | fruit  |
+----+--------+
|  1 | banana |
|  2 | apple  |
|  3 | orange |
|  4 | apple  |
|  5 | apple  |
|  6 | banana |
+----+--------+

The query above:

上面的查询:

+--------+----------+
| fruit  | COUNT(*) |
+--------+----------+
| orange |        1 |
| banana |        2 |
| apple  |        3 |
+--------+----------+

#2

All these queries will all give you a syntax error on any SQL platform that complies with SQL standards.

所有这些查询都会在符合SQL标准的任何SQL平台上给出语法错误。

SELECT * FROM plant ORDER BY SUM(id);
SELECT * FROM plant ORDER BY COUNT(fruit);
SELECT * FROM plant ORDER BY COUNT(*);
SELECT * FROM plant ORDER BY SUM(1) DESC;

On PostgreSQL, for example, all those queries will raise the same error.

例如,在PostgreSQL上,所有这些查询都会引发相同的错误。

ERROR: column "plant.id" must appear in the GROUP BY clause or be used in an aggregate function

错误:列“plant.id”必须出现在GROUP BY子句中或用于聚合函数

That means you're using a domain aggregate function without using GROUP BY. SQL Server and Oracle return similar error messages.

这意味着您在不使用GROUP BY的情况下使用域聚合函数。 SQL Server和Oracle返回类似的错误消息。

MySQL's GROUP BY is known to be broken in several respects, at least as far as standard behavior is concerned. But the queries you posted were a new broken behavior to me, so +1 for that.

众所周知,MySQL的GROUP BY在几个方面都被打破,至少就标准行为而言。但是你发布的查询对我来说是一个新的破坏行为,所以+1就是这样。

Instead of trying to understand what it's doing under the hood, you're probably better off learning to write standard GROUP BY queries. MySQL will process standard GROUP BY statements correctly, as far as I know.

你可能最好不去学习编写标准的GROUP BY查询,而不是试图理解它在幕后做什么。据我所知,MySQL将正确处理标准的GROUP BY语句。

Earlier versions of MySQL docs warned you about GROUP BY and hidden columns. (I don't have a reference, but this text is cited all over the place.)

早期版本的MySQL文档警告您有关GROUP BY和隐藏列的信息。 (我没有参考文献,但是这个文本被引用到了所有地方。)

Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

如果从GROUP BY部分省略的列在组中不是常量,请不要使用此功能。服务器可以*地从组中返回任何值,因此除非所有值都相同,否则结果是不确定的。

More recent versions are a little different.

更新的版本有点不同。

You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

您可以通过避免不必要的列排序和分组来使用此功能来获得更好的性能。但是,当GROUP BY中未命名的每个非聚合列中的所有值对于每个组都相同时,这非常有用。服务器可以*选择每个组中的任何值,因此除非它们相同,否则所选的值是不确定的。

Personally, I don't consider indeterminate a feature in SQL.

就个人而言,我不认为SQL中的功能不确定。

#3

When you use an aggregate like that, the query gets an implicit group by where the entire result is a single group.

当您使用这样的聚合时,查询将获得一个隐式组,其中整个结果是单个组。
Using an aggregate in order by is only useful if you also have a group by, so that you can have more than one row in the result.

按顺序使用聚合仅在您具有分组依据时才有用,这样您可以在结果中包含多行。

#1