hive之分析窗函数(二)

       这次介绍几个序列函数NTILE,ROW_NUMBER,RANK,DENSE_RANK

        注意序列函数不支持WINDOWS字句，即rows between

1.数据准备

   参考上一篇文章，下面是结果：

   

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

2.NTILE

    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,  --分组内将数据分成3片  
NTILE(4) OVER(ORDER BY createtime) AS rn3        --将所有数据分成4片  
FROM lxw1234   
ORDER BY cookieid,createtime;  

  结果：

cookieid  createtime   pv      rn1     rn2     rn3
cookie1 2015-04-10      1       1       1       1
cookie1 2015-04-11      5       1       1       1
cookie1 2015-04-12      7       1       1       2
cookie1 2015-04-13      3       1       2       2
cookie1 2015-04-14      2       2       2       3
cookie1 2015-04-15      4       2       3       4
cookie1 2015-04-16      4       2       3       4
cookie2 2015-04-10      2       1       1       1
cookie2 2015-04-11      3       1       1       1
cookie2 2015-04-12      5       1       1       2
cookie2 2015-04-13      6       1       2       2
cookie2 2015-04-14      3       2       2       3
cookie2 2015-04-15      9       2       3       3
cookie2 2015-04-16      7       2       3       4

例如：统计每个cookie中，pv数最多的前1/3的日期是哪几天

cookieid  createtime    pv      rn
cookie1 2015-04-12      7       1
cookie1 2015-04-11      5       1
cookie1 2015-04-16      4       1
cookie1 2015-04-15      4       2
cookie1 2015-04-13      3       2
cookie1 2015-04-14      2       3
cookie1 2015-04-10      1       3
cookie2 2015-04-15      9       1
cookie2 2015-04-16      7       1
cookie2 2015-04-13      6       1
cookie2 2015-04-12      5       2
cookie2 2015-04-11      3       2
cookie2 2015-04-14      3       3
cookie2 2015-04-10      2       3

取rn=1就是我们想要的结果

3.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 lxw1234; 

结果：

cookieid  createtime    pv      rn
cookie1 2015-04-12      7       1
cookie1 2015-04-11      5       2
cookie1 2015-04-16      4       3
cookie1 2015-04-15      4       4
cookie1 2015-04-13      3       5
cookie1 2015-04-14      2       6
cookie1 2015-04-10      1       7
cookie2 2015-04-15      9       1
cookie2 2015-04-16      7       2
cookie2 2015-04-13      6       3
cookie2 2015-04-12      5       4
cookie2 2015-04-11      3       5
cookie2 2015-04-14      3       6
cookie2 2015-04-10      2       7

4.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 lxw1234
order by cookieid,pv desc 

结果：

cookieid  createtime       pv         rn1        rn2       rn3
cookie1 2015-04-12      7       1       1       1
cookie1 2015-04-11      5       2       2       2
cookie1 2015-04-16      4       3       3       3
cookie1 2015-04-15      4       3       3       4
cookie1 2015-04-13      3       5       4       5
cookie1 2015-04-14      2       6       5       6
cookie1 2015-04-10      1       7       6       7
cookie2 2015-04-15      9       1       1       1
cookie2 2015-04-16      7       2       2       2
cookie2 2015-04-13      6       3       3       3
cookie2 2015-04-12      5       4       4       4
cookie2 2015-04-11      3       5       5       5
cookie2 2015-04-14      3       5       5       6
cookie2 2015-04-10      2       7       6       7

分析：

    名次从第一开始，rank()会在名次中留下空位，那么当有2个并列第三的时候，下一个的排名就是第5.每个人都会有名次，名次数最大值=总人数，但是中间可能会有一些名次没有（比如上面，没有名次第4的人）

   

  DENSE_RANK()不同，当有2个并列第三的时候，下一个的排名就是第4，排名不会留下空位。

     row_number按照上面所说，从1开始排序，没有重复的

     row_number()可以实现去重功能，因为对于指定字段相同的内容也会标上不同的序列，所以我们可以取为1的即可