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

select level
        ,score_grade
        ,round(count(*)/grade_cnt,3) AS ratio
from(
    select level
            ,count(*) over(partition by level) AS grade_cnt
            ,case when score >= 90 then '优' when score>=75 and score<90 then '良' when score >=60 and score<75 then '中' else '差' end AS score_grade
    from exam_record er
    inner join user_info ui on er.uid = ui.uid
    where score is not null
)t
group by level
        ,score_grade
order by level desc
        ,ratio desc

1、窗口函数count(*) over(partition by level)可以计算每个等级的用户数。