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

Question

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

我看到了其他人善意建议的解决方案，例如

1. 获取具有最大值的行
2. 获取热门条目

但我希望在这里了解的是，我目前的解决方案在哪里以及为什么出错。

---

电影分级表：

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

---

然后我想选择分数列的最大值和关联的mIDs

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
 )

这可能是非常低效的，这就是为什么有几个专有的语法扩展。大多数数据库管理系统（但不是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。。。

Answer 2

不要使用子查询，而是尝试使用order by并限制到第一个结果：

SELECT mID, AVG(stars) AS avg_stars
FROM Rating
GROUP BY mID
ORDER BY avg_stars DESC LIMIT 1;

Answer 3

也许WITH子句可以做到这一点。当前无法访问实时数据库进行测试，但查询应该如下所示：

WITH sq AS 
  (SELECT mID, avg(stars) AS avg_stars FROM rating GROUP BY mID)
SELECT mId,avg_stars FROM sq t1 JOIN 
  (SELECT max(avg_stars) AS max_avg FROM sq) t2 
ON t1.avg_stars = t2.max_avg;

SQLite支持3.8.3版本中的WITH子句。这个代码实际上是在问题中的数据上测试的。它还使计算明显比其他解决方案更高效（尽管它们可以通过一些智能查询分析进行优化）。此外，它是最清晰的，WITH子句可以很容易地为进一步的扭曲提供路径。

Answer 4

这只是@SMA答案的扩展。

如果您想将重复记录视为单个实体，可以使用以下查询

SELECT mID,AVG(stars) 
FROM Rating
WHERE AVG(stars) IN (
            (SELECT AVG(stars)
             FROM Rating
             GROUP BY mID
             ORDER BY avg_stars DESC LIMIT 1)
            )
GROUP BY mID

上述查询可能不是优化的查询。