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;
一、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;
说明:
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;
三、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;
四、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;