嵌套聚合函数 - SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8141452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Nesting Aggregate Functions - SQL
提问by steve
I want to make a SQL query which finds the catagory of awards for movies which has the highest average rating, so for a group of movies which have won a particular award, if they have a higher average rating than any other awards group of movies then it will be returned.
我想做一个 SQL 查询，查找平均评分最高的电影的奖项类别，因此对于获得特定奖项的一组电影，如果它们的平均评分高于任何其他奖项组的电影，那么它会被退回。
I tried something like this:
我试过这样的事情：
SELECT MAX(AVG(m."Rating"))
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"
but it seems that aggregate functions cannot be nested. How can I call the max function on the average ratings for each catagory?
但似乎聚合函数不能嵌套。如何在每个类别的平均评分上调用 max 函数？
回答by a_horse_with_no_name
If you are only interested in the value itself, the following should do it:
如果您只对值本身感兴趣，则应执行以下操作：
SELECT MAX(avg_rating)
FROM (
SELECT AVG(m."Rating") as avg_rating
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"
) t
Otherwise Adrian's solution is better.
否则阿德里安的解决方案更好。
回答by Adriano Carneiro
This will bring your desired result:
这将带来您想要的结果：
SELECT a."Award", AVG(m."Rating")
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"
ORDER by AVG(m."Rating") desc
LIMIT 1
This will allow you not only get the MAXvalue, but its corresponding Awardinfo
这将使您不仅可以获得MAX价值，还可以获得其相应的Award信息
回答by Jeff Stock
Did you try this?
你试过这个吗？
SELECT MAX(
SELECT AVG(m."Rating")
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"
)
回答by Lukasz Szozda
Another way is to use windowed MAX:
另一种方法是使用 windowed MAX：
SELECT MAX(AVG(m."Rating")) OVER()
FROM awards a -- proper JOIN syntax
JOIN movies m ON a."Title" = m."Title"
GROUP BY a."Award"
LIMIT 1;
