SELECT n.name as actress_name , r.total_votes , COUNT(r.movie_id) as movie_count, r.avg_rating as actress_avg_rating,
RANK() OVER(
ORDER BY
COUNT(r.movie_id) DESC
) actress_rank
FROM names as n
INNER JOIN role_mapping as rm
ON n.id = rm.name_id
INNER JOIN movie as m
ON rm.name_id = m.id
INNER JOIN ratings as r
ON m.id = r.movie_id
INNER JOIN genre as g
ON g.movie_id = r.movie_id
WHERE r.avg_rating > 8 AND g.genre = 'Drama' AND rm.category ='actress'
GROUP BY actress_name
LIMIT 3;
您只需要在分析函数中进行分区，例如
SELECT *
FROM
(
SELECT n.name AS actress_name , r.total_votes ,
COUNT(r.movie_id) AS movie_count, r.avg_rating as actress_avg_rating,
RANK() OVER( PARTITION BY n.name
ORDER BY COUNT(r.movie_id) DESC
) AS actress_rank
FROM names AS n
JOIN role_mapping AS rm
ON n.id = rm.name_id
JOIN movie AS m
ON rm.name_id = m.id
JOIN ratings AS r
ON m.id = r.movie_id
JOIN genre AS g
ON g.movie_id = r.movie_id
WHERE r.avg_rating > 8 AND g.genre = 'Drama' AND rm.category ='actress'
GROUP BY actress_name
) q
WHERE actress_rank <= 3
