SQL41 各用户等级的不同得分表现占比

题目主要信息：

• 将试卷得分按分界点 [90, 75, 60] 分为优良中差四个得分等级（分界点划分到左区间）
• 统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数）
• 未完成过试卷的用户无需输出

问题拆解：

• 通过 CASE 语句将 exam_record 中的 score 映射为优良中差四个得分等级，并保留 uid, level 信息。CASE 语句具有两种格式：简单 CASE 函数和 CASE 搜索函数。此处使用 CASE 搜索函数，从低到高筛选出符合要求的等级。知识点：CASE
  • CASE 搜索函数语法为 CASE WHEN {stat_1} THEN {res_1} WHEN {stat_2} THEN {res_2} ... ELSE {default} END
• 使用 COUNT OVER 语句统计出每个 level 完成试卷的总数。首先需要通过 WHERE 语句筛选出 submit_time 非空的数据，在统计试卷总数时需要用 PARTITION BY 指定聚合口径为 level。知识点：COUNT, OVER, PARTITION
• 以 WITH AS 语句将上述数据存储为临时表供接下来使用
• 对上述数据基于 level 和 分数等级 进行聚合统计，为了获取每个等级占比，需要用 COUNT(1) 统计出 level x grade 的出现次数，并对之前统计出的 level 维度试卷总数做除法。知识点：COUNT, GROUP BY
• 用 ROUND 函数对每个用户等级回答的试卷中，各得分等级的占比取三位小数，语法为 ROUND(val, 3)，结果按照 level 降序（ORDER BY level DESC） 得分等级占比 降序 输出。知识点：ROUND, ORDER BY, DESC

代码：

WITH grade AS (
    SELECT user_info.uid, level, CASE
            WHEN exam_record.score < 60 THEN '差'
            WHEN exam_record.score < 75 THEN '中'
            WHEN exam_record.score < 90 THEN '良'
            ELSE '优'
        END AS score_grade,
        COUNT(1) OVER(PARTITION BY level) AS lvl_cnt
    FROM exam_record
    LEFT JOIN user_info
    ON exam_record.uid = user_info.uid
    WHERE exam_record.submit_time IS NOT NULL
)
SELECT
    level,
    score_grade,
    ROUND(COUNT(1) / AVG(lvl_cnt), 3)
FROM grade
GROUP BY level, score_grade
ORDER BY level DESC, COUNT(1) / AVG(lvl_cnt) DESC