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

-------------------完整代码--------------------
select level, score_grade, round(defen_num/(sum(defen_num)over(partition by level)),3) as ratio
from(
    select level, score_grade, count(*) as defen_num
    from (
        select uid, case
        when score>=90 then '优'
        when score<90 and score>=75 then '良'
        when score<75 and score>=60 then '中'
        when score<60 and score is not null then '差'
        end as score_grade
        from exam_record
        where score is not null
    ) k1
    join user_info ui
    on k1.uid = ui.uid
    group by level, score_grade
) k2
order by level desc, ratio desc;

具体思路是：

（1）先查询各条已完成的试卷作答记录的用户id和等级情况（其他字段不需要用于计算，就不要查询）

（2）统计不同用户等级的人在完成过的试卷中各得分等级的记录数目

（3）最后使用窗口函数基于（2）表统计不同用户等级的人在完成过的试卷中各得分等级占比

# 查询各条已完成的试卷作答记录的用户id和等级情况
select uid, case
when score>=90 then '优'
when score<90 and score>=75 then '良'
when score<75 and score>=60 then '中'
when score<60 and score is not null then '差'
end as score_grade
from exam_record
where score is not null
# 统计不同用户等级的人在完成过的试卷中各得分等级的记录数目
select level, score_grade, count(*) as defen_num
from (
    select uid, case
    when score>=90 then '优'
    when score<90 and score>=75 then '良'
    when score<75 and score>=60 then '中'
    when score<60 and score is not null then '差'
    end as score_grade
) k1
join user_info ui
on k1.uid = ui.uid
group by level, score_grade
# 统计不同用户等级的人在完成过的试卷中各得分等级占比（使用窗口函数）
select level, score_grade, round(defen_num/(sum(defen_num)over(partition by level)),3) as ratio
from(
    select level, score_grade, count(*) as defen_num
    from (
        select uid, case
        when score>=90 then '优'
        when score<90 and score>=75 then '良'
        when score<75 and score>=60 then '中'
        when score<60 and score is not null then '差'
        end as score_grade
        from exam_record
        where score is not null
    ) k1
    join user_info ui
    on k1.uid = ui.uid
    group by level, score_grade
) k2
order by level desc, ratio desc;