HiveSql一天一个小技巧：如何巧用分布函数percent

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

0 问题描述

参考链接

(3条消息) HiveSql面试题12--如何分析去掉最大最小值的平均薪水字节跳动_莫叫石榴姐的博客-CSDN博客

文中已经给出了三种解法这里我们借助于此题来研究如何用percent_rank()函数求解简化解题思路。

1 percent_rank()函数使用

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

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

使用场景用于关心排在我前面的有多少人。

如班级成绩为例返回的百分数60%表示某个分数排在班级总分排名前60%。

比如站队我往往关心的是排在我前面的有多少人。如下一组数据

如成绩为20的人排在他前面的有5个人除去自身总共有6个人那么他的相对排名百分比为 5/6

成绩为10的排在他前面的有6个人除去自身那么整个群体中都比他的分数高所以也就是100%

成绩	排名(rank)	百分比排名(percent_rank)
100	1	0%
100	1	0%
80	3	33%
40	4	50%
40	4	50%
20	6	83%
10	7	100%

注意点1percent_rank()对重复值的处理

2percent_rank()对NULL值的处理

特点首尾一定是0 和1

cume_dist()累积百分比

和percent_rank()差不多区别在于是否排除自身影响

含义

升序排序表示小于等于当前值的人数所占百分比

降序排序大于等于当前值的人数所占百分比

2 题目分析

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

with salary as (
select
'10001' emp_num    , '1' dep_num    , '60117'   salary
union all
select '10002' emp_num    , '2' dep_num    , '92102'   salary
union all
select '10003' emp_num    , '2' dep_num    , '86074'   salary
union all
select '10004' emp_num    , '1' dep_num    , '66596'   salary
union all
select '10005' emp_num    , '1' dep_num    , '66961'   salary
union all
select '10006' emp_num    , '2' dep_num    , '81046'   salary
union all
select '10007' emp_num    , '2' dep_num    , '94333'   salary
union all
select '10008' emp_num    , '1' dep_num    , '75286'   salary
union all
select '10009' emp_num    , '2' dep_num    , '85994'   salary
union all
select '10010' emp_num    , '1' dep_num    , '76884'   salary
)
SELECT dep_num,cast(avg(salary) as decimal(18,0)) as avg_salary
 from(
SELECT
   emp_num
  ,dep_num
  ,salary
  ,PERCENT_RANK() over(PARTITION BY dep_num ORDER BY salary) as rate
from salary
) t
 where rate != 0 and rate != 1
group by dep_num;