对试卷得分做min

2022-12-24 05:57| 来源: 网络整理| 查看: 265

【场景】：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

【本文地址】

公司简介

联系我们