sql - 使用SQL max()查找平均评分最高的电影

这是斯坦福大学在线数据库课程练习中的一个问题。
查找平均评分最高的电影。返回这些电影标题及其平均评分。使用SQLite。

我看过其他人提出的解决方案，例如，


fetch the row with max values。
get top entries。


但是，我希望在这里理解的是我目前在此处提出的解决方案出了错的地方和原因。



电影分级表：

rID mID stars   ratingDate
201 101 2   2011-01-22
201 101 4   2011-01-27
203 103 2   2011-01-20
203 108 4   2011-01-12
203 108 2   2011-01-30
204 101 3   2011-01-09
205 103 3   2011-01-27
205 104 2   2011-01-22
...

注意：mID代表电影ID，rID代表评论者ID，星号代表评论者排名的得分。

我首先想到的是使用以下代码获取每部电影的平均得分：

Select mID, avg(stars) AS avg_stars
From Rating
Group by mID

结果汇总表是

mID avg_stars
101 3.0
103 2.5
104 2.5
106 4.5
107 4.0
108 3.3

然后，我要选择分数列和关联的mID的最大值

Select mID, max(avg_stars) AS Max_score
From (
Select mID, avg(stars) AS avg_stars
From Rating
Group by mID) T

我期望得到：

mID Max_score
106 4.5

但是，相反，我得到：

mID Max_score
108 4.5

最佳答案

您似乎将MySQL用作DBMS，它允许使用非标准语法：

当您返回mID而不将其添加到GROUP BY时，MySQL将返回一行，其中包含maxID（平均值）和mID的随机值。

这是通用的标准SQL解决方案：

Select mID, avg(stars) AS avg_stars
From Rating
Group by mID
having avg(stars) =
 ( select max(avg_stars) -- find the max of the averages
   from
     ( Select mID, avg(stars) AS avg_stars
       From Rating
       Group by mID
     ) T
 )

这可能效率很低，这就是为什么有几个专有语法扩展的原因。大多数DBMS（但不支持MySQL）支持标准SQL的窗口聚合功能：

select *
from
 (
   Select mID, avg(stars) AS avg_stars,
      max(avg(stars)) as max_avg
   From Rating
   Group by mID
 ) T
where avg_stars = max_avg

编辑：

当您将SQLite添加为DBMS时，我的第二个查询将不起作用（SQLite也不支持分析功能）。

但是由于支持WITH，因此您可以将＃1简化为类似于@ user3580870的查询：

with cte as
 ( Select mID, avg(stars) AS avg_stars
   From Rating
   Group by mID
 )
select * from cte
where avg_stars =
 ( select max(avg_stars) -- find the max of the averages
   from cte
 );

而且这仍然符合标准SQL ...

关于sql - 使用SQL max()查找平均评分最高的电影，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34578223/