题解 | #各用户等级的不同得分表现占比#

# 先找到有记录的，新增一个得分等级字段
# 再按用户等级和得分等级分组，统计占比

# 暴力法，考虑所有等级情况，分别计数
WITH temp_0 AS(
SELECT uid, exam_id, score, level, 
	# 使用case when语句为不同区间赋值
    CASE WHEN score >= 90 THEN '优'
        WHEN score < 90 AND score >= 75 THEN '良'
        WHEN score < 75 AND score >= 60 THEN '中'
        ELSE '差' END score_grade,
	# 计算每个等级的用户们的得分等级为优次数
    SUM(IF(score >= 90, 1, 0)) OVER(PARTITION BY level) a_1,
	# # 计算每个等级的用户们的得分等级为良的次数
    SUM(IF(score < 90 AND score >= 75, 1, 0)) OVER(PARTITION BY level) a_2,
    SUM(IF(score < 75 AND score >= 60, 1, 0)) OVER(PARTITION BY level) a_3,
    SUM(IF(score < 60, 1, 0)) OVER(PARTITION BY level) a_4,
	# 计算每个等级用户们的作答次数
    COUNT(score) OVER(PARTITION BY level) all_t # 每个用户的答题次数
FROM user_info
RIGHT JOIN exam_record USING(uid)
# 排除无分数的作答
WHERE score IS NOT NULL
)

# 因为没有分组，所以要对所有记录去重
SELECT DISTINCT *
FROM (
SELECT level, score_grade, 
	# 将占比视作计算字段，考虑不同情况
    CASE WHEN score_grade = '优' THEN ROUND(a_1 / all_t, 3)
        WHEN score_grade = '良' THEN ROUND(a_2 / all_t, 3)
        WHEN score_grade = '中' THEN ROUND(a_3 / all_t, 3)
        ELSE ROUND(a_4 / all_t, 3) END ratio
FROM temp_0
) a
ORDER BY level DESC, ratio DESC