通过代码

select
    uid,
    exam_id,
    round(avg(newscore),0) avg_new_score
from
    (SELECT
         uid,
         exam_id,
         if(maxs != mins,(score - mins) * 100 / (maxs - mins),maxs) newscore
     FROM
         (select
              uid,
              exam_record.exam_id,
              score,
              max(score) over(
                  partition by exam_record.exam_id
                  ) maxs,
              min(score) over(
                  partition by exam_record.exam_id
                  ) mins
          from
              exam_record
                  right JOIN
              examination_info
              ON
                      exam_record.exam_id = examination_info.exam_id
          WHERE
              score is not null
            and difficulty = 'hard'
         ) mai)new
GROUP BY
    uid,exam_id
order by
    exam_id,avg_new_score DESC

上次见到归一化这个词还是在学习机器学习的时候呢，一转眼时间就过去了。时光匆匆，韶华易逝。

思路

先上要求：

将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。

这道题其实挺清晰的

1.窗口函数求最大最小

2.进行归一化

3.求均值、排序

1.窗口函数求最大最小

select
    uid,
    exam_record.exam_id,
    score,
    max(score) over(
        partition by exam_record.exam_id
        ) maxs,
    min(score) over(
        partition by exam_record.exam_id
        ) mins
from
    exam_record
right JOIN
    examination_info
ON
    exam_record.exam_id = examination_info.exam_id
WHERE
    score is not null
  and difficulty = 'hard'

试卷总信息肯定是主表，连接后查出高难度试卷，现在也不用order 窗口函数只需要分组求最大就行

2.进行归一化

SELECT
   uid,
   exam_id,
   if(maxs != mins,(score - mins) * 100 / (maxs - mins),maxs) newscore
FROM
   (1.代码 ) mai

分数要缩放到0，100，所以就 100，另外如此人就一次记录，或者多次记录分数一样，该公式就会出现分母为0的情况，所以我们就用if来进行判别

3.求均值、排序

select
    uid,
    exam_id,
    round(avg(newscore),0) avg_new_score
from
    (2.代码)new
GROUP BY
    uid,exam_id
order by
    exam_id,avg_new_score DESC

这里是题目描述出问题，描述的是保留一位小数，但实际上是不保留小数。

完成