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

#SQL计算截断平均值和移动平均值| 来源: 网络整理| 查看: 265

在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 表名;

【本文地址】

公司简介

联系我们