Hive学习之路 （十四）Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK

概述
本文中介绍前几个序列函数，NTILE,ROW_NUMBER,RANK,DENSE_RANK，下面会一一解释各自的用途。

注意： 序列函数不支持WINDOW子句。（ROWS BETWEEN）

回到顶部
数据准备
数据格式
复制代码
cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7
复制代码
创建表
use cookie;
drop table if exists cookie2;
create table cookie2(cookieid string, createtime string, pv int) row format delimited fields terminated by ',';
load data local inpath "/home/hadoop/cookie2.txt" into table cookie2;
select * from cookie2;

玩一玩NTILE
说明
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布

查询语句
复制代码
select
cookieid,
createtime,
pv,
ntile(2) over (partition by cookieid order by createtime) as rn1, --分组内将数据分成2片
ntile(3) over (partition by cookieid order by createtime) as rn2, --分组内将数据分成2片
ntile(4) over (order by createtime) as rn3 --将所有数据分成4片
from cookie.cookie2
order by cookieid,createtime;

查询结果

比如，统计一个cookie，pv数最多的前1/3的天
查询语句
复制代码
select
cookieid,
createtime,
pv,
ntile(3) over (partition by cookieid order by pv desc ) as rn
from cookie.cookie2;
复制代码
查询结果

--rn = 1 的记录，就是我们想要的结果

回到顶部
玩一玩ROW_NUMBER
说明
ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
–比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。

分组排序
复制代码
select
cookieid,
createtime,
pv,
row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;
复制代码
查询结果

-- 所以如果需要取每一组的前3名，只需要rn<=3即可，适合TopN

回到顶部
玩一玩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 cookie.cookie2
where cookieid='cookie1';
复制代码
查询结果

ROW_NUMBER、RANK和DENSE_RANK的区别
row_number： 按顺序编号，不留空位
rank： 按顺序编号，相同的值编相同号，留空位
dense_rank： 按顺序编号，相同的值编相同的号，不留空位

转发自：https://www.cnblogs.com/qingyunzong/p/8798102.html