Hive筆記8-窗口分析函數(shù)

hive分析窗口函數(shù)

基礎(chǔ)函數(shù)

SUM、AVG、MIN、MAX

SELECT cookieid,
createtime,
pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默認(rèn)為從起點(diǎn)到當(dāng)前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --從起點(diǎn)到當(dāng)前行，結(jié)果同pv1 
SUM(pv) OVER(PARTITION BY cookieid) AS pv3,                             --分組內(nèi)所有行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,   --當(dāng)前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,    --當(dāng)前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6   ---當(dāng)前行+往后所有行  
FROM lxw1234;
 
cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6 
-----------------------------------------------------------------------------
cookie1  2015-04-10      1       1       1       26      1       6       26
cookie1  2015-04-11      5       6       6       26      6       13      25
cookie1  2015-04-12      7       13      13      26      13      16      20
cookie1  2015-04-13      3       16      16      26      16      18      13
cookie1  2015-04-14      2       18      18      26      17      21      10
cookie1  2015-04-15      4       22      22      26      16      20      8
cookie1  2015-04-16      4       26      26      26      13      13      4

序列函數(shù)

序列函數(shù)，NTILE,ROW_NUMBER,RANK,DENSE_RANK

NTILE

NTILE(n)，用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均勻，默認(rèn)增加第一個(gè)切片的分布

SELECT 
cookieid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,    --分組內(nèi)將數(shù)據(jù)分成2片
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,  --分組內(nèi)將數(shù)據(jù)分成3片
NTILE(4) OVER(ORDER BY createtime) AS rn3        --將所有數(shù)據(jù)分成4片
FROM lxw1234 
ORDER BY cookieid,createtime;
 
cookieid day           pv       rn1     rn2     rn3
-------------------------------------------------
cookie1 2015-04-10      1       1       1       1
cookie1 2015-04-11      5       1       1       1
cookie1 2015-04-12      7       1       1       2
cookie1 2015-04-13      3       1       2       2
cookie1 2015-04-14      2       2       2       3
cookie1 2015-04-15      4       2       3       3
cookie1 2015-04-16      4       2       3       4
cookie2 2015-04-10      2       1       1       1
cookie2 2015-04-11      3       1       1       1
cookie2 2015-04-12      5       1       1       2
cookie2 2015-04-13      6       1       2       2
cookie2 2015-04-14      3       2       2       3
cookie2 2015-04-15      9       2       3       4
cookie2 2015-04-16      7       2       3       4

ROW_NUMBER

ROW_NUMBER() –從1開(kāi)始，按照順序，生成分組內(nèi)記錄的序列
–比如，按照pv降序排列，生成分組內(nèi)每天的pv名次
ROW_NUMBER() 的應(yīng)用場(chǎng)景非常多，再比如，獲取分組內(nèi)排序第一的記錄;獲取一個(gè)session中的第一條refer等。

SELECT 
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
FROM lxw1234;
 
cookieid day           pv       rn
------------------------------------------- 
cookie1 2015-04-12      7       1
cookie1 2015-04-11      5       2
cookie1 2015-04-15      4       3
cookie1 2015-04-16      4       4
cookie1 2015-04-13      3       5
cookie1 2015-04-14      2       6
cookie1 2015-04-10      1       7
cookie2 2015-04-15      9       1
cookie2 2015-04-16      7       2
cookie2 2015-04-13      6       3
cookie2 2015-04-12      5       4
cookie2 2015-04-14      3       5
cookie2 2015-04-11      3       6
cookie2 2015-04-10      2       7

RANK 和 DENSE_RANK

—RANK() 生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中留下空位
—DENSE_RANK() 生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中不會(huì)留下空位

SELECT 
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM lxw1234 
WHERE cookieid = 'cookie1';
 
