用presto取均值时,排除最大和最小的写法 。
例如查询每个班级学生成绩的均值
select
class
,avg(score) score_avg
from
(
select
class
,student
,score
,row_number() over(partition by class order by score desc ) rank_desc
,row_number() over(partition by class order by score ) rank_abs
from table
)
where rank_desc <> 1 and rank_abs <> 1
group by 1
利用开窗函数对每个班级学生的成绩进行排序,然后剔除掉第一和倒数第一,最后再取平均值