用Hive分析函数lead计算用户页面的停留时长

Hive的分析函数又叫窗口函数，在oracle中就有这样的分析函数，主要用来做数据统计分析的。
Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率，其中over()表示当前查询的结果集对象，括号里面的语句则表示对这个结果集进行处理。
场景描述：
用户Peter在浏览网页，在某个时刻，Peter点进了某个页面，过一段时间后，Peter又进入了另外一个页面，如此反复，那怎么去统计Peter在某个特定网页的停留时间呢，又或是怎么统计某个网页用户停留的总时间呢？
1、建表及数据准备

drop table if exists xxx_user_vist_log;create table xxx_user_vist_log(userid string,time string,url string) row format delimited fields terminated by ',';[hadoop@emr-worker-10 hiveAnalyticFunction]$ cat xxx_user_vist_log.txt Peter,2015-10-12 01:10:00,url1Peter,2015-10-12 01:15:10,url2Peter,2015-10-12 01:16:40,url3Peter,2015-10-12 02:13:00,url4Peter,2015-10-12 03:14:30,url5Marry,2015-11-12 01:10:00,url1Marry,2015-11-12 01:15:10,url2Marry,2015-11-12 01:16:40,url3Marry,2015-11-12 02:13:00,url4Marry,2015-11-12 03:14:30,url5LOAD DATA LOCAL INPATH  '/home/hadoop/nisj/hiveAnalyticFunction/xxx_user_vist_log.txt'  OVERWRITE INTO TABLE xxx_user_vist_log;

2、获取用户在某个页面停留的起始与结束时间

select userid,time stime,lead(time) over(partition by userid order by time) etime,url from xxx_user_vist_log;

3、计算用户在页面停留的时间间隔

select userid,time stime,lead(time) over(partition by userid order by time) etime,UNIX_TIMESTAMP(lead(time) over(partition by userid order by time),'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(time,'yyyy-MM-dd HH:mm:ss') period,url from xxx_user_vist_log;

4、计算每个页面停留的总时间（某个用户访问某个页面的总时间）

select nvl(url,'-1') url,nvl(userid,'-1') userid,sum(period) totol_peroid from (select userid,time stime,lead(time) over(partition by userid order by time) etime,UNIX_TIMESTAMP(lead(time) over(partition by userid order by time),'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(time,'yyyy-MM-dd HH:mm:ss') period,url from xxx_user_vist_log) a group by url, userid with rollup;

5、最终想要的结果

OK-1      -1      14940url1    -1      620url1    Marry   310url1    Peter   310url2    -1      180url2    Marry   90url2    Peter   90url3    -1      6760url3    Marry   3380url3    Peter   3380url4    -1      7380url4    Marry   3690url4    Peter   3690url5    -1      NULLurl5    Marry   NULLurl5    Peter   NULLTime taken: 99.59 seconds, Fetched: 16 row(s)