Hive 分析函數lead、lag實例應用

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

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

1 LEAD

與LAG相反,LEAD(col,n,DEFAULT) 用于統計窗口內往下第n行值

參數1為列名,參數2為往下第n行(可選,默認為1),參數3為默認值(當往下第n行為NULL時候,取默認值,如不指定,則為NULL)

 場景

用戶Peter在瀏覽網頁,在某個時刻,Peter點進了某個頁面,過一段時間后,Peter又進入了另外一個頁面,如此反復,那怎么去統計Peter在某個特定網頁的停留時間呢,又或是怎么統計某個網頁用戶停留的總時間呢?

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

使用load命令將如下測試數據導入:

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

數據說明:Peter 2015-10-12 01:10:00 url1 ,表示Peter在2015-10-12 01:10:00進入了網頁url2,即記錄的是進入網頁的時間。

分析

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

獲取用戶在某個頁面停留的起始與結束時間:

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

stime就是進入頁面時間,etime就是離開頁面時間,結果是這樣的:

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;
SQL

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

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
  • 這里有空的情況,也就是沒有獲取到離開時間,這要看實際業務怎么定義了,如果算到23點,太長了。

2 Lag

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

 場景

用戶Peter在瀏覽網頁,在某個時刻,Peter點進了某個頁面,過一段時間后,Peter又進入了另外一個頁面,如此反復,那怎么去統計Peter在某個特定網頁的停留時間呢,又或是怎么統計某個網頁用戶停留的總時間呢?

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

使用load命令將如下測試數據導入:

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

數據說明:Peter 2015-10-12 01:10:00 url1 ,表示Peter在2015-10-12 01:10:00進入了網頁url2,即記錄的是進入網頁的時間。

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;
SQL

這里etime是結束時間,stime是開始時間,結果:

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

計算總時間,只需要用結束時間 - 開始時間,然后分組累加即可。

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;
SQL

結果

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,所以算出來比較大,實際工作中需要按照實際情況處理。




作者:柯廣的網絡日志

微信公眾號:Java大數據與數據倉庫