之前我給粉絲們搞過個投票腮郊，尋找MySQL中那個最熟悉的陌生人~~MySQL中哪些技術點是你既熟悉又陌生的摹蘑？
前三名和我預料大差不差，分別是：
?1轧飞、游標?2衅鹿、窗口函數(shù)?3撒踪、聚簇索引
這三個點雖然平時用得少，但在面試中卻常被問到大渤。值得一提的是制妄，很多面試官對問題竟然也是一知半解。泵三。
今天我想和你聊聊窗口函數(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、怎么理解窗口?
其實窗口的概念是非常重要的仅炊，要想學會窗口函數(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ù)則相反蔽介，不同的記錄對應著不同的窗口摘投，這種動態(tài)變化的窗口叫滑動窗口『缧睿看完本文再回來看這句話相信會理解的更透徹[手動狗頭]犀呼。
2、什么是窗口函數(shù)
窗口函數(shù)也叫OLAP函數(shù)（Online Anallytical Processing）薇组，可以對數(shù)據(jù)進行實時分析處理外臂。
窗口函數(shù)多用在什么場景？主要有以下兩類：
?排名問題律胀，例如：查包子鋪利潤月排名宋光；?TOPN問題，例如：查每種包子利潤最高的兩個月炭菌；
我們常見的窗口函數(shù)和聚合函數(shù)有這些：
?專用窗口函數(shù)：rank()罪佳，dense_rank()，row_number()?聚合函數(shù)：max()黑低，min()赘艳，count()，sum()克握，avg()
因為聚合函數(shù)也可以放在窗口函數(shù)中使用蕾管，因此窗口函數(shù)和普通聚合函數(shù)也很容易被混淆，二者區(qū)別如下：
1.聚合函數(shù)是將多條記錄聚合為一條菩暗；而窗口函數(shù)是每條記錄都會執(zhí)行掰曾，有幾條記錄執(zhí)行完還是幾條。2.聚合函數(shù)也可以用于窗口函數(shù)中停团，這個我會舉例說明旷坦。
二、窗口函數(shù)用法
基本語法：
<窗口函數(shù)> OVER (PARTITION BY <用于分組的列名> ORDER BY <用于排序的列名>);
-- over關鍵字用于指定函數(shù)的窗口范圍客蹋，
-- partition by 用于對表分組塞蹭，
-- order by子句用于對分組后的結果進行排序。
注意：窗口函數(shù)是對where或者group by子句處理后的結果再進行二次操作讶坯，因此會按照SQL語句的運行順序番电，窗口函數(shù)一般放在select子句中（from前），例如上一條SQL，可以往上拖著看看~
窗口函數(shù)都有哪些漱办？懶得畫了这刷，借lulin916老哥的導圖一用~~
?序號函數(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()：并列排序，跳過重復序號 —— 1洞辣、1咐刨、3?DENSE_RANK()：并列排序，不跳過重復序號 —— 1扬霜、1定鸟、2
如上述示例可見，三個窗口函數(shù)服務于不同的三個典型業(yè)務需求著瓶，這三種足以應對我們的排序統(tǒng)計联予。
以后同學們在面試或筆試時被問到時，請不要再說自查詢嵌套之類的lowB方案了材原，不然可別說你認識我~狗子們
2沸久、分布函數(shù)：percent_rank() / cume_dist()
這個分布函數(shù)基本不用，不講余蟹。有興趣的同學自行百度~
3卷胯、前后函數(shù)：lag(expr,n) / lead(expr,n)
expr后面還會涉及到，統(tǒng)一解釋一下：expr可以是表達式客叉，也可以是列名
前后函數(shù)常用于：返回位于當前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
應用場景：查詢前n名同學的成績和當前同學成績的差值
內(nèi)層SQL先通過LAG()函數(shù)得到前1名同學的成績诵竭，外層SQL再將當前同學和前1名同學的成績做差得到成績差值diff话告。
這里換成哈哥的測試表就有點尬了兼搏。。但你肯定明白這意思沙郭，來佛呻，讓我們尬查一下：
這里我想問一下同學們是不是發(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吓著、把窗口提取出來設置了別名
其實，這種是把窗口提了出來送挑，設置別名為：win绑莺，像我們寫SQL時用別名一樣，這樣看起來會簡潔舒服一些惕耕，是吧纺裁。
有人問程序員要什么簡潔？別人看不懂才會覺得代碼牛B啊。這種同學一看就是沒被社會毒打過欺缘，等你遇到百年一見的祖?zhèn)鞔a時候栋豫，你就懂啥叫大道至簡了（借胖哥圖一用）。
2谚殊、窗口中增加了PARTITION BY product
這個關鍵字在over子句中丧鸯，也就意味著控制了窗口的內(nèi)容，在上面基礎語法中我告訴你over中有兩個個關鍵詞：
?partition by 是對窗口內(nèi)容進行分組處理嫩絮；?order by 是對窗口內(nèi)容分組后進行排序丛肢；
其實，還有更有意思的控制窗口范圍的方式~~
對于滑動窗口的范圍指定剿干，有兩種方式摔踱，基于行和基于范圍，我跟你著重介紹常用的基于行來控制窗口范圍怨愤；
通常使用BETWEEN frame_start AND frame_end語法來表示行范圍派敷，frame_start和frame_end可以支持如下關鍵字，來確定不同的動態(tài)行記錄：
?CURRENT ROW 邊界是當前行撰洗，一般和其他范圍關鍵字一起使用?UNBOUNDED PRECEDING 邊界是分區(qū)中的第一行?UNBOUNDED FOLLOWING 邊界是分區(qū)中的最后一行?expr PRECEDING 邊界是當前行減去expr的值?expr FOLLOWING 邊界是當前行加上expr的值
來看幾個例子：
①計算當前行與前n行（共n+1行）的聚合窗口函數(shù)
下例中控制窗口大小為當前月+前兩個月的利潤總和篮愉，來看一下效果：
SELECT *,SUM(sales) OVER win as '近三個月利潤相加'
FROM chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
②計算當前行與前n1行、后n2行的聚合窗口函數(shù)
下例中控制窗口大小為當前月前一個月到后一個月的利潤總和差导，來看一下效果：
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);
4试躏、頭尾函數(shù)：FIRST_VALUE(expr)、LAST_VALUE(expr)
頭尾函數(shù)應用于：返回第一個或最后一個expr的值设褐；
應用場景：截止到當前颠蕴，按照日期排序查詢當前最大月收入和當前最小月收入。
SELECT *,
FIRST_VALUE(sales) over win as '當前最大月收入',
LAST_VALUE(sales) over win as '當前最小月收入'
from chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month`);
5助析、其他函數(shù)：nth_value() / nfile()
nfile()不常用犀被，不再贅述；這里我們只提一下NTH_VALUE(expr,n)函數(shù)外冀；
NTH_VALUE用途：返回窗口中第n個expr的值寡键。
應用場景：截止到當前，顯示陳哈哈包子鋪月利潤榜中排名第2和第3的成績的利潤雪隧。
SELECT *,
nth_value(sales,2) over win as '當前排名第二的月收入',
nth_value(sales,3) over win as '當前排名第三的月收入'
from chh_baozipu
WINDOW win as (PARTITION BY product ORDER BY `month`);
本章小結
窗口函數(shù)就說到這里西轩，窗口函數(shù)是我接觸MySQL8以后發(fā)現(xiàn)的新東西，突然感覺MySQL開發(fā)團隊還是很靈性的脑沿，每個版本都會新增一些玩兒法藕畔，當然也很實用，希望MySQL9.0會給我們帶來更多的驚喜庄拇。
