hive 窗口函数（二）

今天介绍几个序列函数，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：  按顺序编号，排序列值相等排序结果同号，不留空位;

------------------------------------------------------------------------------------------------------------------------------------------------

 下面介绍两个不太常用到的分析函数

数据准备

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

建表

create table  hive.cookie3_sum
(
    dept   string,
    userid string,
    sal    int
)
    row format delimited fields terminated by ',';
select *
from  hive.cookie3_sum;

  cume_dis 小于等于当前值的行数/分组内总行数

select dept, userid, sal, cume_dist() over (order by sal) as rn1, cume_dist() over (partition by dept order by sal) as rn2 from hive.cookie3_sum;

  计算逻辑说明：

rn1: 没有partition,所有数据均为1组，总行数为5,5就是那个分母
     第一行：小于等于1000的行数为1，因此，1/5=0.2
     第二行：小于等于2000的行数为2，因此，2/5=0.4
rn2: 按照部门分组，dpet=d1的行数为3,此时分母为3，当d2 时分母为2
     第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

percent_rank ：分组内当前行的 RANK 值-1/分组内总行数-1

select dept,
       userid,
       sal,
       percent_rank() over (order by sal)                   as rn1,  --分组内
       rank() over (order by sal)                           as rn11, --分组内的rank值
       sum(1) over (partition by null)                      as rn12, --分组内总行数
       percent_rank() over (partition by dept order by sal) as rn2,
       rank() over (partition by dept order by sal)         as rn21,
       sum(1) over (partition by dept)                      as rn22
from  hive.cookie3_sum;

  计算逻辑说明：

rn1: rn1 = (rn11-1) / (rn12-1) 
       第一行,(1-1)/(5-1)=0/4=0
       第二行,(2-1)/(5-1)=1/4=0.25
       第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分组，
     dept=d1的总行数为3
     第一行，(1-1)/(3-1)=0
     第三行，(3-1)/(3-1)=1