# 求每个用户对每份试卷的std
#1、按照每门考试分类，统计每门考试的最大值，最小值，计算离差标准化
#2、筛选高难度试卷
#3、如果一个得分，就是原分数 (如果一个用户的max_score = min_score, 那么说明他的标准化离差值等于它自身)
#4、一个人可以做多份试卷，需要对用户和试卷进行group by
select uid, exam_id, round(avg(new_score), 0) as avg_new_score from 
    (select uid, exam_id, if(max = min, score, std * 100) as new_score
    from
        (select a.uid, a.exam_id, score, max(score) over(partition by exam_id) as max
        , min(score) over(partition by exam_id) as min
        ,(score - min(score) over(partition by exam_id))/(max(score) over(partition by exam_id) - min(score) over(partition by exam_id))
        as std from
        exam_record a left join examination_info b
        on a.exam_id = b.exam_id
        where difficulty = 'hard' and score is not null)t1
    )t2
group by uid, exam_id
order by exam_id asc, avg_new_score desc