Hive 高阶--分组窗口函数--常见的分组函数（rank、denserank、rownumber、ntile）|学习笔记

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

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

Hive 高阶--分组窗口函数--常见的分组函数（rank、denserank、rownumber、ntile）

内容介绍：

一、rank、denserank、rownumber

二、NTILE

三、小结

一、rank、denserank、rownumber

1、打开课程资料，里面列举出 hive 所有的分析窗口函数，当中有些常见，有些不常见，在 row number 函数中，它会不考虑数据重复性进行打标号，举例，统计昨天订单金额最多的前三个，如果相同算并列也把它考虑进来，这时有一个并列重复的问题，但是 row number 并没有考虑这些问题，如果需要得到并列结果，重复的结果该怎么办？

2、打开第一个窗口，准备结构化的数据，有三个字段，第一个是 cookie id，第二个是创建的时间，第三个是 cookie 对应产生的pv值，为了满足结构化数据能够创建成功，在 hive 中创建一个表，表名叫 itcast_t，字段和类型，字段之间的分隔符为逗号，跟数据保持一致，存储的文件是普通的文本文件，映射这个表。

Cookie1,2018-04-10,1

Cookie1,2018-04-11,5

cookie1,2018-04-12,7

cookie1,2018-04-13,3

Cookie1,2018-04-14,2

cookie1,2018-04-15,4

cookie1,2018-04-16 ,4

cookie2 ,2018-04-10,2

cookie2,2018-04-11, 3

cookie2 ,2018-04-12,5

cookie2 ,2018-04-13, 6

cookie2 ,20Î8-04-14,3

cookie2 ,2018-04-15,9

cookie2 ,2018-04-16,7

CREATE TABLE itcast, _t2 (

cookieid string,

createtime string, --day

pv INT

) ROW FORMAT DELIMITED

FIELDS TERMINATED BY' ,'

stored as textfile;

加载数据：

load data local inpath ' /root/hivedata/itcast_ t2.dat' into table itcast_t2;

3、复制数据，把建表语句复制执行，使用 load data 命令，因为数据来自于的服务器本地，使用 local 命令从本地文件系统加载，复制，为了验证是否成功，进行查询，没有问题。

4、把三个窗口函数结合在一起使用的语句，第一个叫做 RANK() OVER，根据cookie 分组排序得到返回的字段叫 rn1，下面是rn2，rn3，三个字段都是 PARTITION BY cookie，根据 cookie 进行分组，根据 cookie 分组，将会被分为两组，一个 cookie1组，一个 cookie2组，在每个分组内，根据的 pv 倒序进行排序，字段就是大的在上，小的在下，排序完之后里面进行的步骤号的标打工作，一个 rank，一个 DENSE_ RANK。

RANK 和 DENSE_ RANK

RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位

DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

SELECT

cookieid,

createtime,

pv,

RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1 ,

DENSE_ RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,

ROW_ NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3

FROM itcast_ t2

WHERE cookieid = 'cookie1' ;

5、进行数据执行，不过滤把所有数据都进行复制，如果加 whre 条件只有 cookie1，现在所有数据都有，把数据复制粘贴到的画图板中。

（1）pv是正常排序的过程，有的数据是重复的，rn1 是 runk()over返回的，rn2 是 DENSE_ RANK() OVER返回的，rn3 是 ROW_ NUMBER() OVER返回的，75443，44是重复的，runk()over 考虑了重复性，但是不是4，是5，4被并列的3站过去，在日常生活中也有这样的需求，比如几个人抢夺游泳比赛，如果两个人成绩是并列第二名，两个金牌，就没有第三名铜牌，因此针对 runk()over在进行数据分组标号时会考虑数据重复性。

RANK( ) over考虑数据的重复性，重复的数据会挤占后续的标号，就是重复的数据会导致后面的标号不会连续一二三三五，第二个三占的就是四坑位。

（2）DENSE_ RANK() OVER，一二三三，考虑三的重复性，但是下面的标号还是四，说明它考虑数据重复性时并没有挤占后续的标号。

DENSE_ RANK() OVER考虑数据重复性重复的数据不会挤占后续的标号。

（3）ROW_ NUMBER() OVER，一二三四五，没有考虑数据重复性，按照排序规则依次打上步骤号。

ROW_ NUMBER() OVER不会考虑重复性，只会打上步骤标号。

6、在企业现实区中使用是否考虑数据重复性，是否挤占后续编号要跟需求人员认真的沟通，比如需求人员让统计分析昨天订单金额最多的前五个，如果重复也算并列，ROW_ NUMBER不能用，可以使用 runk 或 DENSE_ RANK，要结合业务使用。

二、NTILE

1、NTILE 抽样分组函数

SELECT

cookieid,

createtime,

pv,

NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,

NTILE(3) OVER(KARTITION BY cookieid ORDER BY createtime) AS rn2,

NTILE(4) OVER(ORDER BY createtime) AS rn3

FROM itcast_ t2

ORDER BY cookieid,createtime ;

配合 over 分组函数的语句使用，根据 cookie 进行分组，根据创建的时间进行排序，默认正序，排序完之后把数据进行抽签分组。

2、需求：统计昨天每个省的订单，需要每个订单金额整体的三分之一，比如把昨天的订单按照不同的省进行分组统计，北京上海河北，浙江每个省的订单都做排序，排序完成之后只关心整个省内订单中的三分之一，需要把每个省的数据分成三份，只拿一份，可以根据指定的 num 数据，把每个组内的数据分成几份，根据 cookieid 进行分组，里面根据创建时间进行排序，NTILE(2)分为两组，cookie1中有七条数据，整体分为两部分，二分之一，NTILE(2)分成两个部分，一个是第一部分，一个是第二部分，专业说法叫做桶，分为两桶，第一桶和第二桶，它优先保证最小的桶有数据，但是并且保证前后相差不会超过两条，它一定不会出现五二，六一，这样的分法，最后的分法是四三对，优先满足最小的桶，并且相差不会超过两个，相差一个，这是它的分组规则。

3、复制执行，

NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,

NTILE(3) OVER(KARTITION BY cookieid ORDER BY createtime) AS rn2,

NTILE(4) OVER(ORDER BY createtime) AS rn3

FROM itcast_ t2

可以看到 cookie1分组，cookie2分组，在每个分组内根据创建的时间进行排序，首先看 cookie1的情况，把整体分为两个部分，分为两桶，前面一一一一四条数据分成第一部分，后面二二二分成第二部分，整体七条数据，它两个部分，第一部分用一表示，第二部分用二表示，需求让分析或者取出 cookie1 产生 pv 值中，后面的二分之一再加条件 vr rn1 等于二，三条记录就是整体中的后二分之一，前面是前二分之一，举例，NTILE(3) 也是一样，NTILE(3) 把整体分成三个部分，按照五一一三个部分，按照四二一也是三个部分，优先满足桶号较小的并且相差不会超过两个，因此最后分的是一一二二三三，优先满足最小的，所以第一个桶有三条记录，时间相差不会超过两条，需要中间的三分之一，只要加一条件 vr rn2 等于二，就可以过滤出来。NTILE 主要是帮助业务人员去关心整体中的几部分。

三、小结

分组窗口函数

1、row _number 不考虑数据重复性。

2、rank  考虑数据重复性，挤占标号。

3、dense_ rank  考虑数据重复性，不挤占标号。

4、ntile 适用于只关心整体的某些部分数据比如前三分之一，需要先分成三个部分取第一部分。优先满足桶号较小的部分并且保证前后桶的数据相差不会超过2。