使用 SQL max() 查找平均评分最高的电影 [英] Find the movies with the highest average rating using SQL max()

问题描述

这是斯坦福在线数据库课程练习中的一道题.找出平均评分最高的电影.返回这些电影名称及其平均评分.使用 SQLite.

This is a question from Stanford online database course exercise. Find the movie(s) with the highest average rating. Return these movie title(s) and their average rating. Use SQLite.

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

I've seen solutions kindly suggested by others, e.g,

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

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

But what I hope to understand here is where and why my current solution present here went wrong.

电影分级表:

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，star 代表评论者评分.

我的第一个想法是得到每部电影的平均分数，使用代码如下:

My first thought is to get the average scores for each movie, using code as following:

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

---

Then I want to select the max values of the scores column and the assciated 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

推荐答案

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

You seem to use MySQL as a DBMS, which allows non-Standard syntax:

当您返回 mID 而不将其添加到 GROUP BY 时，MySQL 返回单行，其中包含最大值(平均值)和 mID 的随机值.

When you return mID without adding it to GROUP BY MySQL returns a single row with the maximum(average) and a random value for mID.

这是一个通用的标准 SQL 解决方案:

This is a generic Standard SQL solution:

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 的窗口聚合函数:

This might be very inefficient, that's why there are several proprietary syntax extensions. Most DBMSes (but not MySQL) support Standard SQL's Windowed Aggregate Functions:

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 也不支持分析函数).

As you added SQLite as DBMS, my 2nd query will not work (SQLite doesn't support Analytical Functions, too).

但是由于支持 WITH，您可以将 #1 简化为类似于 @user3580870 的查询:

But as WITH is supported you can simplify #1 to a query similar to @user3580870's:

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...

And this is still Standard SQL compliant...

这篇关于使用 SQL max() 查找平均评分最高的电影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！