HIVE SQL:某一类别试卷得分的截断平均值

题目描述：
计算所有用户完成SQL类别**高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。
例子：试卷9001为高难度SQL试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为81.6666667，保留一位小数后为81.7

主要字段：
表1：examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
表2：exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

方法一：公式法

select
    tag,
    difficulty,
    round((sum(score)-min(score)-max(score))/(count(er.score)-2),1)
from exam_record as er 
left join examination_info as ei
on er.exam_id=ei.exam_id
where er.score is not null and difficulty='hard' and tag='SQL'
group by tag,difficulty

方法二：利用窗口函数排序后，去掉每组exam_id非NULL行的分数求平均；（这个是自己想的思路，比较笨）

select
    tag,
    difficulty,
    round(avg(score),1) as avg_score
from 
(select
    er.exam_id,
    tag,
    difficulty,
    score,
    row_number() over(partition by er.exam_id order by score ) as r,
    lag(score,1,null) over(partition by er.exam_id order by score) as lag_score,
    lead(score,1,null) over(partition by er.exam_id order by score) as lead_score
from exam_record as er 
left join examination_info as ei
on er.exam_id=ei.exam_id
where er.score is not null and difficulty='hard' and tag='SQL')a
where lag_score is not null and lead_score is not null
group by tag,difficulty