cookieid day           pv       rn1     rn2     rn3 
-------------------------------------------------- 
cookie1 2015-04-12      7       1       1       1
cookie1 2015-04-11      5       2       2       2
cookie1 2015-04-15      4       3       3       3
cookie1 2015-04-16      4       3       3       4
cookie1 2015-04-13      3       5       4       5
cookie1 2015-04-14      2       6       5       6
cookie1 2015-04-10      1       7       6       7
 
rn1: 15號(hào)和16號(hào)并列第3, 13號(hào)排第5
rn2: 15號(hào)和16號(hào)并列第3, 13號(hào)排第4
rn3: 如果相等，則按記錄值排序，生成唯一的次序，如果所有記錄值都相等，或許會(huì)隨機(jī)排吧。

CUME_DIST

–CUME_DIST 小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)
–比如，統(tǒng)計(jì)小于等于當(dāng)前薪水的人數(shù)，所占總?cè)藬?shù)的比例

–CUME_DIST 小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)
–比如，統(tǒng)計(jì)小于等于當(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 lxw1234;
 
dept    userid   sal   rn1       rn2 
-------------------------------------------
d1      user1   1000    0.2     0.3333333333333333
d1      user2   2000    0.4     0.6666666666666666
d1      user3   3000    0.6     1.0
d2      user4   4000    0.8     0.5
d2      user5   5000    1.0     1.0
 
rn1: 沒(méi)有partition,所有數(shù)據(jù)均為1組，總行數(shù)為5，
     第一行：小于等于1000的行數(shù)為1，因此，1/5=0.2
     第三行：小于等于3000的行數(shù)為3，因此，3/5=0.6
rn2: 按照部門(mén)分組，dpet=d1的行數(shù)為3,
     第二行：小于等于2000的行數(shù)為2，因此，2/3=0.6666666666666666

PERCENT_RANK

–PERCENT_RANK 分組內(nèi)當(dāng)前行的RANK值-1/分組內(nèi)總行數(shù)-1

SELECT 
dept,
userid,
sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分組內(nèi)
RANK() OVER(ORDER BY sal) AS rn11,          --分組內(nèi)RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分組內(nèi)總行數(shù)
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM lxw1234;
 
dept    userid   sal    rn1    rn11     rn12    rn2
---------------------------------------------------
d1      user1   1000    0.0     1       5       0.0
d1      user2   2000    0.25    2       5       0.5
d1      user3   3000    0.5     3       5       1.0
d2      user4   4000    0.75    4       5       0.0
d2      user5   5000    1.0     5       5       1.0
 
rn1: rn1 = (rn11-1) / (rn12-1) 
       第一行,(1-1)/(5-1)=0/4=0
       第二行,(2-1)/(5-1)=1/4=0.25
       第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分組，
     dept=d1的總行數(shù)為3
     第一行，(1-1)/(3-1)=0
     第三行，(3-1)/(3-1)=1

LAG

LAG(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往上第n行值
第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往上第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時(shí)候，取默認(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 lxw1234;
 
 
cookieid createtime             url    rn       last_1_time             last_2_time
-------------------------------------------------------------------------------------------
cookie1 2015-04-10 10:00:00     url1    1       1970-01-01 00:00:00     NULL
cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:00:00     NULL
cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:00:02     2015-04-10 10:00:00
cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:03:04     2015-04-10 10:00:02
cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:10:00     2015-04-10 10:03:04
cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 10:50:01     2015-04-10 10:10:00
cookie1 2015-04-10 11:00:00     url7    7       2015-04-10 10:50:05     2015-04-10 10:50:01
cookie2 2015-04-10 10:00:00     url11   1       1970-01-01 00:00:00     NULL
cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:00:00     NULL
cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:00:02     2015-04-10 10:00:00
cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:03:04     2015-04-10 10:00:02
cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:10:00     2015-04-10 10:03:04
cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 10:50:01     2015-04-10 10:10:00
cookie2 2015-04-10 11:00:00     url77   7       2015-04-10 10:50:05     2015-04-10 10:50:01
 
 
last_1_time: 指定了往上第1行的值，default為'1970-01-01 00:00:00'  
             cookie1第一行，往上1行為NULL,因此取默認(rèn)值 1970-01-01 00:00:00
             cookie1第三行，往上1行值為第二行值，2015-04-10 10:00:02
             cookie1第六行，往上1行值為第五行值，2015-04-10 10:50:01
last_2_time: 指定了往上第2行的值，為指定默認(rèn)值
                         cookie1第一行，往上2行為NULL
                         cookie1第二行，往上2行為NULL
                         cookie1第四行，往上2行為第二行值，2015-04-10 10:00:02
                         cookie1第七行，往上2行為第五行值，2015-04-10 10:50:01

LEAD

與LAG相反
LEAD(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往下第n行值
第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往下第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往下第n行為NULL時(shí)候，取默認(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 lxw1234;
 
 
cookieid createtime             url    rn       next_1_time             next_2_time 
-------------------------------------------------------------------------------------------
cookie1 2015-04-10 10:00:00     url1    1       2015-04-10 10:00:02     2015-04-10 10:03:04
cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:03:04     2015-04-10 10:10:00
cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:10:00     2015-04-10 10:50:01
cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:50:01     2015-04-10 10:50:05
cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:50:05     2015-04-10 11:00:00
cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 11:00:00     NULL
cookie1 2015-04-10 11:00:00     url7    7       1970-01-01 00:00:00     NULL
cookie2 2015-04-10 10:00:00     url11   1       2015-04-10 10:00:02     2015-04-10 10:03:04
cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:03:04     2015-04-10 10:10:00
cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:10:00     2015-04-10 10:50:01
cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:50:01     2015-04-10 10:50:05
cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:50:05     2015-04-10 11:00:00
cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 11:00:00     NULL
cookie2 2015-04-10 11:00:00     url77   7       1970-01-01 00:00:00     NULL
 
--邏輯與LAG一樣，只不過(guò)LAG是往上，LEAD是往下。

FIRST_VALUE

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

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 lxw1234;
 
cookieid  createtime            url     rn      first1
---------------------------------------------------------
cookie1 2015-04-10 10:00:00     url1    1       url1
cookie1 2015-04-10 10:00:02     url2    2       url1
cookie1 2015-04-10 10:03:04     1url3   3       url1
cookie1 2015-04-10 10:10:00     url4    4       url1
cookie1 2015-04-10 10:50:01     url5    5       url1
cookie1 2015-04-10 10:50:05     url6    6       url1
cookie1 2015-04-10 11:00:00     url7    7       url1
cookie2 2015-04-10 10:00:00     url11   1       url11
cookie2 2015-04-10 10:00:02     url22   2       url11
cookie2 2015-04-10 10:03:04     1url33  3       url11
cookie2 2015-04-10 10:10:00     url44   4       url11
cookie2 2015-04-10 10:50:01     url55   5       url11
cookie2 2015-04-10 10:50:05     url66   6       url11
cookie2 2015-04-10 11:00:00     url77   7       url11

LAST_VALUE

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

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 lxw1234;
 
 
cookieid  createtime            url    rn       last1  
-----------------------------------------------------------------
cookie1 2015-04-10 10:00:00     url1    1       url1
cookie1 2015-04-10 10:00:02     url2    2       url2
cookie1 2015-04-10 10:03:04     1url3   3       1url3
cookie1 2015-04-10 10:10:00     url4    4       url4
cookie1 2015-04-10 10:50:01     url5    5       url5
cookie1 2015-04-10 10:50:05     url6    6       url6
cookie1 2015-04-10 11:00:00     url7    7       url7
cookie2 2015-04-10 10:00:00     url11   1       url11
cookie2 2015-04-10 10:00:02     url22   2       url22
cookie2 2015-04-10 10:03:04     1url33  3       1url33
cookie2 2015-04-10 10:10:00     url44   4       url44
cookie2 2015-04-10 10:50:01     url55   5       url55
cookie2 2015-04-10 10:50:05     url66   6       url66
cookie2 2015-04-10 11:00:00     url77   7       url77
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容