目錄一、定義二、語(yǔ)法格式三、分類(lèi)1、聚合類(lèi)2、排序類(lèi)3、偏移分析函數(shù)

對(duì)一個(gè)成熟的數(shù)據(jù)分析師來(lái)說(shuō)，窗口函數(shù)可以大幅提高查詢效率，且SQL代碼優(yōu)雅。

一、定義

窗口可以理解為記錄集合，窗口函數(shù)就是在滿足某種條件的記錄集合上執(zhí)行的特殊函數(shù)。 即：應(yīng)用在窗口內(nèi)的函數(shù)。

靜態(tài)窗口：每條記錄都要在此窗口內(nèi)執(zhí)行函數(shù)，窗口大小都是固定的。

動(dòng)態(tài)窗口：不同的記錄對(duì)應(yīng)著不同的窗口，這種動(dòng)態(tài)變化的窗口叫滑動(dòng)窗口。

二、語(yǔ)法格式函數(shù)名(字段名) over(子句)

over()括號(hào)內(nèi)若不寫(xiě)，則意味著窗口函數(shù)基于滿足where條件的所有行進(jìn)行計(jì)算。

若括號(hào)內(nèi)不為空，則支持以下語(yǔ)法來(lái)設(shè)置窗口。

函數(shù)名(字段名) over(partition by <要分列的組> order by <要排序的列> rows between <數(shù)據(jù)范圍>)

數(shù)據(jù)范圍：

rows between 2 preceding and current row # 取本行和前面兩行rows between unbounded preceding and current row # 取本行和之前所有的行 rows between current row and unbounded following # 取本行和之后所有的行 rows between 3 preceding and 1 following # 從前面三行和下面一行，總共五行 # 當(dāng)order by后面沒(méi)有rows between時(shí)，窗口規(guī)范默認(rèn)是取本行和之前所有的行# 當(dāng)order by和rows between都沒(méi)有時(shí)，窗口規(guī)范默認(rèn)是分組下所有行（rows between unbounded preceding and unbounded following） 三、分類(lèi)1、聚合類(lèi)

聚合窗口函數(shù)與普通聚合函數(shù)的區(qū)別：

普通場(chǎng)景下的聚合函數(shù)是將多條記錄聚合為一條**(多到一)；**窗口函數(shù)是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條**(多到多)**。接下來(lái)通過(guò)解決具體需求來(lái)讓大家更加了解窗口函數(shù)的用法，希望大家閱讀完能動(dòng)手練習(xí)。 先創(chuàng)建user_trade表：-- 現(xiàn)有2018~2020某電商平臺(tái)訂單信息表user_tradecreate table user_trade ( user_name varchar(20) COMMENT '用戶名', piece int COMMENT '購(gòu)買(mǎi)數(shù)量', price double COMMENT '價(jià)格', pay_amount double COMMENT '支付金額', goods_category varchar(20) COMMENT '商品品類(lèi)', pay_time date COMMENT '支付日期');

從navicat中導(dǎo)入以下數(shù)據(jù)源：

user_trade數(shù)據(jù)源：https://gitee.com/hu-weiqing/datasource/blob/master/user_trade.xlsx

數(shù)據(jù)隨機(jī)展示10條如下：

累計(jì)求和：sum()over()-- 需求1: 查詢出2019年每月的支付總額和當(dāng)年累積支付總額 select a.mon,a.pay_amount,sum(a.pay_amount) over(order by a.mon) as sum_amountfrom(select month(a.pay_time) as mon,sum(a.pay_amount) as pay_amountfrom user_trade awhere year(a.pay_time) = '2019'group by month(a.pay_time)) a ;-- 需求2:查詢出2018-2019年每月的支付總額和當(dāng)年累積支付總額select a.*,sum(a.pay_amount) over(partition by a.year order by a.mon) as sum_amountfrom(select year(a.pay_time) as year,month(a.pay_time) as mon,sum(a.pay_amount) as pay_amountfrom user_trade awhere year(a.pay_time) in('2018','2019')group by year(a.pay_time),month(a.pay_time)) a ;

需求1運(yùn)行結(jié)果（部分）

需求2運(yùn)行結(jié)果（部分）

移動(dòng)平均：avg() over()-- 需求3: 查詢出2019年每個(gè)月的近三月移動(dòng)平均支付金額select a.mon,a.pay_amount,avg(a.pay_amount) over(order by a.mon rows between 2 preceding and current row) as avg_amountfrom(select month(a.pay_time) as mon,sum(a.pay_amount) as pay_amountfrom user_trade awhere year(a.pay_time) = '2019'group by month(a.pay_time)) a ;

需求3運(yùn)行結(jié)果（部分）

最大/最小值：max()/min() over()-- 需求4: 查詢出每四個(gè)月的最大月總支付金額select a.mon,a.pay_amount,max(a.pay_amount) over(order by a.mon rows between 3 preceding and current row) as max_amountfrom(select SUBSTRING(a.pay_time,1,7) as mon,sum(a.pay_amount) as pay_amountfrom user_trade agroup by SUBSTRING(a.pay_time,1,7))a ;

