多列分组下左连接缺失数据补全及用户答题统计实现

要补全缺失数据并实现正确统计，核心是先生成所有用户与所有测试-问题的完整组合，再关联用户实际回答，最后按规则统计对错。以下是具体步骤和SQL实现：

步骤说明 #

1. 生成全量组合：先提取所有用户、所有测试下的所有问题，通过交叉连接得到每个用户需要覆盖的所有测试-问题记录（包括未回答的部分）；
2. 关联实际回答：将全量组合与原user_answers左连接，保留所有记录，空值表示用户未回答该问题；
3. 判断问题对错：对每个用户-测试-问题，判断是否答对（有至少一个正确答案则算对，未回答或全错则算错）；
4. 分组统计结果：按user_id和test_id分组，统计正确、错误的问题数量。

完整SQL代码 #

WITH all_user_test_questions AS (
    -- 生成所有用户 + 所有测试-问题的全组合
    SELECT 
        u.user_id,
        t.test_id,
        t.question_id
    FROM (SELECT DISTINCT user_id FROM user_answers) u
    CROSS JOIN (SELECT DISTINCT test_id, question_id FROM test_data) t
),
user_question_answers AS (
    -- 关联用户实际回答，保留全量组合
    SELECT 
        autq.user_id,
        autq.test_id,
        autq.question_id,
        ua.given_answer_id
    FROM all_user_test_questions autq
    LEFT JOIN user_answers ua 
        ON autq.user_id = ua.user_id 
        AND autq.test_id = ua.test_id 
        AND autq.question_id = ua.question_id
),
question_correct_status AS (
    -- 判断每个问题是否答对（去重同一个问题的多份回答）
    SELECT 
        uqa.user_id,
        uqa.test_id,
        uqa.question_id,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM test_data td 
                WHERE td.test_id = uqa.test_id 
                  AND td.question_id = uqa.question_id 
                  AND td.answer_id = uqa.given_answer_id 
                  AND td.valid = true
            ) THEN 1
            ELSE 0
        END AS is_correct
    FROM user_question_answers uqa
    GROUP BY uqa.user_id, uqa.test_id, uqa.question_id
)
-- 最终统计结果
SELECT 
    user_id,
    test_id,
    SUM(is_correct) AS question_correct_count,
    COUNT(*) - SUM(is_correct) AS question_wrong_count
FROM question_correct_status
GROUP BY user_id, test_id
ORDER BY user_id, test_id;

关键逻辑说明 #

• 交叉连接CROSS JOIN确保每个用户都覆盖到所有测试的所有问题，补全了未回答的缺失数据；
• 用EXISTS判断用户的回答中是否存在正确答案，避免同一问题多份回答重复统计；
• 未回答的问题因given_answer_id为空，EXISTS返回false，直接计入错误统计，符合需求。

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