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

WITH t_tag_level as (
    SELECT uid
            ,exam_id
            ,CASE 
                WHEN score >=90 THEN "优"
                WHEN score >=75 THEN "良"
                WHEN score >=60 THEN "中"
                WHEN score <60 THEN "差"
            END AS score_grade
            ,count(*) over(partition by level) as total
    FROM exam_record
    JOIN user_info USING(uid)
    WHERE score IS NOT NULL
)

SELECT level
        ,score_grade
        ,ROUND(COUNT(score_grade) / total, 3) AS ratio
FROM user_info
JOIN t_tag_level USING(uid)
GROUP BY level,score_grade
ORDER BY level DESC, ratio DESC

• ,count(*) over(partition by level) as total是本部分重点