hive 窗口函数(三)

数据准备

cookie1,2015-04-10 10:00:02,url2
cookie1,2015-04-10 10:00:00,url1
cookie1,2015-04-10 10:03:04,1url3
cookie1,2015-04-10 10:50:05,url6
cookie1,2015-04-10 11:00:00,url7
cookie1,2015-04-10 10:10:00,url4
cookie1,2015-04-10 10:50:01,url5
cookie2,2015-04-10 10:00:02,url22
cookie2,2015-04-10 10:00:00,url11
cookie2,2015-04-10 10:03:04,1url33
cookie2,2015-04-10 10:50:05,url66
cookie2,2015-04-10 11:00:00,url77
cookie2,2015-04-10 10:10:00,url44
cookie2,2015-04-10 10:50:01,url55
create table hive.cookie4_sum
(
    cookieid   string,
    createtime string,
    url        string
)
    row format delimited fields terminated by ',';
 select * from hive.cookie4_sum;

hive 窗口函数(三)

  一、lag

lag(col,n,default) 用于统计窗口内往上第 n 行值

参数说明:

第一个参数为列名,
第二个参数为往上第 n 行(可选,默认为 1),
第三个参数为默认值(当往上第 n 行为 null 时候,取默认值,如不指定,则为null)

select cookieid,
       createtime,
       url,
       row_number() over (partition by cookieid order by createtime)                              as rn,
       lag(createtime, 1, '1970-01-01 00:00:00') over (partition by cookieid order by createtime) as last_1_time,
       lag(createtime, 2) over (partition by cookieid order by createtime)                        as last_2_time
from hive.cookie4_sum;

hive 窗口函数(三)

 

 说明:

last_1_time: 指定了往上第1行的值,默认值 为'1970-01-01 00:00:00'  
        cookie1第一行,往上1行为 null,因此取默认值 1970-01-01 00:00:00
        cookie1第三行,往上1行值为第二行值,2015-04-10 10:00:02
        cookie1第六行,往上1行值为第五行值,2015-04-10 10:50:01
last_2_time: 指定了往上第2行的值, 默认值 null
        cookie1第一行,往上2行为 null
        cookie1第二行,往上2行为 null
        cookie1第四行,往上2行为第二行值,2015-04-10 10:00:02
        cookie1第七行,往上2行为第五行值,2015-04-10 10:50:01

二、lead

lead(col,n,default) 用于统计窗口内往下第 n 行值;

参数说明:
第一个参数为列名,
第二个参数为往下第 n 行(可选,默认为1),
第三个参数为默认值(当往下第 n 行为 null 时候,取默认值,如不指定,则为 null)

select cookieid,
       createtime,
       url,
       row_number() over (partition by cookieid order by createtime)                               as rn,
       lead(createtime, 1, '1970-01-01 00:00:00') over (partition by cookieid order by createtime) as next_1_time,
       lead(createtime, 2) over (partition by cookieid order by createtime)                        as next_2_time
from hive.cookie4_sum; 

hive 窗口函数(三)

 

 三、first_value

取分组内数据排序后,截止到当前行,第一个值

select cookieid,
       createtime,
       url,
       row_number() over (partition by cookieid order by createtime)     as rn,
       first_value(url) over (partition by cookieid order by createtime) as first1
from  hive.cookie4_sum;

 

hive 窗口函数(三)

 

 四、last_value

取分组内数据排序后,截止到当前行,最后一个值

select cookieid,
       createtime,
       url,
       row_number() over (partition by cookieid order by createtime)    as rn,
       last_value(url) over (partition by cookieid order by createtime) as last1
from hive.cookie4_sum;

hive 窗口函数(三)

 

上一篇:浅谈JSONP跨域漏洞


下一篇:剑指 Offer 54. 二叉搜索树的第k大节点