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

select 
c.level level,
c.score_grade score_grade,
format(count(1)/c.level_cn,3) ratio
from 
(
    select
b.uid,
b.exam_id,
b.score,
a.level,
case when b.score<60 then '差'
when b.score>=60 and b.score<75 then '中'
when b.score>=75 and b.score<90 then '良'
else  '优' end score_grade,
count(level) OVER(PARTITION BY level) level_cn
from 
exam_record b
left join user_info a
on a.uid=b.uid
where b.submit_time is not null
) c
group by c.level,c.score_grade
order by level desc,ratio desc

学习了 count(a) over (partition by b)