需求4運(yùn)行結(jié)果（部分）

2、排序類(lèi)row_number()、rank() 和dense_rank()-- 需求4: 查詢出每四個(gè)月的最大月總支付金額select a.mon,a.pay_amount,max(a.pay_amount) over(order by a.mon rows between 3 preceding and current row) as max_amountfrom(select SUBSTRING(a.pay_time,1,7) as mon,sum(a.pay_amount) as pay_amountfrom user_trade agroup by SUBSTRING(a.pay_time,1,7))a ;

需求5運(yùn)行結(jié)果（部分）

row_number()、rank() 和dense_rank() 三種排序函數(shù)的區(qū)別：

row_number：每一行記錄生成一個(gè)序號(hào)，依次排序且不會(huì)重復(fù)。 12345…

rank：跳躍排序，生成的序號(hào)有可能不連續(xù)。11345…

dense_rank：在生成序號(hào)時(shí)是連續(xù)的。11234…

ntile(n)over()

ntile(n)用于將分組數(shù)據(jù)按照順序切分成n片，返回當(dāng)前切片值. n表示切片的數(shù)量; 不支持rows between

-- 需求6: 查詢出將2020年2月的支付用戶，按照支付金額分成5組后的結(jié)果select a.user_name,sum(a.pay_amount) as pay_amount,ntile(5) over(order by sum(a.pay_amount) desc) as levelfrom user_trade awhere SUBSTRING(a.pay_time,1,7) = '2020-02'group by a.user_name;-- 需求7: 查詢出2020年支付金額排名前30%的所有用戶select a.user_name,a.pay_amountfrom (select a.user_name,sum(a.pay_amount) as pay_amount,ntile(10) over(order by sum(a.pay_amount) desc) as levelfrom user_trade awhere year(a.pay_time) = '2020'group by a.user_name) a where a.level in(1,2,3);

需求6運(yùn)行結(jié)果（部分）

需求7運(yùn)行結(jié)果（部分）

3、偏移分析函數(shù)lag() over()向上偏移

lag(exp_str,offset,defval) exp_str：字段名 offset：偏移量 defval：默認(rèn)值。當(dāng)向上偏移了offset行已經(jīng)超出了表的范圍時(shí)，lag()函數(shù)將defval這個(gè)參數(shù)值作為函數(shù)的返回值，若沒(méi)有指定默認(rèn)值，則返回NULL。

-- 需求8: 查詢出King和West的時(shí)間偏移(前N行)select a.user_name,a.pay_time,lag(a.pay_time,1,a.pay_time) over(partition by a.user_name order by a.pay_time) as lag1,-- 沒(méi)有傳入偏移量，那么默認(rèn)就是1，找不到的話，此處也沒(méi)有給默認(rèn)值，為nulllag(a.pay_time) over(partition by a.user_name order by a.pay_time) as lag2,lag(a.pay_time,2,a.pay_time) over(partition by a.user_name order by a.pay_time) as lag3,lag(a.pay_time,2) over(partition by a.user_name order by a.pay_time) as lag4from user_trade a where a.user_name in('King','West');

需求8運(yùn)行結(jié)果

lead() over()向下偏移

用法同lag()over()函數(shù)。

補(bǔ)充練習(xí)：

-- 需求9: 查詢出支付時(shí)間間隔超過(guò)100天的用戶數(shù)select count(distinct a.user_name)from (select a.user_name,a.pay_time,lag(a.pay_time) over(partition by a.user_name order by a.pay_time) as lgfrom user_trade a ) a where DATEDIFF(a.pay_time,a.lg) >100;# 需求9運(yùn)行結(jié)果為180-- 需求10: 查詢出每年支付時(shí)間間隔最長(zhǎng)的用戶select c.years,c.user_name,c.pay_days from(select b.years,b.user_name,datediff(b.pay_time,b.lg) as pay_days,rank() over(partition by b.years order by datediff(b.pay_time,b.lg) desc) as rk from (select year(a.pay_time) as years,a.user_name,a.pay_time,lag(a.pay_time) over(partition by a.user_name,year(a.pay_time) order by a.pay_time) as lgfrom user_trade a ) b where b.lg is not null) c where c.rk = 1;

需求10運(yùn)行結(jié)果

窗口函數(shù)在數(shù)據(jù)分析師的工作中應(yīng)用非常廣，如果不會(huì)窗口函數(shù)，很可能同樣的需求用普通表關(guān)聯(lián)寫(xiě)需要關(guān)聯(lián)很多張表，導(dǎo)致性能不好，查詢速度非常慢。

到此這篇關(guān)于深入解析MySQL的窗口函數(shù)的文章就介紹到這了,更多相關(guān)MySQL窗口函數(shù)內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！