在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

Hive 分析函數(shù)lead、lag實例應(yīng)用

Lag和Lead分析函數(shù)可以在同一次查詢中取出同一字段的后N行的數(shù)據(jù)(Lag)和前N行的數(shù)據(jù)(Lead)作為獨立的列。

這種操作可以代替表的自聯(lián)接，并且LAG和LEAD有更高的效率，其中over()表示當(dāng)前查詢的結(jié)果集對象，括號里面的語句則表示對這個結(jié)果集進行處理。

1 LEAD

與LAG相反，LEAD(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往下第n行值

參數(shù)1為列名，參數(shù)2為往下第n行（可選，默認為1），參數(shù)3為默認值（當(dāng)往下第n行為NULL時候，取默認值，如不指定，則為NULL）

　場景

用戶Peter在瀏覽網(wǎng)頁，在某個時刻，Peter點進了某個頁面，過一段時間后，Peter又進入了另外一個頁面，如此反復(fù)，那怎么去統(tǒng)計Peter在某個特定網(wǎng)頁的停留時間呢，又或是怎么統(tǒng)計某個網(wǎng)頁用戶停留的總時間呢？

create table test.user_log(
    userid string,
    time string,
    url string
) row format delimited fields terminated by '\t';

使用load命令將如下測試數(shù)據(jù)導(dǎo)入：

Peter   2015-10-12 01:10:00 url1
Peter   2015-10-12 01:15:10 url2
Peter   2015-10-12 01:16:40 url3
Peter   2015-10-12 02:13:00 url4
Peter   2015-10-12 03:14:30 url5
Marry   2015-11-12 01:10:00 url1
Marry   2015-11-12 01:15:10 url2
Marry   2015-11-12 01:16:40 url3
Marry   2015-11-12 02:13:00 url4
Marry   2015-11-12 03:14:30 url5

數(shù)據(jù)說明：Peter 2015-10-12 01:10:00 url1 ，表示Peter在2015-10-12 01:10:00進入了網(wǎng)頁url2，即記錄的是進入網(wǎng)頁的時間。

分析

要計算Peter在頁面url1停留的時間，需要用進入頁面url2的時間，減去進入url1的時間，即2015-10-12 01:15:10這個時間既是離開頁面url1的時間，也是開始進入頁面url2的時間。

獲取用戶在某個頁面停留的起始與結(jié)束時間：

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

stime就是進入頁面時間，etime就是離開頁面時間，結(jié)果是這樣的：

Marry   2015-11-12 01:10:00 2015-11-12 01:15:10 url1
Marry   2015-11-12 01:15:10 2015-11-12 01:16:40 url2
Marry   2015-11-12 01:16:40 2015-11-12 02:13:00 url3
Marry   2015-11-12 02:13:00 2015-11-12 03:14:30 url4
Marry   2015-11-12 03:14:30 NULL    url5
Peter   2015-10-12 01:10:00 2015-10-12 01:15:10 url1
Peter   2015-10-12 01:15:10 2015-10-12 01:16:40 url2
Peter   2015-10-12 01:16:40 2015-10-12 02:13:00 url3
Peter   2015-10-12 02:13:00 2015-10-12 03:14:30 url4
Peter   2015-10-12 03:14:30 NULL    url5

用etime減去stime，然后按照用戶分組累加就是，每個用戶訪問的總時間了。

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 test.user_log;

這里展示出了stime(開始時間)，etime(離開時間)，period(停留時長)，url(頁面地址)，結(jié)果：

Marry   2015-11-12 01:10:00 2015-11-12 01:15:10 310 url1
Marry   2015-11-12 01:15:10 2015-11-12 01:16:40 90  url2
Marry   2015-11-12 01:16:40 2015-11-12 02:13:00 3380    url3
Marry   2015-11-12 02:13:00 2015-11-12 03:14:30 3690    url4
Marry   2015-11-12 03:14:30 NULL    NULL    url5
Peter   2015-10-12 01:10:00 2015-10-12 01:15:10 310 url1
Peter   2015-10-12 01:15:10 2015-10-12 01:16:40 90  url2
Peter   2015-10-12 01:16:40 2015-10-12 02:13:00 3380    url3
Peter   2015-10-12 02:13:00 2015-10-12 03:14:30 3690    url4
Peter   2015-10-12 03:14:30 NULL    NULL    url5

• 這里有空的情況，也就是沒有獲取到離開時間，這要看實際業(yè)務(wù)怎么定義了，如果算到23點，太長了。

2 Lag

LAG(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往上第n行值第一個參數(shù)為列名，第二個參數(shù)為往上第n行（可選，默認為1），第三個參數(shù)為默認值（當(dāng)往上第n行為NULL時候，取默認值，如不指定，則為NULL）可以用來做一些時間的維護，如上一次登錄時間。

　場景

用戶Peter在瀏覽網(wǎng)頁，在某個時刻，Peter點進了某個頁面，過一段時間后，Peter又進入了另外一個頁面，如此反復(fù)，那怎么去統(tǒng)計Peter在某個特定網(wǎng)頁的停留時間呢，又或是怎么統(tǒng)計某個網(wǎng)頁用戶停留的總時間呢？

create table test.user_log(
    userid string,
    time string,
    url string
) row format delimited fields terminated by '\t';

使用load命令將如下測試數(shù)據(jù)導(dǎo)入：

Peter   2015-10-12 01:10:00 url1
Peter   2015-10-12 01:15:10 url2
Peter   2015-10-12 01:16:40 url3
Peter   2015-10-12 02:13:00 url4
Peter   2015-10-12 03:14:30 url5
Marry   2015-11-12 01:10:00 url1
Marry   2015-11-12 01:15:10 url2
Marry   2015-11-12 01:16:40 url3
Marry   2015-11-12 02:13:00 url4
Marry   2015-11-12 03:14:30 url5

數(shù)據(jù)說明：Peter 2015-10-12 01:10:00 url1 ，表示Peter在2015-10-12 01:10:00進入了網(wǎng)頁url2，即記錄的是進入網(wǎng)頁的時間。

select userid,
time etime,
lag(time, 1, '1970-01-01 00:00:00') over(partition by userid order by time) stime,
url 
from test.user_log;

這里etime是結(jié)束時間，stime是開始時間，結(jié)果：

Marry   2015-11-12 01:10:00 1970-01-01 00:00:00 url1
Marry   2015-11-12 01:15:10 2015-11-12 01:10:00 url2
Marry   2015-11-12 01:16:40 2015-11-12 01:15:10 url3
Marry   2015-11-12 02:13:00 2015-11-12 01:16:40 url4
Marry   2015-11-12 03:14:30 2015-11-12 02:13:00 url5
Peter   2015-10-12 01:10:00 1970-01-01 00:00:00 url1
Peter   2015-10-12 01:15:10 2015-10-12 01:10:00 url2
Peter   2015-10-12 01:16:40 2015-10-12 01:15:10 url3
Peter   2015-10-12 02:13:00 2015-10-12 01:16:40 url4
Peter   2015-10-12 03:14:30 2015-10-12 02:13:00 url5

計算總時間，只需要用結(jié)束時間 - 開始時間，然后分組累加即可。

select userid,
UNIX_TIMESTAMP(time, 'yyyy-MM-dd HH:mm:ss') - 
UNIX_TIMESTAMP(lag(time, 1, '1970-01-01 00:00:00') over(partition by userid order by time), 'yyyy-MM-dd HH:mm:ss'),
url 
from test.user_log;

結(jié)果

Marry   1447290600  url1
Marry   310 url2
Marry   90  url3
Marry   3380    url4
Marry   3690    url5
Peter   1444612200  url1
Peter   310 url2
Peter   90  url3
Peter   3380    url4
Peter   3690    url5

因為有兩個我將默認值置為了1970-01-01，所以算出來比較大，實際工作中需要按照實際情況處理。

作者：柯廣的網(wǎng)絡(luò)日志

微信公眾號：Java大數(shù)據(jù)與數(shù)據(jù)倉庫