题目描述

牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。

要求

从考试记录的表（exam_record）中，计算 hard 难度的 得分平均值，要求去掉一个最大值和最小值。

解题过程

将测试数据导入到本地数据库

先查询确认一下 标记为 hard ，tag 为sql的答题记录

select * from examination_info as t

inner join exam_record t1 on t.exam_id = t1.exam_id

where tag = 'SQL' and difficulty = 'hard'

可以看到是有没有答完题目的情况。

结合介绍部分提供的示例，可以判断需要有分数的才统计在内。

答案

1、使用聚合函数

select tag,difficulty,

round((sum(score)-min(score)-MAX(score))/(count(score)-2),1) as clip_avg_score

 from examination_info as t

inner join exam_record t1 on t.exam_id = t1.exam_id

where tag = 'SQL' and difficulty = 'hard' and score is not NULL

group by tag,difficulty

由于只去掉一个最高值和一个最低值，使用聚合函数是比较简单的。
且分母不算没有分数的。
这里其实不加 score is not null 更通用一些，因为 聚合函数（列）都是对非null进行求职。

加上了就更好理解一点。

2、使用窗口函数

select "SQL" tag,"hard" as difficulty, round(avg(score), 1) as clip_avg_score

from(

select tag, difficulty, score,

    row_number() over(order by score asc) as rk_asc,

    row_number() over(order by score desc) as rk_desc

from examination_info as i

join exam_record as r

on i.exam_id = r.exam_id

where tag = 'SQL' and difficulty = 'hard' and score is not null) as t

where rk_asc <> 1 and rk_desc <> 1

逻辑就是先对所有的成绩进行排序，然后再剔除第一名和最后一名。

因为不想加group by 所用使用了添加辅助列的方式，这样在严格模式下也不会报错。

这里由于只有一个分组，所以把结果看成一张大表，所以没有partition by
.