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

select tag
,difficulty
,round(avg(score),1) as clip_avg_score
from (
    select 
    tag
,score
,difficulty
,a.exam_id
,rank()over(partition by exam_id order by score desc) as score1
,rank()over(partition by exam_id order by score ) as score2
from examination_info a join exam_record b on a.exam_id =b.exam_id
where score is not null
) c
where c.score1 !=1 and c.score2 !=1 
group by c.exam_id

审题后会发现最难的是取截断平均值，要去掉最大值和最小值，因此选择了开窗函数

然后是需要去掉最大值和最小值，因此用了 !=

最后，是需要取difficulty下的平均值，但是是以exam_id作为条件筛选，因此以exam_id作为分区，最后聚合到difficulty