Hive窗口函數(shù)詳解—及3套案例練習(xí)

可愛的貓

目錄：
一、hive窗口函數(shù)語(yǔ)法
----over()窗口函數(shù)的語(yǔ)法結(jié)構(gòu)
----常與over()一起使用的分析函數(shù)
二、hive窗口函數(shù)練習(xí)28道題

視頻錄制好了，建議大家邊看視頻邊閱讀文章，文章中的窗口函數(shù)練習(xí)題也有相應(yīng)的視頻。點(diǎn)擊藍(lán)色字體可跳轉(zhuǎn)到視頻。

前言：我們?cè)趯W(xué)習(xí)hive窗口函數(shù)的時(shí)候，一定要先了解窗口函數(shù)的結(jié)構(gòu)。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法，如果這樣做，永遠(yuǎn)也掌握不到窗口函數(shù)的核心，當(dāng)然我剛開始的時(shí)候也是這樣做的，包括去年自己在接觸ORACLE分析函數(shù)時(shí)也是這樣搜索。

還好我比較頑強(qiáng)，在HIVE窗口函數(shù)問題上折騰了半個(gè)月、看了很多文章后才知道over()才是窗口函數(shù)，而sum、row_number、count只是與over()搭配的分析函數(shù)，當(dāng)然除了這三個(gè)函數(shù)還有其他的函數(shù)。

一、hive窗口函數(shù)語(yǔ)法

在前言中我們已經(jīng)說了avg()、sum()、max()、min()是分析函數(shù)，而over()才是窗口函數(shù)，下面我們來看看over()窗口函數(shù)的語(yǔ)法結(jié)構(gòu)、及常與over()一起使用的分析函數(shù)

1、over()窗口函數(shù)的語(yǔ)法結(jié)構(gòu)
2、常與over()一起使用的分析函數(shù)
3、窗口函數(shù)總結(jié)

1、over()窗口函數(shù)的語(yǔ)法結(jié)構(gòu)

分析函數(shù) over(partition by 列名 order by 列名 rows between 開始位置 and 結(jié)束位置)

over()窗口函數(shù)由三部分組成：包括分區(qū)partition by 列名、排序order by 列名、從分區(qū)中選擇指定的多條記錄rows between 開始位置 and 結(jié)束位置【也叫窗口幀】。
我們?cè)谑褂胦ver()窗口函數(shù)時(shí)，上面三個(gè)函數(shù)可組合使用也可以不使用。

over()函數(shù)中如果不使用這三個(gè)函數(shù)，窗口大小是針對(duì)查詢產(chǎn)生的所有數(shù)據(jù)，如果指定了分區(qū)，窗口大小是針對(duì)每個(gè)分區(qū)的數(shù)據(jù)。

1.1、over()函數(shù)中的三個(gè)函數(shù)講解

1）partition by分區(qū)：
partition by可理解為group by 分組。over(partition by 列名)搭配分析函數(shù)時(shí)，分析函數(shù)按照每一組每一組的數(shù)據(jù)進(jìn)行計(jì)算的。

2）order by排序：
order by是排序的意思，針對(duì)該窗口

3）窗口幀
窗口幀用于從分區(qū)中選擇指定的多條記錄，也就是對(duì)分區(qū)中的數(shù)據(jù)范圍進(jìn)行限定，供分析函數(shù)處理。Hive 提供了兩種定義窗口幀的形式：ROWS 和 RANGE。兩種類型都需要配置上界和下界。

例如：rows between 開始位置 and 結(jié)束位置
比如第一行到當(dāng)前行ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示從起點(diǎn)到當(dāng)前行），常用該窗口來計(jì)算累加。

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：當(dāng)前行
UNBOUNDED：起點(diǎn)（一般結(jié)合PRECEDING，F(xiàn)OLLOWING使用）
UNBOUNDED PRECEDING 表示該窗口最前面的行（起點(diǎn)）
UNBOUNDED FOLLOWING：表示該窗口最后面的行（終點(diǎn)）
比如說：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示從起點(diǎn)到當(dāng)前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到當(dāng)前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示當(dāng)前行到終點(diǎn)）

