题解 | #对试卷得分做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-23 19:02
吉林大学 Java
点赞 评论 收藏
分享
07-07 12:47
门头沟学院 Java
码农索隆:竟然还真有卡体检报告的
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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