MySQL窗口函數(shù)的具體使用
之前我給粉絲們搞過個投票,尋找MySQL中那個
最熟悉的陌生人
~~MySQL中哪些技術(shù)點(diǎn)是你既熟悉又陌生的?
前三名和我預(yù)料大差不差,分別是:
1、游標(biāo)2、窗口函數(shù)3、聚簇索引
這三個點(diǎn)雖然平時用得少,但在面試中卻常被問到
。值得一提的是,很多面試官對問題竟然也是一知半解。。
今天我想和你聊聊窗口函數(shù),MySQL從8.0開始支持窗口函數(shù)
,或許你們公司的MySQL版本還無法讓你爽一把,但我建議你要在本地搞一個試試,真香!
好了,廢話不多說,老規(guī)矩,先上開胃小菜,看看今天的測試表數(shù)據(jù)吧。
本文用來演示用的測試表是chh_baozipu
,翻譯過來就是emmm…陳哈哈的包子鋪。悄悄告訴你,哈哥今年盤了個包子鋪賣包子,這張表就是包子鋪這半年的利潤~
mysql> SELECT * from chh_baozipu ; +----+--------------------+-------+---------+ | id | product | sales | month | +----+--------------------+-------+---------+ | 1 | 豬肉大蔥包子 | 600 | 2021-11 | | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | | 7 | 面餡兒包子 | 700 | 2021-11 | | 8 | 面餡兒包子 | 200 | 2021-10 | | 9 | 面餡兒包子 | 300 | 2021-09 | | 10 | 面餡兒包子 | 0 | 2021-08 | | 11 | 面餡兒包子 | 100 | 2021-07 | | 12 | 面餡兒包子 | 200 | 2021-06 | +----+--------------------+-------+---------+ 12 rows in set (0.00 sec)
怎么說?什么時候來我店里,請大家吃面餡兒包子。
一、什么是窗口函數(shù)
1、怎么理解窗口?
其實窗口
的概念是非常重要的,要想學(xué)會窗口函數(shù),可不能只知其一不知其二;我們得搞清楚窗口
代表著啥,才知道什么時候該用它。
拿測試表舉個簡單的例子,統(tǒng)計一下:包子鋪的豬肉大蔥包子這半年截至每月累計利潤
。
SELECT *,SUM(sales) over(ORDER BY `month`) as 累計利潤 from chh_baozipu where product='豬肉大蔥包子';
mysql> SELECT *,SUM(sales) over(ORDER BY `month`) as 累計利潤 from chh_baozipu where product='豬肉大蔥包子'; +----+--------------------+-------+---------+--------------+ | id | product | sales | month | 累計利潤 | +----+--------------------+-------+---------+--------------+ | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | 1000 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 2600 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 3400 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 4400 | | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | 6000 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 6600 | +----+--------------------+-------+---------+--------------+ 6 rows in set (0.00 sec)
從這條SQL可以看出,對于第一行id=6這行的窗口
就是第一行,對于第二行id=5這行的窗口
就是前兩行,以此類推(如下圖)。
可見,窗口就是范圍的意思
,可以理解為一些記錄(行)的集合;窗口函數(shù)也就是在滿足某種條件的記錄集合上執(zhí)行計算
的特殊函數(shù)。
對于每條記錄都要在此窗口內(nèi)執(zhí)行函數(shù),有的函數(shù)隨著記錄不同,窗口大小都是固定的,這種屬于靜態(tài)窗口
;有的函數(shù)則相反,不同的記錄對應(yīng)著不同的窗口,這種動態(tài)變化的窗口叫滑動窗口
??赐瓯疚脑倩貋砜催@句話相信會理解的更透徹[手動狗頭]。
2、什么是窗口函數(shù)
窗口函數(shù)也叫
OLAP函數(shù)
(Online Anallytical Processing),可以對數(shù)據(jù)進(jìn)行實時分析處理。
窗口函數(shù)多用在什么場景?主要有以下兩類:
- 排名問題,例如:查包子鋪利潤月排名;
- TOPN問題,例如:查每種包子利潤最高的兩個月;
我們常見的窗口函數(shù)和聚合函數(shù)有這些:
- 專用窗口函數(shù):
rank()
,dense_rank()
,row_number()
- 聚合函數(shù):
max()
,min()
,count()
,sum()
,avg()
因為聚合函數(shù)也可以放在窗口函數(shù)中使用,因此窗口函數(shù)和普通聚合函數(shù)也很容易被混淆,二者區(qū)別如下:
聚合函數(shù)
是將多條記錄聚合為一條
;而窗口函數(shù)是每條記錄都會執(zhí)行,有幾條記錄執(zhí)行完還是幾條
。- 聚合函數(shù)也可以用于窗口函數(shù)中,這個我會舉例說明。
二、窗口函數(shù)用法
基本語法:
<窗口函數(shù)> OVER (PARTITION BY <用于分組的列名> ORDER BY <用于排序的列名>); -- over關(guān)鍵字用于指定函數(shù)的窗口范圍, -- partition by 用于對表分組, -- order by子句用于對分組后的結(jié)果進(jìn)行排序。
注意:窗口函數(shù)是對where或者group by子句處理后的結(jié)果再進(jìn)行二次操作,因此會按照SQL語句的運(yùn)行順序,窗口函數(shù)一般放在select子句中(from前),例如上一條SQL,可以往上拖著看看~
窗口函數(shù)都有哪些?懶得畫了,借lulin916
老哥的導(dǎo)圖一用~~
- 序號函數(shù):row_number() / rank() / dense_rank()
- 分布函數(shù):percent_rank() / cume_dist()
- 前后函數(shù):lag() / lead()
- 頭尾函數(shù):first_val() / last_val()
- 其他函數(shù):nth_value() / nfile()
讓我們來分別舉例看一看:
1、序號函數(shù):row_number() / rank() / dense_rank()
ROW_NUMBER():順序排序 —— 1、2、3
RANK():并列排序,跳過重復(fù)序號 —— 1、1、3
DENSE_RANK():并列排序,不跳過重復(fù)序號 —— 1、1、2
mysql> SELECT *,ROW_NUMBER() over(ORDER BY sales desc) as pro_ROW_NUMBER,rank() over(ORDER BY sales desc) as pro_rank,DENSE_RANK() over(ORDER BY sales desc) as pro_DENSE_RANK from chh_baozipu where product='豬肉大蔥包子'; +----+--------------------+-------+---------+----------------+----------+----------------+ | id | product | sales | month | pro_ROW_NUMBER | pro_rank | pro_DENSE_RANK | +----+--------------------+-------+---------+----------------+----------+----------------+ | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | 1 | 1 | 1 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 2 | 1 | 1 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 3 | 3 | 2 | | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | 4 | 3 | 2 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 5 | 5 | 3 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 6 | 6 | 4 | +----+--------------------+-------+---------+----------------+----------+----------------+ 6 rows in set (0.00 sec)
如上述示例可見,三個窗口函數(shù)服務(wù)與不同的三個典型業(yè)務(wù)需求,這三種足以應(yīng)對我們的排序統(tǒng)計。
以后同學(xué)們在面試或筆試時被問到時,請不要再說自查詢嵌套之類的lowB方案了,不然可別說你認(rèn)識我~狗子們
2、分布函數(shù):percent_rank() / cume_dist()
這個分布函數(shù)基本不用,不講。有興趣的同學(xué)自行百度~
3、前后函數(shù):lag(expr,n) / lead(expr,n)
expr后面還會涉及到,統(tǒng)一解釋一下:expr可以是
表達(dá)式
,也可以是列名
前后函數(shù)常用于:返回位于當(dāng)前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))
的expr的值
應(yīng)用場景:查詢前n名同學(xué)的成績和當(dāng)前同學(xué)成績的差值
內(nèi)層SQL先通過LAG()函數(shù)得到前1名同學(xué)的成績,外層SQL再將當(dāng)前同學(xué)和前1名同學(xué)的成績做差得到成績差值diff。
這里換成哈哥的測試表就有點(diǎn)尬了。。但你肯定明白這意思,來,讓我們尬查一下:
mysql> SELECT *,lag(sales,1) over win as pro_lag,lead(sales,1) over win as pro_lead from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY sales desc); +----+--------------------+-------+---------+---------+----------+ | id | product | sales | month | pro_lag | pro_lead | +----+--------------------+-------+---------+---------+----------+ | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | NULL | 1600 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 1600 | 1000 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 1600 | 1000 | | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | 1000 | 800 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 1000 | 600 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 800 | NULL | | 7 | 面餡兒包子 | 700 | 2021-11 | NULL | 300 | | 9 | 面餡兒包子 | 300 | 2021-09 | 700 | 200 | | 8 | 面餡兒包子 | 200 | 2021-10 | 300 | 200 | | 12 | 面餡兒包子 | 200 | 2021-06 | 200 | 100 | | 11 | 面餡兒包子 | 100 | 2021-07 | 200 | 0 | | 10 | 面餡兒包子 | 0 | 2021-08 | 100 | NULL | +----+--------------------+-------+---------+---------+----------+ 12 rows in set (0.00 sec)
這里我想問一下同學(xué)們是不是發(fā)現(xiàn)這條SQL和前面SQL不同?有哪幾個地方不同呢?
SELECT *, lag(sales,1) over win as pro_lag, lead(sales,1) over win as pro_lead from chh_baozipu where product='豬肉大蔥包子' WINDOW win as (PARTITION BY product ORDER BY sales desc);
1、把窗口提取出來設(shè)置了別名
其實,這種是把窗口提了出來,設(shè)置別名為:win
,像我們寫SQL時用別名一樣,這樣看起來會簡潔舒服一些,是吧。
有人問程序員要什么簡潔?別人看不懂才會覺得代碼牛B啊。這種同學(xué)一看就是沒被社會毒打過,等你遇到百年一見的祖?zhèn)鞔a時候,你就懂啥叫大道至簡了(借胖哥圖一用)。
2、窗口中增加了PARTITION BY product
這個關(guān)鍵字在over子句中,也就意味著控制了窗口的內(nèi)容,在上面基礎(chǔ)語法中我告訴你over中有兩個個關(guān)鍵詞:
partition by
是對窗口內(nèi)容進(jìn)行分組處理;order by
是對窗口內(nèi)容分組后進(jìn)行排序;
其實,還有更有意思的控制窗口范圍的方式~~
對于滑動窗口的范圍指定
,有兩種方式,基于行和基于范圍,我跟你著重介紹常用的基于行
來控制窗口范圍;
通常使用BETWEEN frame_start AND frame_end語法來表示行范圍,frame_start和frame_end可以支持如下關(guān)鍵字,來確定不同的動態(tài)行記錄:
- CURRENT ROW 邊界是當(dāng)前行,一般和其他范圍關(guān)鍵字一起使用
- UNBOUNDED PRECEDING 邊界是分區(qū)中的第一行
- UNBOUNDED FOLLOWING 邊界是分區(qū)中的最后一行
- expr PRECEDING 邊界是當(dāng)前行減去expr的值
- expr FOLLOWING 邊界是當(dāng)前行加上expr的值
來看幾個例子:
①計算當(dāng)前行與前n行(共n+1行)的聚合窗口函數(shù)
下例中控制窗口大小為當(dāng)前月+前兩個月的利潤總和,來看一下效果:
SELECT *,SUM(sales) OVER win as '近三個月利潤相加' FROM chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
mysql> SELECT *,SUM(sales) OVER win as '近三個月利潤相加' -> FROM chh_baozipu -> WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING); +----+--------------------+-------+---------+--------------------------+ | id | product | sales | month | 近三個月利潤相加 | +----+--------------------+-------+---------+--------------------------+ | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | 1000 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 2600 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 3400 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 3400 | | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | 3400 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 3200 | | 12 | 面餡兒包子 | 200 | 2021-06 | 200 | | 11 | 面餡兒包子 | 100 | 2021-07 | 300 | | 10 | 面餡兒包子 | 0 | 2021-08 | 300 | | 9 | 面餡兒包子 | 300 | 2021-09 | 400 | | 8 | 面餡兒包子 | 200 | 2021-10 | 500 | | 7 | 面餡兒包子 | 700 | 2021-11 | 1200 | +----+--------------------+-------+---------+--------------------------+ 12 rows in set (0.00 sec)
②計算當(dāng)前行與前n1行、后n2行的聚合窗口函數(shù)
下例中控制窗口大小為當(dāng)前月前一個月到后一個月的利潤總和,來看一下效果:
SELECT *,SUM(sales) OVER win as '前三個月利潤相加' FROM chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN n1 PRECEDING AND n2 FOLLOWING);
mysql> SELECT *,SUM(sales) OVER win as '前一個月到下一個月利潤相加' FROM chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING); +----+--------------------+-------+---------+--------------------------+ | id | product | sales | month |前一個月到下一個月利潤相加| +----+--------------------+-------+---------+--------------------------+ | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | 2600 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 3400 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 3400 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 3400 | | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | 3200 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 2200 | | 12 | 面餡兒包子 | 200 | 2021-06 | 300 | | 11 | 面餡兒包子 | 100 | 2021-07 | 300 | | 10 | 面餡兒包子 | 0 | 2021-08 | 400 | | 9 | 面餡兒包子 | 300 | 2021-09 | 500 | | 8 | 面餡兒包子 | 200 | 2021-10 | 1200 | | 7 | 面餡兒包子 | 700 | 2021-11 | 900 | +----+--------------------+-------+---------+--------------------------+ 12 rows in set (0.00 sec)
4、頭尾函數(shù):FIRST_VALUE(expr)、LAST_VALUE(expr)
頭尾函數(shù)應(yīng)用于:返回第一個或最后一個expr的值;
應(yīng)用場景:截止到當(dāng)前,按照日期排序查詢當(dāng)前最大月收入
和當(dāng)前最小月收入
。
SELECT *, FIRST_VALUE(sales) over win as '當(dāng)前最大月收入', LAST_VALUE(sales) over win as '當(dāng)前最小月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`);
mysql> SELECT *,FIRST_VALUE(sales) over win as '當(dāng)前最大月收入',LAST_VALUE(sales) over win as '當(dāng)前最小月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`); +----+--------------------+-------+---------+-----------------------+-----------------------+ | id | product | sales | month | 當(dāng)前最大月收入 | 當(dāng)前最小月收入 | +----+--------------------+-------+---------+-----------------------+-----------------------+ | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | 1000 | 1000 | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 1000 | 1600 | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 1000 | 800 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 1000 | 1000 | | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | 1000 | 1600 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 1000 | 600 | | 12 | 面餡兒包子 | 200 | 2021-06 | 200 | 200 | | 11 | 面餡兒包子 | 100 | 2021-07 | 200 | 100 | | 10 | 面餡兒包子 | 0 | 2021-08 | 200 | 0 | | 9 | 面餡兒包子 | 300 | 2021-09 | 200 | 300 | | 8 | 面餡兒包子 | 200 | 2021-10 | 200 | 200 | | 7 | 面餡兒包子 | 700 | 2021-11 | 200 | 700 | +----+--------------------+-------+---------+-----------------------+-----------------------+ 12 rows in set (0.00 sec)
5、其他函數(shù):nth_value() / nfile()
nfile()不常用,不再贅述;這里我們只提一下NTH_VALUE(expr,n)
函數(shù);
NTH_VALUE用途:返回窗口中第n個expr的值。
應(yīng)用場景:截止到當(dāng)前,顯示陳哈哈包子鋪月利潤榜中排名第2和第3的成績的利潤。
SELECT *, nth_value(sales,2) over win as '當(dāng)前排名第二的月收入', nth_value(sales,3) over win as '當(dāng)前排名第三的月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`);
mysql> SELECT *,nth_value(sales,2) over win as '當(dāng)前排名第二的月收入',nth_value(sales,3) over win as '當(dāng)前排名第三的月收入' from chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month`); +----+--------------------+-------+---------+--------------------------------+--------------------------------+ | id | product | sales | month | 當(dāng)前排名第二的月收入 | 當(dāng)前排名第三的月收入 | +----+--------------------+-------+---------+--------------------------------+--------------------------------+ | 6 | 豬肉大蔥包子 | 1000 | 2021-06 | NULL | NULL | | 5 | 豬肉大蔥包子 | 1600 | 2021-07 | 1600 | NULL | | 4 | 豬肉大蔥包子 | 800 | 2021-08 | 1600 |800 | | 3 | 豬肉大蔥包子 | 1000 | 2021-09 | 1600 |800 | | 2 | 豬肉大蔥包子 | 1600 | 2021-10 | 1600 |800 | | 1 | 豬肉大蔥包子 | 600 | 2021-11 | 1600 |800 | | 12 | 面餡兒包子 | 200 | 2021-06 | NULL | NULL | | 11 | 面餡兒包子 | 100 | 2021-07 |100 | NULL | | 10 | 面餡兒包子 | 0 | 2021-08 |100 | 0 | | 9 | 面餡兒包子 | 300 | 2021-09 |100 | 0 | | 8 | 面餡兒包子 | 200 | 2021-10 |100 | 0 | | 7 | 面餡兒包子 | 700 | 2021-11 |100 | 0 | +----+--------------------+-------+---------+--------------------------------+--------------------------------+ 12 rows in set (0.00 sec)
本章小結(jié)
窗口函數(shù)就說到這里,窗口函數(shù)是我接觸MySQL8以后發(fā)現(xiàn)的新東西,突然感覺MySQL開發(fā)團(tuán)隊還是很靈性的,每個版本都會新增一些玩兒法,當(dāng)然也很實用,希望MySQL9.0會給我們帶來更多的驚喜。
到此這篇關(guān)于MySQL窗口函數(shù)的具體使用的文章就介紹到這了,更多相關(guān)MySQL窗口函數(shù)內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站!
版權(quán)聲明:本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學(xué)習(xí)參考,不代表本站立場,如有內(nèi)容涉嫌侵權(quán),請聯(lián)系alex-e#qq.com處理。