sql-server - SQL Server 2012 中每部电影的平均类型

Question

偶然发现这个问题，我也找不到解决方案，需要一些帮助或指出正确的方向。

这些表格将几部电影分为多个流派，例如:玩具总动员属于流派； child 、喜剧和动画。

我试图找出哪些类型最明确，哪些类型非常困惑，以至于它们往往被列在一部典型电影的其他几个类型中。

对于数据集中的每个类型，该类型的电影平均有多少种类型。例如， Action 片是 1.3 流派，而冒险片的平均流派是 2.9，因此 Action 片是一种定义更明确的流派。

下面是表格的示例，但这里是一个带有表格和其中的值的 sql 编辑器以供引用:https://blazerme.herokuapp.com/queries/new

3 个表:

类型表:

id  name
1   Action
2   Adventure
3   Animation
4   Children's
5   Comedy

genre_movies 表:

id movie_id     genre_id
1  1(Toy Story) 3(Animation)
2  1(Toy Story) 4(Children's)
3  1(Toy Story) 5(Comedy)
4  2(GoldenEye) 1(Action)
5  2(GoldenEye) 2(Adventure)
6  2(GoldenEye) 16(Thriller)

电影表:

id title             release_date
1  Toy Story (1995)  1995-01-01
2  GoldenEye (1995)  1995-01-01
3  Four Rooms (1995) 1995-01-01

到目前为止，我最大的努力是尝试对 genre_id 的计数求平均值。见下文:

SELECT 
    name, AVG(c.count)
FROM 
    (SELECT
         g.name AS name, COUNT(gm.genre_id) AS count
     FROM
         genres g 
     INNER JOIN
         genres_movies gm ON g.id = gm.genre_id
     INNER JOIN
         movies m ON m.id = gm.movie_id
     GROUP BY
         g.name) c
GROUP BY
    name

但这只是让我从 genres_movies 表中得到每种类型的计数。我似乎无法弄清楚如何将其转化为每种类型电影的平均类型数。

Answer 1

我通过首先使用我想要的主要信息构建最内层查询，然后向外工作来解决此类问题。

需要的核心信息是:

select movie_id, count(*) from genres_movies group by movie_id

完整的查询:

select G.name, Round(avg(numgenres),2) as MuddleFactor
from genres_movies GM

inner join
(select movie_id, count(*) as numGenres
 from genres_movies
 group by movie_id) MNG 
 on MNG.movie_id = GM.movie_id

inner join genres G
on GM.genre_id = G.id

group by G.name
order by MuddleFactor desc