微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

Hive分析窗口函数(三) CUME_DIST,PERCENT_RANK

Hive lxw1234@qq.com 59883℃ 5评论

这两个序列分析函数不是很常用,这里也介绍一下。

注意: 序列函数不支持WINDOW子句。(什么是WINDOW子句,点此查看前面的文章

Hive版本为 apache-hive-0.13.1

数据准备:

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

CREATE EXTERNAL TABLE lxw1234 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile location '/tmp/lxw11/';


hive> select * from lxw1234;
OK
d1      user1   1000
d1      user2   2000
d1      user3   3000
d2      user4   4000
d2      user5   5000

CUME_DIST

–CUME_DIST 小于等于当前值的行数/分组内总行数
–比如,统计小于等于当前薪水的人数,所占总人数的比例

SELECT 
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM lxw1234;

dept    userid   sal   rn1       rn2 
-------------------------------------------
d1      user1   1000    0.2     0.3333333333333333
d1      user2   2000    0.4     0.6666666666666666
d1      user3   3000    0.6     1.0
d2      user4   4000    0.8     0.5
d2      user5   5000    1.0     1.0

rn1: 没有partition,所有数据均为1组,总行数为5,
     第一行:小于等于1000的行数为1,因此,1/5=0.2
     第三行:小于等于3000的行数为3,因此,3/5=0.6
rn2: 按照部门分组,dpet=d1的行数为3,
     第二行:小于等于2000的行数为2,因此,2/3=0.6666666666666666

PERCENT_RANK

–PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
应用场景不了解,可能在一些特殊算法的实现中可以用到吧。

SELECT 
dept,
userid,
sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内
RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分组内总行数
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM lxw1234;

dept    userid   sal    rn1    rn11     rn12    rn2
---------------------------------------------------
d1      user1   1000    0.0     1       5       0.0
d1      user2   2000    0.25    2       5       0.5
d1      user3   3000    0.5     3       5       1.0
d2      user4   4000    0.75    4       5       0.0
d2      user5   5000    1.0     5       5       1.0

rn1: rn1 = (rn11-1) / (rn12-1) 
	   第一行,(1-1)/(5-1)=0/4=0
	   第二行,(2-1)/(5-1)=1/4=0.25
	   第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分组,
     dept=d1的总行数为3
     第一行,(1-1)/(3-1)=0
     第三行,(3-1)/(3-1)=1

点此查看所有Hive窗口分析函数的文章

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » Hive分析窗口函数(三) CUME_DIST,PERCENT_RANK

喜欢 (85)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(5)个小伙伴在吐槽
  1. percent_rank 在hive中,可以用来做某类数据,在另一类数据中的排名。 比如有以下两个表:ta 与 tb,根据 ra 与 rb 字段分组,希望计算出 tb中每个分组下的数据,在ta表中的排名percent: select type, ra, rb, num , percent_rank() over(partition by ra, rb order by num) as rank from (select 'tb' as type, tb.* from tb union all select 'ta' as type, ta.* from ta)t; 不过当tb在每个分组下有多条记录时,并不是太适用了 type ra rb num ta a 1 26 ta a 2 24 ta a 2 23 ta a 3 14 ta a 3 12 ta a 3 17 ta a 3 15 ta b 1 26 ta b 1 21 ta b 1 23 ta b 2 33 ta b 3 16 ta b 3 34 ta c 2 30 ta c 2 16 ta c 3 32 ta c 3 13 ta c 3 39 type ra rb num tb a 1 15 tb a 3 16 tb b 1 23 tb c 2 35
    五花吃肉2017-03-11 11:04 回复
  2. 很清楚,感谢~
    孙安知2018-09-29 14:02 回复
  3. percent_rank 是不是返回分组内自己所在的百分位
    转瞬之间2019-09-30 16:30 回复
    • 看起来是的。文章中PERCENT_RANK() OVER(ORDER BY sal) AS rn1, --分组内,由于没有partition by,实际是全局,导致看起来有些不太好理解
      2021-05-31 20:23 回复
  4. 一直是自己手动实现 :cry:
    muy2020-08-19 16:25 回复