Hive 分析函式lead、lag例項應用

柯廣發表於2020-12-17
Hive函式

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

使用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;

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;

這裡展示出了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';

使用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;

這裡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;

結果

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

相關文章