题解 | #SQL类别高难度试卷得分的截断平均值#

# 两表join，并构建每个考试的最大分列和最小分列
WITH tmp1 AS (
    SELECT t1.id,
           uid,
           t1.exam_id,
           score,
           tag,
           difficulty,
		   # 构建最大分值列
           MAX(score)
               OVER (PARTITION BY t1.exam_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max,
		   # 构建最小分值列
           MIN(score)
               OVER (PARTITION BY t1.exam_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min
    FROM exam_record t1
			# join的时候筛选出‘hard’的考试，并按照exam_id相等进行关联
             LEFT JOIN (SELECT * FROM examination_info WHERE difficulty = 'hard') t2 ON t1.exam_id = t2.exam_id
			过滤掉关联不上的数据，以及没成绩的数据
    WHERE t2.id IS NOT NULL
      AND t1.score IS NOT NULL)
	  #过滤掉成绩和最大、最小相等的数据，按照考试id，tag，难度的分组聚合，求出平均分
SELECT tag,
        difficulty,
		#求平均分
        round( avg(score),1) as clip_avg_score
FROM tmp1
#过滤
WHERE score != max
  AND score != min
  #分组
GROUP BY exam_id,tag,difficulty;