Hive 如何巧用分布函数percent_rank()剔除极值求均值

场景描述

前期写过一篇关于剔除订单极值求订单均值的案例，之前使用的是 dense_rank 函数对订单金额进行排序后，过滤掉最大值最小值后进行处理，最近工作刚好使用到分布函数percent_rank，想起来应该也可以用到这个场景；

percent_rank() 简介

percent_rank() 函数为分布函数，用于返回某个排序数值在数据集中的百分比排位，其值分布在0-1之间【0,1】，此函数用于计算数值在数据集内的相对位置。

计算公式：当前行rn -1 / 组内行数 -1 其中减去1表示排位时候不包括当前订单本身，表示他前面有多少订单比它值低或高，在实际中有一定分析意义。

解题方法

题目中要求是去除最大值、最小值后的平均值，因此本题难点问题是如何去除，最大、最小值。经过上面分析，percent_rank() 函数为按照某个排序后值进行排名后当前行的占比，其值在[0,1]区间内，按照其特性，我们知道排序后，0和1 的值代表最小和最大值，因此我们根据该函数很容易获取最大最小值的标记，从而解决了row_number() 或dense_rank()函数使用一次排序不能彻底区分最大，最小值的问题，简化了问题的求解方式

  dense_rank() over (partition by shop_id order by sale) rn,
  dense_rank() over (partition by shop_id order by sale desc) rn_

如果使用 percent_rank()  可以利用分布特性，使用一次即可过滤掉分组内的最大值和最小值

参考实现

select shop_id, cast(avg(sale) as decimal(18, 0)) as avg_salary
from (select shop_id, sale, percent_rank() over (partition by shop_id order by sale) as rate
      from temp_shop_info) t
where rate != 0
  and rate != 1
group by shop_id;