因?yàn)閞ows between工作中使用比較少，比較難理解，這里舉幾個(gè)案例。

案例1：求當(dāng)前日期銷售額和后面所有日期的銷售額總和、以及當(dāng)前日期銷售額和前面所有日期的銷售額總和

SELECT  
  sid,  
  day_time,  --時(shí)間
  sales_volume,   --銷售額
--1）當(dāng)前日期銷售額和后面所有日期的銷售額總和
-- unbounded following 最后一行
  sum(sales_volume) over(rows between current row and unbounded following) sum_sales ，

--2）求當(dāng)前日期銷售額和前面所有日期的銷售額總和
-- unbounded preceding就是第一行數(shù)據(jù),current row當(dāng)前行
-- 這個(gè)是累計(jì)求和，好像通過 sum()over(partition by 字段 order by 字段)也可以實(shí)現(xiàn)，驗(yàn)證后補(bǔ)充
sum(sales_volume) over(rows between unbounded preceding and current row) sum_sales2  
FROM 
dw_sale_data;

rows between current row and unbounded following
這里面current row就是指當(dāng)前行,unbounded following就是指最后一行,unbounded preceding就是第一行數(shù)據(jù)。
and前面和后面的關(guān)系就是范圍,從and后面的到and前面,但是and前面是不可以使用unbounded following的,這樣使用是錯(cuò)誤的。

案例2：求當(dāng)前日期和后面兩天銷售額的總和，以及當(dāng)前日期和前兩天銷售額的總和

SELECT  
sid,   --訂單id
day_time,   --時(shí)間
sales_volume,   --銷售額
-- 1）求當(dāng)前日期和后面兩天銷售額的總和,
-- current row是指當(dāng)前行,2 following就是后兩行數(shù)據(jù)
sum(sales_volume) over(rows between current row and 2 following) sum_sales ，

-- 2）求當(dāng)前日期和前兩天銷售額的總和
-- 1 preceding 就是指上一行數(shù)據(jù),current row就是指當(dāng)前行數(shù)據(jù)
sum(sales_volume) over(rows between 1 preceding and current row) sum_sales2 ，
FROM dw_sale_data;

2、常與over()一起使用的分析函數(shù)：

2.1、聚合類

avg()、sum()、max()、min()

2.2、排名類

row_number()按照值排序時(shí)產(chǎn)生一個(gè)自增編號(hào)，不會(huì)重復(fù)（如：1、2、3、4、5、6）
rank() 按照值排序時(shí)產(chǎn)生一個(gè)自增編號(hào)，值相等時(shí)會(huì)重復(fù)，會(huì)產(chǎn)生空位（如：1、2、3、3、3、6）
dense_rank() 按照值排序時(shí)產(chǎn)生一個(gè)自增編號(hào)，值相等時(shí)會(huì)重復(fù)，不會(huì)產(chǎn)生空位（如：1、2、3、3、3、4）

2.3、其他類

** lag() 、lead() 、ntile(n) 、first_value() 、last_value()**
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）

lag(列名,往前第幾行的數(shù)據(jù),[行數(shù)為null時(shí)的默認(rèn)值，不指定為null]),可以計(jì)算用戶上次購(gòu)買時(shí)間，或者用戶下次購(gòu)買時(shí)間。

2)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）

lead(列名,往后第幾行的數(shù)據(jù),[行數(shù)為null時(shí)的默認(rèn)值，不指定為null])

3)ntile(n):
NTILE(n)，用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值

把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中，各個(gè)組有編號(hào)，編號(hào)從1開始，對(duì)于每一行，ntile返回此行所屬的組的編號(hào).
NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

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 dw_window_data   
ORDER BY cookieid,createtime;

