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

1. count(A.score) scoreCnt 用来计算 每个层级下分数等级的数量，sum(count(A.score)) over(partition BY A.level) 窗口函数统计每个层级下的分数个数。


 select A.level ,
        A.score_grade,
        #count(A.score) scoreCnt ,
        #sum(count(A.score)) over(partition by A.level) levelCnt ,
        round(count(A.score)/(sum(count(A.score)) over(partition by A.level)),3) ratio 
   from (
    select UI.level,
           ER.uid,
           ER.exam_id,
           ER.score,
           (case when ER.score >= 90 then 
              '优' 
              when score >= 75 and score < 90 then 
              '良' 
              when score >= 60 and score < 75 then 
              '中' 
              when score < 60 then 
              '差'  end ) score_grade 
      from exam_record ER join user_info UI using(uid) 
     where ER.score is not null 
       ) A 
       group by A.level,A.score_grade 
       order by level desc, ratio desc;