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

SELECT
  l,
  score_grade,
  ROUND(count(1) / max(sum_),3) as ratio
FROM
(
SELECT
  er.uid,
  er.score,
  er.exam_id,
  ui.`level` as l,
  
  count(1) over(PARTITION by `level`) as sum_,
CASE
    WHEN er.score >=90 THEN '优' 
    WHEN er.score >= 75 THEN'良' 
    WHEN er.score >= 60 THEN'中' 
    WHEN er.score < 60 THEN '差' 
        END AS score_grade 
      FROM
        exam_record er
        INNER JOIN user_info ui 
        on ui.uid = er.uid
    WHERE
  er.score IS NOT NULL
)t
GROUP BY l,score_grade
ORDER BY l desc,ratio desc