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

SELECT
    tag,
    difficulty,
    round(avg(score),1) AS clip_avg_score
FROM (SELECT
        tag,
        difficulty,
        score,
        rank() over(order by score desc) AS high_rank,
        rank() over(order by score) AS low_rank
    FROM exam_record er LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
    WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL) AS t1
WHERE high_rank != 1 AND low_rank != 1
GROUP BY tag