文章目錄

• • • • 1.累計(jì)計(jì)算窗口函數(shù)
      • • • 1）.sum（...）over（...）
          • 2）.avg(...) over(...)
          • 3.語(yǔ)法總結(jié):
      • 2.分區(qū)排序窗口函數(shù):
      • • row_number() over(partition by ...A...order by...B...) 1,2,3,4
        • rank() over(partition by ...A...order by...B...) 1,2,2,4
        • dense_rank() over(partition by ...A...order by...B...) 1,2,2,3
      • 3.分組排序窗口函數(shù)
      • • ntile(n) over(......)
      • 4.偏移分析窗口函數(shù)
      • • lag(...)over(...),lead(...)over(...)

1、掌握sum(),avg()用于累計(jì)計(jì)算的窗口函數(shù)；
2、掌握row_number(),rank()用于排序的窗口函數(shù)；
3、掌握ntile()用于分組查詢的窗口函數(shù)；
4、掌握l(shuí)ag(),lead()偏移分析窗口函數(shù)

1.累計(jì)計(jì)算窗口函數(shù)

1）.sum（…）over（…）

求截止某月的數(shù)值

user_trade列名舉例

user_name	Amy，Dennis
piece	購(gòu)買數(shù)量
pay_amount	支付金額
goods_category	food，clothes，book，computer，electronics，shoes
pay_time	132265412，時(shí)間戳
dt	partition，‘yyyy-mm-dd’

求2018年每月的支付總額和當(dāng)年累計(jì)支付總額

select a.month,a.pay_amount,sum(a.pay_amount) over(order by a.month) from(select month(dt) month,sum(pay_amount) pay_amountfrom user_tradewhere year(dt)=2018group by month(dt))a;

2017-2018年每月的支付總額和當(dāng)年累積支付總額（如果用上面的方式會(huì)把之前月份的都匯總，無(wú)法實(shí)現(xiàn)）

select a.year,a.month,a.pay_amount,sum(a.pay_amount) over(partition by a.year order by a.month) -- over:指定累計(jì)計(jì)算的條件，這里須正確分組 from (select year(dt) year,month(dt) month,sum(pay_amount) pay_amountfrom user_tradewhere year(dt) in (2017,2018)group by year(dt),month(dt))a;

注：
1.partition by 起到分組的作用；
2.order by按照什么順序進(jìn)行累加，升序ASC,降序DESC,默認(rèn)升序
3.常見(jiàn)錯(cuò)誤：分組沒(méi)有限制正確（partition by 后多加了a.month）

2）.avg(…) over(…)

2018年每個(gè)月的近三個(gè)月移動(dòng)平均支付金額

select a.month,a.pay_amount,avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row) from (select month(dt)month,sum(pay_amount)pay_amountfrom user_tradewhere year(dt)=2018group by month(dt))a;

說(shuō)明:我們用rows between 2 preceding and current row來(lái)限制計(jì)算移動(dòng)平均的平均范圍，本語(yǔ)句含義是包含本行及前兩行，這個(gè)就是我們題目中要求的近三月的寫法

3.語(yǔ)法總結(jié):

sum(…A…) over(partition by …B… order by…C…rows between…D1…and…D2…)
avg(…A…) over(partition by …B… order by…C…rows between…D1…and…D2…)
A:需要被加工的字段名稱
B:分組的字段名稱
C:排序的字段名稱
D:計(jì)算的行數(shù)范圍
rows between unbounded preceding and current row–包括本行和之前所有的行
rows between current row and unbounded following–包括本行和之后所有的行
rows between 3 preceding and current row --包括本行以內(nèi)和前三行
rows between 3 preceding and 1 following–從前三行到下一行(5行)
拓展：
max(…) over(partition by …order by … rows between… and…)
min(…) over(partition by …order by … rows between… and…)

2.分區(qū)排序窗口函數(shù):

這三個(gè)函數(shù)的作用都是返回相應(yīng)規(guī)則的排序序號(hào)

row_number() over(partition by …A…order by…B…) 1,2,3,4

rank() over(partition by …A…order by…B…) 1,2,2,4

dense_rank() over(partition by …A…order by…B…) 1,2,2,3

2019年1月，用戶購(gòu)買商品品類數(shù)量的排名

select user_name,count(distinct goods_category),row_number() over(order by count(distinct goods_category)),rank() over(order by count(distinct goods_category)),dense_rank() over(order by count(distinct goods_category)) from user_trade where substr(dt,1,7)='2019-01' group by user_name;

選擇2019年支付金額排名在第10,20,30名的用戶:

select a.user_name,a.pay_amount,a.rank from(select user_name,sum(pay_amount)pay_amount,rank() over(order by sum(pay_amount)desc)rankfrom user_tradewhere year(dt)=2019group by user_name)awhere a.rank in (10,20,30);

3.分組排序窗口函數(shù)

ntile(n) over(…)

ntile(n) over(partition by…A…order by…B…)
n:切分的片數(shù)
A:分組的字段名稱
B:排序的字段名稱
ntile(n),用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值
ntile不支持row between，比如ntile(2) over(partition by …order by…rows between 3 preceding and current row)
如果切片不均勻，默認(rèn)增加第一個(gè)切片的分布

將2019年1月份支付用戶，按照支付金額分成5組:

select user_name,sum(pay_amount)pay_amount,ntile(5) over(order by sum(pay_amount) desc)level from user_trade where substr(dt,1,7)='2019-01' group by user_name;

選出2019年退款金額排名前10%的用戶:

