Lag和Lead分析函數(shù)可以在同一次查詢中取出同一字段的后N行的數(shù)據(jù)(Lag)和前N行的數(shù)據(jù)(Lead)作為獨立的列湾趾。
這種操作可以代替表的自聯(lián)接绽昼，并且LAG和LEAD有更高的效率唯鸭，其中over()表示當前查詢的結(jié)果集對象，括號里面的語句則表示對這個結(jié)果集進行處理硅确。
1 LEAD
與LAG相反目溉，LEAD(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往下第n行值
參數(shù)1為列名，參數(shù)2為往下第n行（可選菱农，默認為1）缭付，參數(shù)3為默認值（當往下第n行為NULL時候，取默認值循未，如不指定陷猫，則為NULL）
場景
用戶Peter在瀏覽網(wǎng)頁，在某個時刻只厘，Peter點進了某個頁面烙丛，過一段時間后，Peter又進入了另外一個頁面羔味，如此反復河咽，那怎么去統(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ù)導入：
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è)務怎么定義了俏站，如果算到23點，太長了痊土。
2 Lag
LAG(col,n,DEFAULT) 用于統(tǒng)計窗口內(nèi)往上第n行值第一個參數(shù)為列名乾翔，第二個參數(shù)為往上第n行（可選，默認為1）施戴，第三個參數(shù)為默認值（當往上第n行為NULL時候反浓，取默認值，如不指定赞哗，則為NULL）可以用來做一些時間的維護雷则，如上一次登錄時間。
場景
用戶Peter在瀏覽網(wǎng)頁肪笋，在某個時刻月劈，Peter點進了某個頁面，過一段時間后藤乙，Peter又進入了另外一個頁面猜揪，如此反復，那怎么去統(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ù)導入：
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公般，所以算出來比較大，實際工作中需要按照實際情況處理。
