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

【场景】：min-max归一化

【分类】：聚合函数、嵌套子查询

分析思路

（1）统计每个试卷的min(score)和max(score)-min(score)

• [条件]：where difficulty = hard
• [使用]：group by

（2）统计执行min-max归一化后的数据

如果分组之后最大值等于最小值，即为某个试卷作答记录中只有一个得分

• [条件]：where score is not null

（3）最终根据uid,exam_id分组求均值得到new_score_avg，按照试卷ID升序、归一化分数降序输出

• [使用]：group by uid,exam_id； order by exam_id,new_score_avg desc

扩展：

总结了MySQL中不四舍五入取整、取小数、四舍五入取整、取小数、向下、向上取整的几种方法。 前往查看：MySQL 不四舍五入取整、取小数、四舍五入取整、取小数、向下、向上取整

求解代码

方法一

with子句

with
    main as(
        #按照试卷分组求出每个试卷的min(score)和max(score)-min(score)
        select distinct
            exam_id,
            min(score) as min_score,
            max(score)-min(score) as max_min_score
        from examination_info
        join exam_record using(exam_id)
        where difficulty = 'hard'
        group by exam_id
    ),
    main1 as(
        #统计执行min-max归一化后的数据
        select
            uid,
            exam_id,
            min_score,
            max_min_score,
            score,
            if(max_min_score=0,score,100*(score-min_score)/max_min_score) as new_score
        from main
        join exam_record using(exam_id)
        where score is not null
    )
#根据uid,exam_id分组求均值得到new_score_avg，按照试卷ID升序、归一化分数降序输出
select
    uid,
    exam_id,
    round(avg(new_score),0) as new_score_avg
from main1
group by uid,exam_id
order by exam_id,new_score_avg desc

方法二

from嵌套子查询

#根据uid,exam_id分组求均值得到new_score_avg，按照试卷ID升序、归一化分数降序输出
select
    uid,
    exam_id,
    round(avg(new_score),0) as new_score_avg
from(
    #统计执行min-max归一化后的数据
    select
        uid,
        exam_id,
        min_score,
        max_min_score,
        score,
        if(max_min_score=0,score,100*(score-min_score)/max_min_score) as new_score
    from(
        #按照试卷分组求出每个试卷的min(score)和max(score)-min(score)
        select distinct
            exam_id,
            min(score) as min_score,
            max(score)-min(score) as max_min_score
        from examination_info
        join exam_record using(exam_id)
        where difficulty = 'hard'
        group by exam_id
    ) main
    join exam_record using(exam_id)
    where score is not null
) main1
group by uid,exam_id
order by exam_id,new_score_avg desc