Hive 高阶--分组窗口函数--序列分组函数（CUME_DIST）|学习笔记

开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）：Hive 高阶--分组窗口函数--序列分组函数（CUME_DIST）】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/696/detail/12234

Hive 高阶--分组窗口函数--序列分组函数（CUME_DIST）

内容介绍：

一、CUME_ DIST

二、percent rank

 

一、CUME_ DIST

1、了解两个不常见的分析窗口函数叫序列函数，序列函数指的是在分组内把数据进行排序生成序列，需要强调的是序列函数不支持 window 子句，window 子句就是 rows between 控制行数从第几行到第几行。

2、数据是标准化的数据，当中有部门编号及用户编号以及它的薪水，计划数据需要创建表把它验证出来。表字段对应上，分隔符为逗号，存储格式为普通文本格式。

数据准备

d1,user1, 1000

d1 ,user2,2000

d1,user3, 3000

d2 ,user4, 4000

d2 ,user5, 5000

CREATE TABLE itcast _t3 (

dept STRING ,

userid string ,

sal INT

) ROW FORMAT DELIMITED

FIELDS TERMINATED BY ' , '

stored as textfile;

打开 hive 的终端，创建表的操作，创建好之后把结构化数据上传到的 linux 本地，使用 load 命令加载过来。

加载数据：

load data local inpath " /root/hivedata/itcast_ t3.dat' into table itcast_t3;

执行加载之后，按照老的思路去验证表是否有映射成功，操作如果没成功，后面直接写 sql，后面万一出错就需要重做表，产生完美映射。

3、CUME_ DIST 和 order by 的排序顺序有关系

CUME_ DIST 小于等于当前值的行数/分组内总行数 order 默认顺序正序升序。

比如统计小于等于当前薪水的人数所占总人数的比例。

截止到当前这一行，统计小于等于当前行数的值，分组内有多少行，

在进行部门清理统计时，比如是大的集团公司，下面有好多业务，好多部门，统计每个部门内薪水小于等于当前薪水的值，取个中间值月薪是一万，每个部门内一万能有多少占整个部门的比例，这样的需求用它非常方便，它的背后跟 order by 有相当大的关系。

SELECT

dept,

userid,

sal,

CUME_ DIST() OVER(ORDER BY sal) AS rn1,--没有 partition 语句所有的数据位于一组

CUME_ DISTO) OVER(PARTITION BY dept ORDER BY sal) AS rn2

FROM itcast_t3;

使用两个函数，没有分组的默认所有数据为一组。如果没有分组，所有数据位于一组，组内就会有五条记录，总共是12345，截止到当前行小于等于它的薪水占组内百分比，

执行，一个有分组一个没有分组，

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.666666666666666

到第一行时小于等于当前行1000，小于它自己只有它自己一个，一个占整个的五分之一，所以是0.2，下面是0.4，因为小于等于2000只有2000，1000，这两条占整个的五分之二0.4，但是当加上PARTITION BY 分组时，它就会根据部门进行分组，根据部分分组，有两个部门，一个是d1，一个是d2，在d1分组内共有三个，这时除以不是五而是三，统计第一条时小于等于它的只有它自己本身，一占整个三是三分之一，所以是0.333，第二个小于等于两千，两条占三条的三分之二，0.666，第三条，三分之三，1.0。第二个分组内有两个，0.5，1.0，统计出来少于等于当前值的行数占分组内总行数的百分比，对于的业务需要员统计薪水，每个省订单小于多少，这种比例情况就是统计所占行的百分比，非常方便，这是第一个序列函数叫 CUME_ DIST，函数中不能去添加 rows between，不能控制它从第几行到第几行，它不支持，语法层面不能通过。

二、percent rank

1、percent rank 百分比，函数背后表达的意义非常的复杂，经过研究后没有发现在当下的业务中或在企业中，这个函数可以用于什么业务场景，值得后续的不断追究和考证，但是函数的格式和值要理解清楚。

2、PERCENT_ RANK 分组内当前行的RANK值-1/分组内总行数-1，分组内有几行直接进行排序，sum 求和可以统计，RANK 值就是标号值12345，

经调研该函数显示现实意义不明朗，有待于继续考证。

SELECT

dept,

userid,

sal ,

PERCENT_ RANK() OVER(ORDER BY sal) AS rn1, --分组内

根据薪水进行排序，没有 PARTITION BY，所有数据都是一组，所有数据根据 sal 排序后依次打上 RANK 步骤号。

ORDER BY sal 意味着没有分组，分组就是一组。分组内是5。

RANK() OVER(ORDER BY sal) AS rn11,--分组内 RANK 值，RANK 就是分组内给每个数据打序列标号

SUM(1) OVER(PARTITION BY NULL 没有分组) AS rn12,--分组内总行数，sum是聚合函数跟分组函数配合使用，把所有 sum 分为一组后进行 sum 求和，默认只有一组，统计分组内所有函数。

PERCENT_ RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2

FROM itcast. t3;

rn1: rn1 = (rn11-1) 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

 

第一条步骤号是1，用它的 rank 值减1，等于0，0再除以总行数减1，四分之零还是零，用前面的值减1除以后面分组内减1，这就是当下第一个 rn1，PERCENT_ RANK值，如果分组之后要注意一旦分组之后，它的总和数就不再是五个而是第一个分组内有三个，第二个分组内有两个，一减一等于零，三减一等于二，二分之零还是零，二减一等于一，三减一等于二，二分之一，0.5，同样的三减一等于二，三减一等于二，二分之一，1.0，计算出每一条记录当前行 rank 值减1除以分组内总行数的百分比，这个函数非常不常见，当中需要掌握的就是 CUME_ DIST ，可以统计小于等于当前值的行数占分组内总行数的百分比，对统计薪水订单占比例的情况非常有帮助，只不过要强调当中的 order by 会影响最终的结果。