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

Question

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

我见过其他人善意建议的解决方案，例如，

1. fetch the row with max values 。
2. 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

Answer 1

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

当您返回mID而不将其添加到GROUP BY时，MySQL会返回带有最大值(平均值)和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...