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

题目主要信息：

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

问题拆分：

• 首先筛选出用户ID、相应的完成的考试的ID及其分数、用户等级、分数等级等信息：
  • 上述信息分布在表 user_info和表exam_record中，因此要依靠uid将两张表连接在一起。知识点：join...on...
  • 筛选出题目完成的所有试卷，即有得分的试卷。知识点：where
  • 用户ID、对应的试卷ID、分数、用户等级可由表中直接获取。
  • 设置case根据得分的区间，赋予score_grade优良中差四种字符串。case when score >= 90 then '优' when score >= 75 then '良'when score >= 60 then '中' else '差' end as score_grade 知识点：case when...then...end
  • 统计每个用户等级的总人数。count(*) over(partition by level) as total count()表示统计总数，over表示从此个开始，partition表示以用户等级为划分。 知识点：count、over()、partition by
  • 筛选出的结果记为user_grade_table
• 从user_grade_table表中筛选出用户等级、成绩等级、相应成绩等级在这个用户等级中的百分比：
  • 要选出每个成绩等级在用户等级中的百分比，我们要以level和score_grade分组。知识点：group by
  • 统计每个组的用户数除以该用户等级的总人数得到比值，取三位小数。round(count(uid) / total, 3) as ratio 知识点：round()、count()
• 按照先用户等级后比值的降序输出。order by desc

代码：

select level, score_grade, 
       round(count(uid) / total, 3) as ratio
from (
    select u_i.uid as uid,
           exam_id, score, level,
           case when score >= 90 then '优'
                when score >= 75 then '良'
                when score >= 60 then '中'
                else '差' end as score_grade,
           count(*) over(partition by level) as total
    from user_info u_i join exam_record e_r
    on u_i.uid = e_r.uid
    where score is not null 
) user_grade_table
group by level, score_grade
order by level desc, ratio desc