时间戳

首页 > 解决方案 > 使用 IMDB 数据集查找使用 Yash Chopra 制作的电影比任何其他导演都多的所有演员（重新发布）

sql - 使用 IMDB 数据集查找使用 Yash Chopra 制作的电影比任何其他导演都多的所有演员（重新发布）

问题描述

第一个查询 q1 给出了想要的结果。但是查询 q2 为一对演员和导演提供了过多的电影数量。

SELECT * FROM 
   (SELECT  pc.PID AS actorID, pc.Name AS Actor, pd.PID AS directorID,pd.Name AS 
    Director,COUNT(DISTINCT m.MID) count_movie FROM Movie m 
    JOIN 
    M_Cast mc ON m.MID = Trim(mc.MID) 
   JOIN  
   M_Director md ON  m.MID = md.MID
   JOIN  
   Person pc ON Trim(mc.PID) = pc.PID
   JOIN 
   Person pd ON md.PID = pd.PID
   WHERE pd.Name LIKE '%Yash%' GROUP BY pc.Name) AS q1

   JOIN

   (SELECT  pc.PID AS actorID,pc.Name Actor,pd.PID AS directorID,pd.Name AS Director, COUNT(DISTINCT 
   m.MID) count_movie FROM Movie m 
   JOIN 
   M_Cast mc ON  m.MID = TRIM(mc.MID ) 
   JOIN  
   M_Director md ON m.MID = md.MID 
   JOIN 
   Person pc ON TRIM(mc.PID) = pc.PID 
   JOIN 
   Person pd  ON md.PID = pd.PID
   WHERE pd.Name NOT LIKE '%Yash%' GROUP BY pc.PID) AS q2
   ON q1.Actor = q2.Actor ORDER BY q1.count_movie DESC

        actorID     Actor          directorID   Director      count_movie  actorID      Actor      directorID    Director       count_movie
   0    nm0707271   Jagdish Raj    nm0007181    Yash Chopra   11           nm0707271    Jagdish Raj nm0474806   Gulshan Kumar   98
   1    nm0471443   Manmohan       nm0007181    Yash Chopra   10           nm0471443    Manmohan    nm0695153   T. Prakash Rao  39
   2    nm0407002   Iftekhar       nm0007181    Yash Chopra    9           nm0407002    Iftekhar    nm1065099   C.P. Dixit      93
   3    nm0534501   Madan Puri     nm0007181    Yash Chopra    8           nm0534501    Madan Puri  nm0619050   Hiren Nag       94

最右边的 movie_count 似乎高得不合理。如果必须匹配答案，则第一行应小于 11。

在这里，我没有使用上述 WHERE 条件来显示我的查询错误的地方。即使我使用 Where 条件

where q1.count_movie > q2.count_movie

我得到的结果是

        Name            count
    0   Sanjeev Kumar   3
    1   Sanjeev Kumar   3
    2   Sanjeev Kumar   3
    3   Sanjeev Kumar   3
    4   Ashok Kumar     2

下面的输出是正确的结果。它将提供 245 行。我只显示 6 行。

                Name            count
       0        Jagdish Raj     11
       1        Manmohan        10
       2        Iftekhar        9
       3        Shashi Kapoor   7
       4        Rakhee Gulzar   5
       5        Waheeda Rehman  5

给出的表格是

我是 sql 新手，在调试代码时遇到了很多麻烦。请帮助我理解逻辑。

标签： sqlsqliteimdb

解决方案

查询的第二部分的问题是，您汇总了所有其他导演的所有电影，而不是计算每个导演的电影。

我看到了两种完成任务的方法：

  1. 每个演员将 Yash Chopra 的电影数量与所有其他人进行比较。（这将需要 WHERE yash_chopra_count > ANY (...)，但 SQLite 不包含ANY关键字。）或者将 Yash Chopra 的电影数量与其他人的最大电影数量进行比较。（这在 SQLite 中有效。）
  2. 按电影数量对每位演员的导演进行排名，看看排名最高的演员是否是 Yash Chopra。（应该用 来完成ROW_COUNT）。

在开始之前，让我们检查一下是否一切正确：数据模型允许每部电影有多个导演。但我们仍然可以数数。如果一个演员与 Yash Chopra 制作了三部电影，而与 IS Johar 合作制作了三部电影，我们不在乎这是否是六部独立的电影，或者可能只是 Yash Chopra 是 IS Johar 的助理导演的三部电影。在我的演员示例中，我们看到另一位导演的电影数量至少与 Yash Chopra 一样多，因此我们不想选择该演员。因此，m:n 关系没有障碍。这没什么区别。

现在我们确保我们可以简单地计算每个演员和导演的电影，让我们看看这两种方法：

将 Yash Chopra 计数与其他最大计数进行比较

如前所述，我们必须将其他导向器聚合两次（一次用于每个导向器的计数，一次用于最大计数），因为 SQLite 缺少ANY运算符。不过，当我们使用 CTE 来提高可读性时，这没什么大不了的。

with director_actor as
(
  select
    c.pid as pid_actor,
    d.pid as pid_director,
    count(*) as movie_count
  from m_director d
  join m_cast c on c.mid = d.mid
  group by c.pid, d.pid
)
select pid_actor, movie_count
from director_actor
where pid_director = (select pid from person where name = 'Yash Chopra')
and movie_count >
(
  select coalesce(max(movie_count), 0)
  from director_actor other
  where other.pid_actor = director_actor.pid_actor
  and other.pid_director <> director_actor.pid_director
);

按电影数量对每位演员的导演排名

我们必须采取哪些步骤来让这些参与者受到质疑？

  1. 计算每个演员和导演的电影。
  2. 对每个演员的导演进行排名。拥有最多电影的导演排名第一。如果有平局，那么我们对另一位导演的排名比 Yash Chopra 更好，所以如果 Yash Chopra 的电影比其他导演多，则 Yash Chopra 只能获得第一名。
  3. 保留排名最高的导演是 Yash Chopra 的演员。

对于我会使用的排名ROW_NUMBER。

with ranked as
(
  select
    c.pid as pid_actor,
    d.pid as pid_director,
    yc.pid as pid_yash_chopra,
    count(*) as movie_count,
    row_number() over (partition by c.pid order by count(*) desc, d.pid = yc.pid) as rn
  from m_director d
  join m_cast c on c.mid = d.mid
  left join person yc on yc.pid = d.pid and name = 'Yash Chopra'
  group by c.pid, d.pid, yc.pid
)
select pid_actor, movie_count
from ranked
where rn = 1 and pid_director = pid_yash_chopra;

推荐阅读