4)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 dw_window_data_02;

5) 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 dw_window_data_02;

3、窗口函數(shù)總結(jié)：

其實(shí)窗口函數(shù)邏輯比較繞，我們可以把窗口理解為對(duì)表中的數(shù)據(jù)進(jìn)行分組，排序等計(jì)算。要真正的理解HIVE窗口函數(shù)，還是要結(jié)合練習(xí)題才行。下面我們開始HIVE窗口函數(shù)的練習(xí)吧！

二、hive窗口函數(shù)練習(xí)28道題

第一套練習(xí)：hive之簡(jiǎn)單窗口函數(shù) over()

1、使用 over() 函數(shù)進(jìn)行數(shù)據(jù)統(tǒng)計(jì), 統(tǒng)計(jì)每個(gè)用戶及表中數(shù)據(jù)的總數(shù)
2、求用戶明細(xì)并統(tǒng)計(jì)每天的用戶總數(shù)
3、計(jì)算從第一天到現(xiàn)在的所有 score 大于80分的用戶總數(shù)
4、計(jì)算每個(gè)用戶到當(dāng)前日期分?jǐn)?shù)大于80的天數(shù)

測(cè)試數(shù)據(jù)

20191020,11111,85
20191020,22222,83
20191020,33333,86
20191021,11111,87
20191021,22222,65
20191021,33333,98
20191022,11111,67
20191022,22222,34
20191022,33333,88
20191023,11111,99
20191023,22222,33

建表并導(dǎo)入數(shù)據(jù)：

create table test_window
(logday string,    #logday時(shí)間
userid string, 
score int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#加載數(shù)據(jù)
load data local inpath '/home/xiaowangzi/hive_test_data/test_window.txt' into table test_window;

我們先看下表中的數(shù)據(jù)：

select  *  from test_window;
test_window
1、使用 over() 函數(shù)進(jìn)行數(shù)據(jù)統(tǒng)計(jì), 統(tǒng)計(jì)每個(gè)用戶及表中數(shù)據(jù)的總數(shù)
select *, count(userid)over() as total  from  test_window;

這里使用 over() 與 select count(*) 有相同的作用，好處就是，在需要計(jì)算總數(shù)時(shí)不用再進(jìn)行一次關(guān)聯(lián)。

2、求用戶明細(xì)并統(tǒng)計(jì)每天的用戶總數(shù)

可以使用 partition by 按日期列對(duì)數(shù)據(jù)進(jìn)行分區(qū)處理，如：over(partition by logday)

select  *,count()over(partition by logday)as day_total from  test_window;

求每天的用戶數(shù)可以使用select logday, count(userid) from recommend.test_window group by logday，但是當(dāng)想要得到 userid 信息時(shí)，這種用法的優(yōu)勢(shì)就很明顯。

3、計(jì)算從第一天到現(xiàn)在的所有 score 大于80分的用戶總數(shù)

此時(shí)簡(jiǎn)單的分區(qū)不能滿足需求，需要將 order by 和 窗口定義結(jié)合使用。

select  *,count()over(order by logday rows between unbounded preceding and current row)as total 
from  test_window
where score > 80;


通過 over() 計(jì)算出按日期的累加值。
其實(shí)自己剛開始的時(shí)候就計(jì)算我思路是錯(cuò)了，我就想的是不用累加，直接select *,count()over()as total from test_window where score > 80;這樣計(jì)算，如果這樣計(jì)算的話只會(huì)顯示表中所有大于80的人數(shù)，如果我想看20191021或者看20191022的人數(shù)看不見。

4、計(jì)算每個(gè)用戶到當(dāng)前日期分?jǐn)?shù)大于80的天數(shù)
select *,
count()over(partition  by userid order by logday rows between unbounded preceding and current row) as total
from test_window
where score > 80  order by logday, userid;

第二套練習(xí)

