with cte_get_deviation as ( select uid, exam_id, score, if(max_score=min_score,score,(score-min_score)*100/(max_score-min_score)) as max_min # 计算归一化的分数时需要注意最大分数差为0或者只有一个分数的情况,使用原分数 from ( select uid, exam_id, score, min(score) over(partition ...
对试卷得分做min-max归一化 https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6 select uid,exam_id,round(avg(combinednum),0) as combinednum from ( SELECT uid,exam_id,if(MIN(score)OVER(PARTITION BY exam_id) = MAX(score)OVER(PARTITION BY exam_id),score, (score-MIN(sco...
对试卷得分做min-max归一化 https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6select uid, exam_id, round(avg(new_score),0) as avg_new_score from ( select t1.uid, t1.exam_id, t1.score, t2.max_score, t2.min_score, t2.exam_count, case when exam_count = 1 ...
对试卷得分做min-max归一化 http://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6 with t as ( select uid,exam_id, if(round((score-min(score)over(partition by exam_id))/(max(score)over(partition by exam_id)-min(score)over(partition by exam_id))*100,1) is not null,...
对试卷得分做min-max归一化 https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6最高最低分是针对每个试卷来说,不要考虑不同用户的不同试卷 SELECT uid,exam_id, ROUND(AVG( CASE WHEN score_MAX = score_MIN THEN score ELSE (score-score_MIN)/(score_MAX-score_MIN)*100 END ))...
对试卷得分做min-max归一化 https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6select uid, exam_id, round(sum(max_min)/count(uid),0) as avg_new_score from( select uid, exam_id, IF(min_x=max_x,score,(score-min_x)*100/(max_x-min_x))max_min from( #1.用户作...
对试卷得分做min-max归一化 https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6 1. 通过exam_record.exam_id分组 计算每张试卷的最大分数最小分数作为基础表数据,然后通过基础表数据计算每个用户的每张试卷的经过归化公式之后的的得分情况;有两种情况:如果试卷得分的最大值和最小值相等则表示...
问题:请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间,并输出用户ID、试卷ID、归一化后分数平均值;最后按照试卷ID升序、归一化分数降序输出。(注:得分区间默认为[0,100],如果某个试卷作答记录中只有一个得分,那么无需使用
将用户作答 高难度 试卷的得分在每份试卷作答记录内执行 min-max 归一化 后缩放到 [0,100] 区间,并输出用户 ID、试卷 ID、归一化后分数平均值;最后按照试卷ID升序、归一化分数 降序输出。 得分区间默认为 [0,100],如果某个试卷作答记录中只有一个得分,归一化并缩放后分数仍为原分数。 min-max 归一化: ...
对试卷得分做min-max归一化 http://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6 一、知识点总结与拓展 本题所涉及到的知识点不多,困难点在于题目读起来比较拗口,以及一些关键信息藏在“解释”里。牛客的题,好多线索都喜欢放在“解释”里。