请求生成按性别、年龄组、职业划分的Top5高评分游戏SQL查询

Hey Jessica, I get it—working with joins and window functions can feel tricky at first, but let's break these down one by one. All three queries follow a similar pattern: join the necessary tables, calculate average ratings (the most meaningful metric for "top rated"), then rank games within each demographic group to grab the top 5.

1. Top 5 Highest-Rated Games by User Gender #

This query links user genders to their game ratings, calculates the average rating per game per gender, then ranks games within each gender to get the top 5. We'll use ROW_NUMBER() to handle ties (swap it with RANK() if you want to include tied games with the same rank):

WITH GenderGameRatings AS (
    -- Calculate average rating per game and gender, plus total ratings for context
    SELECT
        u.gender,
        g.game_id,
        g.title,
        g.genre,
        AVG(r.rating) AS avg_rating,
        COUNT(r.rating) AS total_ratings
    FROM Users u
    JOIN Ratings r ON u.user_id = r.user_id
    JOIN Games g ON r.game_id = g.game_id
    GROUP BY u.gender, g.game_id, g.title, g.genre
    HAVING COUNT(r.rating) >= 5 -- Optional: filter out games with too few ratings for reliability
),
RankedGames AS (
    -- Rank games within each gender by average rating (highest first)
    SELECT
        gender,
        title,
        genre,
        avg_rating,
        total_ratings,
        ROW_NUMBER() OVER (PARTITION BY gender ORDER BY avg_rating DESC) AS rating_rank
    FROM GenderGameRatings
)
-- Grab only the top 5 games per gender
SELECT gender, title, genre, avg_rating, total_ratings
FROM RankedGames
WHERE rating_rank <= 5
ORDER BY gender, rating_rank;

Key notes:

• The HAVING clause is optional but helps exclude games with very few ratings (adjust the number to fit your needs).
• ROW_NUMBER() assigns a unique rank even if games have identical averages. Use RANK() if you want tied games to share the same rank (e.g., two 4.8-star games both get rank 1).

2. Top 5 Highest-Rated Games by Age Group #

First, we'll create custom age groups using a CASE statement, then follow the same ranking logic as above. Feel free to tweak the age ranges to match your needs:

WITH AgeGroupGameRatings AS (
    -- Define age groups and calculate average ratings
    SELECT
        CASE
            WHEN u.age < 18 THEN 'Under 18'
            WHEN u.age BETWEEN 18 AND 25 THEN '18-25'
            WHEN u.age BETWEEN 26 AND 35 THEN '26-35'
            WHEN u.age BETWEEN 36 AND 45 THEN '36-45'
            ELSE '45+'
        END AS age_group,
        g.game_id,
        g.title,
        g.genre,
        AVG(r.rating) AS avg_rating,
        COUNT(r.rating) AS total_ratings
    FROM Users u
    JOIN Ratings r ON u.user_id = r.user_id
    JOIN Games g ON r.game_id = g.game_id
    WHERE u.age IS NOT NULL -- Exclude users with no age data
    GROUP BY age_group, g.game_id, g.title, g.genre
    HAVING COUNT(r.rating) >= 5
),
RankedGames AS (
    -- Rank games within each age group
    SELECT
        age_group,
        title,
        genre,
        avg_rating,
        total_ratings,
        ROW_NUMBER() OVER (PARTITION BY age_group ORDER BY avg_rating DESC) AS rating_rank
    FROM AgeGroupGameRatings
)
-- Get top 5 per age group
SELECT age_group, title, genre, avg_rating, total_ratings
FROM RankedGames
WHERE rating_rank <= 5
ORDER BY age_group, rating_rank;

Key notes:

• Modify the CASE statement ranges if you want different segments (e.g., add a "55+" group).
• The WHERE u.age IS NOT NULL clause ensures we don't include users with missing age data.

3. Top 5 Highest-Rated Games by User Occupation #

This is nearly identical to the gender query—we just group by the Occupation field instead:

WITH OccupationGameRatings AS (
    -- Calculate average rating per game and occupation
    SELECT
        u.Occupation,
        g.game_id,
        g.title,
        g.genre,
        AVG(r.rating) AS avg_rating,
        COUNT(r.rating) AS total_ratings
    FROM Users u
    JOIN Ratings r ON u.user_id = r.user_id
    JOIN Games g ON r.game_id = g.game_id
    GROUP BY u.Occupation, g.game_id, g.title, g.genre
    HAVING COUNT(r.rating) >= 5
),
RankedGames AS (
    -- Rank games within each occupation
    SELECT
        Occupation,
        title,
        genre,
        avg_rating,
        total_ratings,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY avg_rating DESC) AS rating_rank
    FROM OccupationGameRatings
)
-- Grab top 5 per occupation
SELECT Occupation, title, genre, avg_rating, total_ratings
FROM RankedGames
WHERE rating_rank <= 5
ORDER BY Occupation, rating_rank;

Key notes:

• Double-check that your Occupation field has consistent values (no typos like "Student" vs "student"). If not, use LOWER(u.Occupation) in the GROUP BY and SELECT to standardize entries.

All these queries use Common Table Expressions (CTEs) to split the logic into readable chunks, which makes debugging way easier. If you're using a specific database (like MySQL vs PostgreSQL) and run into syntax quirks, just let me know—I can adjust the code to fit!

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