今天介绍几个序列函数，NTILE,ROW_NUMBER,RANK,DENSE_RANK，其中 ROW_NUMBER 是现在工作中较常用到的函数，下面会一一解释各自的用途。

数据准备

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

建表，导入数据

create table  hive.cookie2_sum
(
    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  hive.cookie2_sum;

 

 一 、ntile 

ntile(n)，用于将分组数据按照顺序切分成 n 片，返回当前切片值

set mapreduce.map.memory.mb=8192;
set mapreduce.reduce.memory.mb=8192;
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 片
       ntile(5) over (partition by cookieid order by createtime) as rn05 --将分组内将数据分成 5 片
from hive.cookie2_sum
order by cookieid, createtime;

 

 例子，统计一个cookie，pv数最多的前 1/3 的天的数据

select *
from (select cookieid,
             createtime,
             pv,
             ntile(3) over (partition by cookieid order by pv desc ) as rn
      from  hive.cookie2_sum) t
where rn = 1;

 

 二、row_number

ROW_NUMBER() –从1开始，按照制定排序列和排序规则进行排序，生成分组内记录的序列，比如，按照pv降序排列，生成分组内每天的pv名次
ROW_NUMBER() 的应用场景非常多，再比如，业务数据按 user_id 去重等等。

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

 

 查询结果如上所示，日如果需要自定义限制前几名，可以嵌套子查询在限制一下 rn 的范围即可；

三、rank 和 dense_rank

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

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

select cookieid,
       createtime,
       pv,
       rank() over (partition by cookieid order by pv desc)       as rank_,
       dense_rank() over (partition by cookieid order by pv desc) as dense_rank_,
       row_number() over (partition by cookieid order by pv desc) as row_number_
from  hive.cookie2_sum
where cookieid = 'cookie2';

 

 说明：

row_number： 按顺序编号，排序列值相等排序结果不留空位;
rank：　　       按顺序编号，排序列值相等排序结果同号，留空位;
dense_rank：  按顺序编号，排序列值相等排序结果同号，不留空位;