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

select 
    tag
    ,difficulty
    ,round(sum(score)/(rnk_max-2),1) as clip_avg_score
from
(
    select
        *
        ,max(rnk) over(partition by exam_id) as rnk_max
    from
    (
        select
            a.exam_id
            ,b.tag
            ,b.difficulty
            ,score
            ,row_number() over(partition by a.exam_id order by a.score) as rnk
        from 
        (
            select * from exam_record where submit_time is not null
        ) a 
        join 
        (
            select *
            from examination_info
            where tag='SQL'
            and difficulty='hard'
        ) b on a.exam_id=b.exam_id
    ) t
) i
where rnk!=1
and rnk!=rnk_max
group by 
    tag
    ,difficulty

考虑到了可能有多个SQL难题的情况用了groupby

去掉最大值用了max的窗口函数，也可以再用一个row_number的倒序

稳健的、可拓展性写法