时间戳

首页 > 解决方案 > 如何在 MySQL 中找到更好电影评分的平均值

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

问题描述

我有一张桌子 

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 中得到这个输出吗

标签： mysqlsqldatabase

解决方案

  • 我们可以使用相关子查询来计算同一年“更好的电影”的平均评分。
  • 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 小提琴： http ://sqlfiddle.com/#!9/4960ea/3

推荐阅读