如何针对考试数据库重复行汇总唯一分类变量并展示考试流向

原始数据 #

1. 统计单个考生的考试次数与结果轨迹 #

通过以下SQL可以获取每个考生的考试次数、首次/最终考试结果，清晰呈现个体的考试流向：

SELECT 
    Applicant,
    Age,
    COUNT(*) AS exam_attempts,
    MAX(`Date (MM/YY)`) AS latest_exam_date,
    FIRST_VALUE(Result) OVER (PARTITION BY Applicant ORDER BY `Date (MM/YY)`) AS first_result,
    LAST_VALUE(Result) OVER (PARTITION BY Applicant ORDER BY `Date (MM/YY)` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_result
FROM exam_records
GROUP BY Applicant, Age;

执行后输出结果：

2. 汇总分类统计（匹配你的需求示例） #

基于个体轨迹数据，用CTE汇总不同考试次数的考生分布，以及各类结果流向的人数：

WITH applicant_summary AS (
    SELECT 
        Applicant,
        COUNT(*) AS exam_attempts,
        FIRST_VALUE(Result) OVER (PARTITION BY Applicant ORDER BY `Date (MM/YY)`) AS first_result,
        LAST_VALUE(Result) OVER (PARTITION BY Applicant ORDER BY `Date (MM/YY)` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_result
    FROM exam_records
    GROUP BY Applicant
)
SELECT 
    exam_attempts,
    COUNT(*) AS total_applicants,
    SUM(CASE WHEN first_result = 'Fail' AND final_result = 'Pass' THEN 1 ELSE 0 END) AS fail_to_pass,
    SUM(CASE WHEN first_result = 'Fail' AND final_result = 'Fail' THEN 1 ELSE 0 END) AS remained_fail,
    SUM(CASE WHEN first_result = 'Pass' THEN 1 ELSE 0 END) AS passed_first_try
FROM applicant_summary
GROUP BY exam_attempts
ORDER BY exam_attempts;

执行后得到你需要的汇总结果：

该结果完全对应你描述的需求：1名考生参加2次考试且最终通过；2名考生参加1次考试，其中半数通过、半数失败。

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