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

WITH a AS (SELECT uid, exam_id, score
            FROM exam_record JOIN examination_info USING(exam_id)
            WHERE difficulty = 'hard'),
     b AS (SELECT exam_id, COALESCE(MAX(score),0) AS maximum, COALESCE(MIN(score),0) AS minimum
          FROM exam_record
          GROUP BY exam_id)
SELECT uid,exam_id, ROUND(AVG(new_score)) AS avg_new_score
FROM(SELECT *, 
     IF(maximum = minimum, score, ((score-minimum)/(maximum-minimum))*100) AS new_score
     FROM a LEFT JOIN b USING (exam_id)) AS d
WHERE score IS NOT NULL
GROUP BY uid, exam_id
ORDER BY exam_id ASC, avg_new_score DESC;