LAG的使用：

LAG（col，n，DEFAULT） 用于統(tǒng)計窗口內(nèi)往上第n行值。

第一個參數(shù)為列名，第二個參數(shù)為往上第n行（可選，默認(rèn)為1），第三個參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時候，取默認(rèn)值，如不指定，則為NULL）

SELECT cookieid，
createtime，
url，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn，
LAG（createtime，1，＇1970－01－01 00：00：00＇） OVER（PARTITION BY cookieid ORDER BY createtime） AS last＿1＿time，
LAG（createtime，2） OVER（PARTITION BY cookieid ORDER BY createtime） AS last＿2＿time
FROM user＿url；

結(jié)果如下：

解釋：

last＿1＿time： 指定了往上第1行的值，default為＇1970－01－01 00：00：00＇  
                cookie1第一行，往上1行為NULL，因此取默認(rèn)值 1970－01－01 00：00：00
                cookie1第三行，往上1行值為第二行值，2021－06－10 10：00：02
                cookie1第六行，往上1行值為第五行值，2021－06－10 10：50：01
last＿2＿time： 指定了往上第2行的值，為指定默認(rèn)值
      cookie1第一行，往上2行為NULL
      cookie1第二行，往上2行為NULL
      cookie1第四行，往上2行為第二行值，2021－06－10 10：00：02
      cookie1第七行，往上2行為第五行值，2021－06－10 10：50：01

LEAD的使用：

與LAG相反

LEAD（col，n，DEFAULT） 用于統(tǒng)計窗口內(nèi)往下第n行值。

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

SELECT cookieid，
createtime，
url，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn，
LEAD（createtime，1，＇1970－01－01 00：00：00＇） OVER（PARTITION BY cookieid ORDER BY createtime） AS next＿1＿time，
LEAD（createtime，2） OVER（PARTITION BY cookieid ORDER BY createtime） AS next＿2＿time
FROM user＿url；

結(jié)果如下：

FIRST＿VALUE的使用：

取分組內(nèi)排序后，截止到當(dāng)前行，第一個值。

SELECT cookieid，
createtime，
url，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn，
FIRST＿VALUE（url） OVER（PARTITION BY cookieid ORDER BY createtime） AS first1
FROM user＿url；

結(jié)果如下：

LAST＿VALUE的使用：

取分組內(nèi)排序后，截止到當(dāng)前行，最后一個值。

SELECT cookieid，
createtime，
url，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn，
LAST＿VALUE（url） OVER（PARTITION BY cookieid ORDER BY createtime） AS last1
FROM user＿url；

結(jié)果如下：

如果想要取分組內(nèi)排序后最后一個值，則需要變通一下：

SELECT cookieid，
createtime，
url，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn，
LAST＿VALUE（url） OVER（PARTITION BY cookieid ORDER BY createtime） AS last1，
FIRST＿VALUE（url） OVER（PARTITION BY cookieid ORDER BY createtime DESC） AS last2
FROM user＿url
ORDER BY cookieid，createtime；

注意上述SQL，使用的是 FIRST＿VALUE 的倒序取出分組內(nèi)排序最后一個值！

結(jié)果如下：

此處要特別注意order  by

如果不指定ORDER BY，則進行排序混亂，會出現(xiàn)錯誤的結(jié)果

SELECT cookieid，
createtime，
url，
FIRST＿VALUE（url） OVER（PARTITION BY cookieid） AS first2  
FROM user＿url；

結(jié)果如下：

上述 url2 和 url55 的createtime即不屬于最靠前的時間也不屬于最靠后的時間，所以結(jié)果是混亂的。

4． CUME＿DIST

先創(chuàng)建一張員工薪水表：staff＿salary

CREATE EXTERNAL TABLE staff＿salary （
dept string，
userid string，
sal int
）；

表中加入如下數(shù)據(jù)：

d1，user1，1000
d1，user2，2000
d1，user3，3000
d2，user4，4000
d2，user5，5000
CUME＿DIST的使用：

此函數(shù)的結(jié)果和order by的排序順序有關(guān)系。

CUME＿DIST：小于等于當(dāng)前值的行數(shù)／分組內(nèi)總行數(shù)。  order默認(rèn)順序：正序

比如，統(tǒng)計小于等于當(dāng)前薪水的人數(shù)，所占總?cè)藬?shù)的比例。

SELECT
dept，
userid，
sal，
CUME＿DIST（） OVER（ORDER BY sal） AS rn1，
CUME＿DIST（） OVER（PARTITION BY dept ORDER BY sal） AS rn2
FROM staff＿salary；

結(jié)果如下：

解釋：

rn1： 沒有partition，所有數(shù)據(jù)均為1組，總行數(shù)為5，
    第一行：小于等于1000的行數(shù)為1，因此，1／5＝0．2
    第三行：小于等于3000的行數(shù)為3，因此，3／5＝0．6
rn2： 按照部門分組，dpet＝d1的行數(shù)為3，
    第二行：小于等于2000的行數(shù)為2，因此，2／3＝0．6666666666666666