1、查詢?cè)?017年4月份購(gòu)買過的顧客及總?cè)藬?shù)
2、查詢顧客的購(gòu)買明細(xì)及月購(gòu)買總額
3、查詢顧客的購(gòu)買明細(xì)及到目前為止每個(gè)顧客購(gòu)買總金額
4、查詢顧客上次的購(gòu)買時(shí)間----lag()over()偏移量分析函數(shù)的運(yùn)用
5、查詢前20%時(shí)間的訂單信息

測(cè)試數(shù)據(jù)

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

建表并加載數(shù)據(jù)

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#加載數(shù)據(jù)
load  data local inpath "/home/xiaowangzi/hive_test_data/business.txt" into table business;

查看表數(shù)據(jù)

select   *  from  business;
查看表數(shù)據(jù)

1、查詢?cè)?017年4月份購(gòu)買過的顧客及總?cè)藬?shù)

分析：按照日期過濾、分組count求總?cè)藬?shù)(分組為什么不是用group by？自己思考)

select   *,count()over() as total  from  business 
where substr(orderdate,1,7) = '2017-04';

2、查詢顧客的購(gòu)買明細(xì)及月購(gòu)買總額

是計(jì)算每月每個(gè)用戶的消費(fèi)金額

select 
*,
sum(cost) over(partition by name,substr(orderdate,1,7)) total_amount
from 
business;

3、查詢顧客的購(gòu)買明細(xì)及到目前為止每個(gè)顧客購(gòu)買總金額

分析：按照顧客分組、日期升序排序、組內(nèi)每條數(shù)據(jù)將之前的金額累加

select 
*,
sum(cost) over(partition by name order  by  orderdate 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_amount
from 
business;

4、查詢顧客上次的購(gòu)買時(shí)間----lag()over()偏移量分析函數(shù)的運(yùn)用

lag()over()偏移量分析函數(shù)的運(yùn)用

select 
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate) last_date
from 
business;
image.png

5、查詢前20%時(shí)間的訂單信息

select  *  
from 
(select   *,
ntile(5)over(order  by  cost)sortgroup_num from  business)t 
where t.sortgroup_num = 1;

第三套練習(xí)：

1、每門學(xué)科學(xué)生成績(jī)排名(是否并列排名、空位排名三種實(shí)現(xiàn))
2、每門學(xué)科成績(jī)排名top n的學(xué)生

原始數(shù)據(jù)(學(xué)生成績(jī)信息)

name    subject score
孫悟空 語(yǔ)文  87
孫悟空 數(shù)學(xué)  95
孫悟空 英語(yǔ)  68
大海  語(yǔ)文  94
大海  數(shù)學(xué)  56
大海  英語(yǔ)  84
宋宋  語(yǔ)文  64
宋宋  數(shù)學(xué)  86
宋宋  英語(yǔ)  84
婷婷  語(yǔ)文  65
婷婷  數(shù)學(xué)  85
婷婷  英語(yǔ)  78

建表并加載數(shù)據(jù)

create table score
(
name string,
subject string, 
score int
) row format delimited fields terminated by "\t";

#加載數(shù)據(jù)
load data local inpath '/home/xiaowangzi/hive_test_data/score.txt' into table score;

查看數(shù)據(jù)

select  *  from  score;

1、每門學(xué)科學(xué)生成績(jī)排名(是否并列排名、空位排名三種實(shí)現(xiàn))

select  *,
row_number()over(partition by subject order by score desc),
rank()over(partition by subject order by score desc),
dense_rank()over(partition by subject order by score desc)
from score

2、每門學(xué)科成績(jī)排名top n的學(xué)生

select 
*
from 
(
select 
*,
row_number() over(partition by subject order by score desc) rmp
from score
) t
where t.rmp<=3;

參考文章1：Hive窗口函數(shù)案例詳解
參考文章2：hive表之簡(jiǎn)單窗口函數(shù) over()

最后編輯于 ：
?著作權(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)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

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

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