四者都是常见的排序窗口函数,区别:
1、NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布
2、ROW_NUMBER() 从1开始,按照顺序,生成分组内记录的序列
3、RANK()生成数据项在分组中的排名从1开始,排名相等会在名次中留下空位
4、DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位
5、这4项都不支持row between and
1、hive建表
1)首先在另外一个窗口上建立txt的数据集
2)hive建表
由于cookie在木子李:hive基础建表(1)已经建立表结构,故采用表结构进行建表导入
2、NTILE(n)-按顺序切分n片,返回当前切片值
如果切片不均与,默认增加第一个切片的分布
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;
问题:求该pv点击量前三分之一的是哪天?
select
cookieid,
createtime,
pv,
ntile(3) over (partition by cookieid order by pv desc ) as rn1--按照cookieid分组,分组内数据切分三分
ntile(3) over (partition by cookieid ) as rn2--按照cookieid分组,按pv降序排列, 分组内数据切分三分
ntile(3) over (order by pv desc ) as rn3--全局按pv降序排列, 数据切分三分,不分组
ntile(3) over ( ) as rn4--全局数据切分三分
from cookie.cookie2;
按照需求,选rn1-分组pv降序,取标记为1的数据。
3、ROW_NUMBER-无间隔排序
select
cookieid,
createtime,
pv,
row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2;
4、ROW_NUMBER、RANK 和 DENSE_RANK
这三个窗口函数的使、用场景非常多
- row_number()从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
- RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
- DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位
**注意:
rank和dense_rank的区别在于排名相等时会不会留下空位.**
为方便观看,我们采用木子李:Hive分析窗口函数(一) SUM,AVG,MIN,MAX(3)的cookie表进行计算。
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';
5、ROW_NUMBER、RANK 和 DENSE_RANK 的不同
row_number: 按顺序编号,不留空位 rank: 按顺序编号,相同的值编相同号,留空位 dense_rank: 按顺序编号,相同的值编相同的号,不留空位
Hive学习之路 (十四)Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK