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

法一：

SELECT uid, exam_id, ROUND(AVG(norm_score),0) avg_new_score
FROM 
(SELECT uid, exam_record.exam_id,
 IF(num=1, score, (score-min_score)/(max_score-min_score)*100) norm_score
 FROM (SELECT exam_id, MIN(score) min_score, MAX(score) max_score, COUNT(score) num FROM exam_record
       WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE difficulty='hard')
       AND score IS NOT NULL#过滤掉NULL值，COUNT(score)才能算对。而mysql中 MIN、MAX、AVG函数会忽略NULL值
       GROUP BY exam_id) t1, exam_record
 WHERE exam_record.exam_id=t1.exam_id AND score IS NOT NULL) t2 #过滤掉NULL值，不考虑没有score的
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC;

一开始的想法是如下，错在t1表那里。SELECT uid, exam_id....GROUP BY exam_id这样是没有办法通过执行的，GROUP BY exam_id后，数据最后是每个exam_id只有一行的，不能说出现有1001,9001（uid，exam_id）一行，1003,9001另一行这样的情况。但是要算COUNT,MIN,MAX又必须得用上GROUP BY。因此，做法修改为先算出num,min_score,max_score，然后把算出来的结果和exam_record连表，这样就能得到1001,9001一行，1003,9001另一行这样的情况。

SELECT uid, exam_id, ROUND(AVG(norm_score),0) avg_new_score
FROM 

(SELECT uid, exam_id, 
 IF(COUNT(score)=1, score, (score-MIN(score))/(MAX(score)-MIN(score))*100) norm_score
 FROM exam_record
 WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE difficulty='hard') AND score IS NOT NULL
 GROUP BY exam_id) t1
 
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC;

法二：这个做法是用了窗口函数。与法一差别就在于，刚刚说的，GROUP BY exam_id后算最大最小值，数据最后是每个exam_id只有一行的，不能说出现有1001,9001一行，1003,9001另一行这样的情况。但是用窗口函数的话就可以实现。

SELECT uid, exam_id, ROUND(AVG(norm_score),0) avg_new_score
FROM 
(SELECT uid, exam_id,
 IF(num=1, score, (score-min_score)/(max_score-min_score)*100) norm_score
 FROM (SELECT uid,exam_id, score, MAX(score) over (partition by exam_id) as max_score,
        MIN(score) over (partition by exam_id) as min_score,
       COUNT(score) over (partition by exam_id) as num
       FROM exam_record
       WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE difficulty='hard')
       AND score IS NOT NULL) t1) t2
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC;

下面附上t1表的结构直观看：

这就体现了窗口函数不减少原表行数的特点。