题解 | #对试卷得分做min-max归一化#

对试卷得分做min-max归一化

https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6

select 
    uid,
    exam_id,
    round(sum(max_min)/count(uid),0) as avg_new_score
from(
    select
        uid,
        exam_id,
        IF(min_x=max_x,score,(score-min_x)*100/(max_x-min_x))max_min
    from(
        #1.用户作答高难度试卷的得分情况
        select 
            uid,
            a.exam_id,
            score,
            max(score) over (partition by exam_id) as max_x,
            min(score) over (partition by exam_id) as min_x
        from
            exam_record as a
        left join 
            examination_info as b
        on a.exam_id = b.exam_id
        where 
            score is not null
            and difficulty = 'hard')as c)as d
group by uid,exam_id
order by exam_id,avg_new_score desc

这道题卡半天的原因,忘了用max(score) over (partition by exam_id) as max_x

全部评论

相关推荐

05-26 10:24
门头沟学院 Java
qq乃乃好喝到咩噗茶:其实是对的,线上面试容易被人当野怪刷了
找工作时遇到的神仙HR
点赞 评论 收藏
分享
练习生懒羊羊:开飞机把这个公司创飞吧
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务