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

select distinct level,score_grade,round(count(score)over(partition by level,score_grade )/allnum,3) as rate from (
select
    level,
    score,
    case
        when score between 60 and 74  then '中'
        when score between 75 and 89  then '良'
        when score >= 90 then '优'
        else '差'
    end as score_grade,
    count(score)over(partition by level) as allnum
from
    user_info
    join exam_record using (uid)
    where score is not null
) t1
order by level desc,rate desc

本题不难使用count()over()函数就可以解决了，一开始先根据分数划分出不同等级，然后根据level分区找出每个level的最大数量，再把partition level score_grade再分区求出各个等级的值即可，代码如上