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

【场景】：不同等级

【分类】：高级条件语句、case when

分析思路

难点：

1.各用户等级的不同得分表现占比，需要分别求分子：得分等级的个数和分母：不同用户等级的个数，区别分组不同

2.统计用户分数等级，使用case when在select做判断，它会根据不同的条件生产不同的元素放在一列

（1）统计完成了的试卷对应的用户等级和分数等级

• [使用]：group by level; 未完成过试卷的用户无需输出: right join

• [条件]：score is not null

• [不同情况输出不同]：case when: >= 90\between 75 and 89\between 60 and 74<= 60

（2）统计不同用户各等级的个数

• [使用]：group by level,score_grade

（3）统计不同用户作答完成的试卷个数

• [使用]：group by level

（4）根据计算得分等级占比（结果保留3位小数）,按用户等级降序、占比降序排序

• [使用]：round(cnt_grade/cnt_level,3)；order by level desc,ratio desc

求解代码

方法一：

with子句 + 一步步拆解

with
    main as(
        #统计完成了的试卷 对应的用户等级和分数等级
        select
            uid,
            exam_id,
            score,
            level,
            case
                when score >= 90
                then '优'
                when score between 75 and 89
                then '良'
                when score between 60 and 74
                then '中'
                else '差'
            end as score_grade
        from user_info
        right join exam_record b using(uid)
        where score is not null
    )
    ,attr as(
        #统计不同用户各等级的个数
        select
            level,
            score_grade,
            count(score_grade) as cnt_grade
        from main
        group by level,score_grade
    )
    ,attr1 as(
        #统计不同用户作答完成的试卷个数
        select
            level,
            count(score_grade) as cnt_level
        from main
        group by level
    )

#各得分等级占比（结果保留3位小数）,按用户等级降序、占比降序排序
select
    level,
    score_grade,
    round(cnt_grade/cnt_level,3) as ratio
from attr
left join attr1 using(level)
order by level desc,ratio desc

方法二：

统计不同用户各等级和作答的个数都是对main表做处理，可以合为一个表。

select
    level,
    score_grade,
    round(count(score_grade)/score_cnt,3) ratio
from(
    select
        level,
        count(*) over(partition by level) score_cnt,
        (case
            when score >= 90 then '优'
            when score between 75 and 89 then '良'
            when score between 60 and 74 then '中'
            else '差'
        end) as score_grade
    from user_info
    left join exam_record using(uid)
    where score is not null         
) main
group by level,score_grade
order by level desc,ratio desc