如何按电影类型筛选出演该类型电影最多的N位演员

To get the actors who've appeared in the most movies for each genre (including ties, as shown in your example), we can use window functions to rank actors within each genre after calculating their movie counts. Here's how to do it:

Step 1: Calculate Actor-Genre Movie Counts #

First, we'll refine your initial query to get a clean count of distinct movies per actor per genre (this avoids counting multiple roles in the same movie as separate entries):

WITH actor_genre_counts AS (
    SELECT 
        g.genre_name,
        a.actor_id,
        a.name, -- Optional but useful to see actor names
        COUNT(DISTINCT m.movie_id) AS movie_count
    FROM genre g
    INNER JOIN movie_has_genre mhg ON mhg.genre_id = g.genre_id
    INNER JOIN movie m ON mhg.movie_id = m.movie_id
    INNER JOIN role r ON m.movie_id = r.movie_id
    INNER JOIN actor a ON a.actor_id = r.actor_id
    GROUP BY g.genre_name, a.actor_id, a.name
)

Step 2: Rank Actors Within Each Genre #

Next, we use DENSE_RANK() to assign ranks to actors in each genre based on their movie count. This function gives the same rank to actors with identical counts (which matches your example where multiple actors share the top spot):

, ranked_actors AS (
    SELECT 
        genre_name,
        actor_id,
        name,
        movie_count,
        DENSE_RANK() OVER (
            PARTITION BY genre_name 
            ORDER BY movie_count DESC
        ) AS actor_rank
    FROM actor_genre_counts
)

Step 3: Filter for Top N Actors #

Finally, select only the actors with a rank ≤ your desired N (replace N with the number of top actors you want per genre):

SELECT genre_name, actor_id, name, movie_count
FROM ranked_actors
WHERE actor_rank <= N; -- Example: Replace N with 3 to get top 3 actors per genre

Example Output (Matching Your Sample) #

If N=1, the output would look like your example (including all ties for the top count):

Action 22591 7
Horror 25863 3
Horror 24867 3
Comedy 23476 2
Drama 14536 1
Drama 19634 1
Drama 17563 1

Choosing the Right Ranking Function #

• DENSE_RANK(): Includes all actors tied for the top counts (as in your example). If two actors have the same max count, both are included even if N=1.
• RANK(): Skips ranks for ties. For example, if two actors are rank 1, the next actor would be rank 3.
• ROW_NUMBER(): Assigns a unique rank to each actor, even if counts are equal (this arbitrarily picks one actor over others in case of ties, which may not be desired).

Notes #

• Using COUNT(DISTINCT m.movie_id) ensures we count each movie only once per actor, even if they have multiple roles in it. If you want to count roles instead of movies, remove the DISTINCT keyword.
• The name column is optional but makes results more readable—you can omit it if you only need actor_id.

内容的提问来源于stack exchange，提问作者stef