微网站建设开发工具,开发公司土建工程师绩效考核,学术推广,卖手表的网站0 问题描述参考链接(3条消息) HiveSql面试题12--如何分析去掉最大最小值的平均薪水#xff08;字节跳动#xff09;_莫叫石榴姐的博客-CSDN博客文中已经给出了三种解法#xff0c;这里我们借助于此题#xff0c;来研究如何用percent_rank()函数求解#xff0c;简化解题思路…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)10010%10010%80333%40450%40450%20683%107100%注意点1percent_rank()对重复值的处理 2percent_rank()对NULL值的处理特点首尾一定是0 和1cume_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_salaryfrom(
SELECTemp_num,dep_num,salary,PERCENT_RANK() over(PARTITION BY dep_num ORDER BY salary) as rate
from salary
) twhere rate ! 0 and rate ! 1
group by dep_num;3 小结本文给出了一种利用percent_rank()求去掉最大最小值的平均薪水的方法该方法更简洁高效值得借鉴。通过本文需要掌握的姿势点如下PERCENT_RANK函数的作用、意义及使用场景是什么 PERCENT_RANK函数的结果是如何计算PERCENT_RANK与cume_disk()函数的区别如何利用PERCENT_RANK()函数的特性快速get最大、最小值?
