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

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

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

with cte_get_deviation as (
    select uid,
    exam_id,
    score,
    if(max_score=min_score,score,(score-min_score)*100/(max_score-min_score)) as max_min
	# 计算归一化的分数时需要注意最大分数差为0或者只有一个分数的情况,使用原分数
    from (
        select uid,
        exam_id,
        score,
        min(score) over(partition by exam_id) min_score,
        max(score) over(partition by exam_id) max_score
		# 根据试卷分类获取最大分数和最小分数
        from examination_info
        left join exam_record using(exam_id)
        where difficulty = 'hard' and submit_time is not null
    ) t1
) 

# 获取每份试卷最大和最小分数
select uid,exam_id,
round(sum(max_min)/count(score),0) as avg_new_score
from cte_get_deviation
group by uid,exam_id
order by exam_id,avg_new_score desc

全部评论

相关推荐

07-18 14:55
门头沟学院 Java
点赞 评论 收藏
分享
06-11 17:39
门头沟学院 Java
小呆呆的大鼻涕:卧槽,用户彻底怒了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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