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

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;

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;

select 
  cookieid, 
  createtime, 
  pv, 
  ntile(3) over (partition by cookieid order by pv desc ) as rn  
from cookie.cookie2;

select 
  cookieid, 
  createtime, 
  pv, 
  row_number() over (partition by cookieid order by pv desc) as rn 
from cookie.cookie2;

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';