【场景】：截断平均值

【分类】：可能存在多个最值的情况下，如何只去掉一个最值、窗口函数rank()；去掉正序排和倒序排的第一行

分析思路

难点：

1.如何去掉最大值和最小值(去掉一个最大值和一个最小值后的平均值)

如何去掉最大值和最小值

  • [使用]：在where 中使用 select max 和min，如果有多个最大值和最小值，这种方法计算均值的时候去掉的不止一个最大值和最小值。所以此方法不适用。
  • [使用]：窗口函数rank()；去掉正序排和倒序排的第一行。

判断不等于

  • [使用]：<> 或者 !=

求解代码

错误代码 使用 where select

select
    tag,
    difficulty,
    round(avg(score),1) as clip_avg_score
from exam_record a, examination_info b
where a.exam_id = b.exam_id
and b.tag = 'SQL'
and b.difficulty = 'hard'
and a.score is not null 
and a.score != (select min(score) from exam_record)
and a.score != (select max(score) from exam_record)
group by b.tag, b.difficulty

正确代码

方法一：

with子句

with
    main as(
        #对成绩进行正序、倒序排序
        select 
            tag,
            difficulty, 
            score, 
            row_number() over(order by score asc) as rank_asc,
            row_number() over(order by score desc) as rank_desc
        from examination_info 
        join exam_record using(exam_id)
        where tag = 'SQL' 
        and difficulty = 'hard' 
        and score is not null
    )
#统计SQL类别高难度试卷得分的截断平均值
select 
    tag, 
    difficulty, 
    round(avg(score), 1) as clip_avg_score
from main
where rank_asc <> 1 and rank_desc <> 1
group by tag, difficulty

main表输出结果：

1	SQL|hard|90|5|1	
2	SQL|hard|84|4|2		
3	SQL|hard|81|3|3		
4	SQL|hard|80|2|4		
5	SQL|hard|50|1|5

方法二：

from子查询

#统计SQL类别高难度试卷得分的截断平均值
select 
    tag, 
    difficulty, 
    round(avg(score), 1) as clip_avg_score
from(
    #对成绩进行正序、倒序排序
    select 
        tag,
        difficulty, 
        score, 
        row_number() over(order by score asc) as rank_asc,
        row_number() over(order by score desc) as rank_desc
    from examination_info 
    join exam_record using(exam_id)
    where tag = 'SQL' 
    and difficulty = 'hard' 
    and score is not null
) main
where rank_asc != 1 and rank_desc != 1
group by tag, difficulty