MySQL中按类别计算用户平均得分排名的实现方法咨询

Hey there! Let's break down how to solve this ranking problem—you're looking to get per-category rankings for unique users, where each user's score is the average of their correct answers across all their attempts. Here's a straightforward approach using SQL window functions, which avoids messy temp tables (though we can use a CTE to keep things clean):

Step 1: Calculate User Averages Per Category #

First, we'll group the data by username and category to compute each user's average correct answers for every category they've attempted. This ensures we only have one row per user-category pair.

Step 2: Apply Per-Category Ranking #

Next, we'll use a window function to rank users within each category, ordered by their average score (highest to lowest).

Full SQL Query Using CTE #

WITH user_category_averages AS (
    SELECT
        username,
        category,
        -- Replace `correct_answers` with your actual column name for correct answers per attempt
        AVG(correct_answers) AS average_correct
    FROM your_quiz_table_name  -- Replace with your actual table name
    GROUP BY username, category
)
SELECT
    username,
    category,
    ROUND(average_correct, 2) AS average_correct,  -- Optional: Round for readability
    -- Use RANK() for gaps in ranking, DENSE_RANK() for no gaps
    RANK() OVER (
        PARTITION BY category 
        ORDER BY average_correct DESC
    ) AS user_rank
FROM user_category_averages
ORDER BY category, user_rank;

If You Prefer Subqueries Instead of CTEs #

If your SQL dialect doesn't support CTEs (unlikely these days, but just in case), you can use a subquery:

SELECT
    username,
    category,
    ROUND(average_correct, 2) AS average_correct,
    RANK() OVER (
        PARTITION BY category 
        ORDER BY average_correct DESC
    ) AS user_rank
FROM (
    SELECT
        username,
        category,
        AVG(correct_answers) AS average_correct
    FROM your_quiz_table_name
    GROUP BY username, category
) AS user_avg_subquery
ORDER BY category, user_rank;

Key Notes #

• RANK() vs DENSE_RANK(): Use RANK() if you want gaps in ranking when users have the same score (e.g., two users with average 90 get rank 1, next user gets rank 3). Use DENSE_RANK() if you want consecutive ranks (same two users get rank 1, next gets rank 2).
• Column Names: Make sure to replace your_quiz_table_name and correct_answers with your actual table and column names from your database.
• Handling Single Attempts: If a user only has one attempt in a category, the average will just be that single attempt's correct answer count—this works automatically with AVG().

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