SQL计算截断平均值和移动平均值

在SQL实际数值计算中，可能会遇到计算计算某一列去掉最大值和最小值后的平均值（截断平均值）或者计算字段前m条记录和后n条记录的平均值。

截断平均值

截断平均最常见的场景是去掉一个最大值和一个最小值计算平均值。计算中有两种思路：一是求整列的和减去最大值和最小值除以（列计数-2）；二是利用窗口函数排名去掉第一名和最后一名（注意可能存在并列情况，使用row_number函数，另外窗口函数排名时不会自动忽略null值，需要把空值去掉或填充为0）

-- 去掉一个最大值，一个最小值计算平均值
select
    round((sum(score) - max(score) - min(score)) / (count(score) - 2), 1) as clip_avg_score
from test;

-- 使用窗口函数
select
    round(avg(score), 1) as clip_avg_score
from (select score,
      row_number() over(order by score) as t_rank_a,
      row_number() over(order by score desc) as t_rank_d
     from test 
     where score is not null)t
where t_rank_a != 1 and t_rank_d != 1

-- 空值填充为0
select
    round(avg(score), 1) as clip_avg_score
from (select score,
      row_number() over(order by ifnull(score,0)) as t_rank_a,
      row_number() over(order by ifnull(score,0) desc) as t_rank_d
     from test )t
where t_rank_a != 1 and t_rank_d != 1

移动平均值

-- 计算包含本条记录的前三条记录的平均值（向前移动二期）
select *,avg(计算字段) over(partition by 分组字段 order by 排序字段 rows between 2 preceding and current row) as 别名 from 表名;

-- 向前移动二期、向后移动二期
select *,avg(计算字段) over(partition by 分组字段 order by 排序字段 rows between 2 preceding and 2 following) as 别名 from 表名;