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處理。
關(guān)注官方微信