mysql - 如何在 MySQL 中找到更好的电影评分的平均值

Question

我有一张 table 电影

id  release_year    category_id rating
1   2015                 1         8
2   2015                 2        8.5
3   2015                 3         9
4   2016                 2        8.2
5   2016                 1        8.4
6   2017                 2         7

我想添加一个新列“avg_better_films”来查找发行年份所有更好电影的平均评分

输出应该是

id  release_year    category_id rating  avg_better_films
1   2015                1         8          8.75
2   2015                2        8.5           9
3   2015                3         9        Not Available
4   2016                2        8.2          8.4
5   2016                1        8.4       Not Available
6   2017                2         7        Not Available

你可以看到，当电影在上映年份获得最高评分时它将显示“不可用”

你知道如何在 MySQL 中得到这个输出吗

Answer 1

• 我们可以使用 Correlated Subquery计算同年“更好的电影”的平均评分。
• 如果特定电影和年份没有更好的电影，子查询将返回 null。
• 然后我们可以使用Coalesce()用于处理当年没有找到“更好的电影”的情况。
• 感谢@Strawberry的评论，我们需要对子查询的结果做+0，让MySQL认为是数字

尝试以下操作:

SELECT
  t1.id, 
  t1.release_year, 
  t1.category_id, 
  t1.rating, 
  COALESCE(
           (
            SELECT AVG(t2.rating) 
            FROM Films AS t2
            WHERE t2.rating > t1.rating AND  -- higher rating films
                  t2.release_year = t1.release_year -- films from same year
           ) + 0, 
           'Not Available' -- handle null result of subquery
          ) AS avg_better_films
FROM Films AS t1

SQL fiddle : http://sqlfiddle.com/#!9/4960ea/3