user_refund列名舉例

user_name	Amy，Dennis
refund_piece	退款件數(shù)
refund_amount	退款金額
refund_time	134854654，時(shí)間戳
dt	partition，‘yyyy-mm-dd’

select a.user_name,a.refund_amount,a.level from(select user_name,sum(refund_amount)refund_amount,ntile(10) over(order by sum(refund_amount)desc)levelfrom user_refundwhere year(dt)=2019group by user_name)a where a.level=1;

4.偏移分析窗口函數(shù)

lag(…)over(…),lead(…)over(…)

lag和lead分析函數(shù)可以在通一次查詢中取出同一字段的前N行的數(shù)據(jù)(lag)和后N行的數(shù)據(jù)(lead)作為獨(dú)立的列。

在實(shí)際應(yīng)用中，若要用到取今天和昨天的某字段差值時(shí)，lag和lead函數(shù)的應(yīng)用就顯得尤為重要。當(dāng)然，
這種操作可以用表的自連接實(shí)現(xiàn)，但是lag和lead與left join、right join等自連接相比，效率更高，SQL更簡(jiǎn)潔。

lag(exp_str,offset,defval) over(partition by …order by…)
lead(exp_str,offset,defval) over(partition by …order by…)
– exp_str是字段名稱。
– offset是偏移量，即是上1個(gè)或上N個(gè)的值，假設(shè)當(dāng)前行在表中排在第5行，offset為3，則表示我們所要找的數(shù)據(jù)行就是表中的第2行(即5-3=2)。offset默認(rèn)值為1。
– defval默認(rèn)值，當(dāng)兩個(gè)函數(shù)取上N/下N個(gè)值，當(dāng)在表中從當(dāng)前行位置向前數(shù)N行已經(jīng)超出了表的范圍時(shí)，lag()函數(shù)
– 將defval這個(gè)參數(shù)值作為函數(shù)的返回值，若沒(méi)有指定默認(rèn)值，則返回null，那么在數(shù)學(xué)運(yùn)算中，總要給一個(gè)默認(rèn)值才不會(huì)出錯(cuò)。

Alice和Alexander的各種時(shí)間偏移
lag()實(shí)例：

select user_name,dt,lag(dt,1,dt) over(partition by user_name order by dt),lag(dt) over(partition by user_name order by dt),lag(dt,2,dt) over(partition by user_name order by dt),lag(dt,2) over(partition by user_name order by dt) from user_trade where dt>'0'and user_name in ('Alice','Alexander');

注意：已經(jīng)對(duì)人進(jìn)行分組了，所以不會(huì)由Alice偏移到Alexander，否則沒(méi)有意義

lead()實(shí)例：

select user_name,dt,lead(dt,1,dt) over(partition by user_name order by dt),lead(dt) over(partition by user_name order by dt),lead(dt,2,dt) over(partition by user_name order by dt),lead(dt,2) over(partition by user_name order by dt) from user_trade where dt>'0'and user_name in ('Alice','Alexander');

實(shí)例：支付時(shí)間間隔超過(guò)100天的用戶數(shù)

select count(distinct user_name) from(select user_name,dt, --選出每個(gè)人的支付日期lead(dt) over(partition by user_name order by dt) lead_dtfrom user_trade where dt>'0')a --用的是全量，所以用>0where a.lead_dt is not null and datediff(a.lead_dt,a.dt)>100; -- 大的寫左邊，小的寫右邊

重點(diǎn)練習(xí)
1.每個(gè)城市，不同性別，2018年支付金額最高的top3用戶(使用user_trade和user_info兩個(gè)表)

user_info列名舉例

user_id	10001,10002
user_name	Amy,Dennis
sex	[male,female]
age	[13,70]
city	beijing,shanghai
fistactivetime	2019-04-19 15:40:00
level	[1,10]
extra1	string類型:{“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}
extra2	map<string.string>類型: {“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphoneX”}

user_trade列名舉例

user_name	Amy,Dennis
piece	購(gòu)買數(shù)量
price	價(jià)格
pay_amount	支付金額
goods_category	food,clothes,book,computer,electronics,shoes
pay_time	2412521561,時(shí)間戳
dt	partition,‘yyyy-mm-dd’

select c.user_name,c.city,c.sex,c.pay_amount,c.rank from(select a.user_name,b.city,b.sex,a.pay_amount,row_number() over(partition by b.city,b.sex order by a.pay_amount desc) rank from(select user_name,sum(pay_amount) pay_amountfrom user_tradewhere year(dt)=2018group by user_name)aleft join user_info b on a.user_name=b.user_name)c where c.rank<=3; -- 使用row_number是因?yàn)樗梢跃_限制可以具體出來(lái)多少行，使用dense_rank和rank都不行

每個(gè)手機(jī)品牌退款金額前25%的用戶(使用user_refund和user_info兩個(gè)表)

user_refund列名舉例

user_name	Amy,Dennis
refund_piece	退款件數(shù)
refund_amount	退款金額
refund_time	123412521,時(shí)間戳
dt	partition,‘yyyy-mm-dd’

select * from (select a.user_name,extra2['b.phonebrand'] as phonebrand,a.refund_amount,ntile(4) over(partition by extra2['b.phonebrand'] order by a.refund_amount desc)levelfrom(select user_name,sum(refund_amount) refund_amountfrom user_refundwhere dt>'0'group by user_name) aleft join user_info b on a.user_name=b.user_name)c where c.level=1;

總結(jié)

以上是生活随笔為你收集整理的HiveSQL窗口函数的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。