MySQL窗口函數(shù)，你最熟悉的陌生人~

之前我給粉絲們搞過個投票，尋找MySQL中那個最熟悉的陌生人~~MySQL中哪些技術(shù)點是你既熟悉又陌生的？

前三名和我預(yù)料大差不差，分別是：

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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

怎么說？什么時候來我店里，請大家吃面餡兒包子。

目錄

一、什么是窗口函數(shù)

1、怎么理解窗口?

2、什么是窗口函數(shù)

二、窗口函數(shù)用法

1、序號函數(shù)：row_number() / rank() / dense_rank()

2、分布函數(shù)：percent_rank() / cume_dist()

3、前后函數(shù)：lag(expr,n) / lead(expr,n)

4、頭尾函數(shù)：FIRST_VALUE(expr)、LAST_VALUE(expr)

5、其他函數(shù)：nth_value() / nfile()

本章小結(jié)

一、什么是窗口函數(shù)

1、怎么理解窗口?

其實窗口的概念是非常重要的，要想學(xué)會窗口函數(shù)，可不能只知其一不知其二；我們得搞清楚窗口代表著啥，才知道什么時候該用它。

拿測試表舉個簡單的例子，統(tǒng)計一下：包子鋪的豬肉大蔥包子這半年截至每月累計利潤。

SELECT *,SUM(sales) over(ORDER BY `month`) as 累計利潤 from chh_baozipu where product='豬肉大蔥包子';

1

2

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)

1

2

3

4

5

6

7

8

9

10

11

12

從這條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ù)進行實時分析處理。

窗口函數(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é)果進行排序。

1

2

3

4

注意：窗口函數(shù)是對where或者group by子句處理后的結(jié)果再進行二次操作，因此會按照SQL語句的運行順序，窗口函數(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)

1

2

3

4

5

6

7

8

9

10

11

12

如上述示例可見，三個窗口函數(shù)服務(wù)與不同的三個典型業(yè)務(wù)需求，這三種足以應(yīng)對我們的排序統(tǒng)計。

以后同學(xué)們在面試或筆試時被問到時，請不要再說自查詢嵌套之類的lowB方案了，不然可別說你認識我~狗子們

2、分布函數(shù)：percent_rank() / cume_dist()

這個分布函數(shù)基本不用，不講。有興趣的同學(xué)自行百度~

3、前后函數(shù)：lag(expr,n) / lead(expr,n)

expr后面還會涉及到，統(tǒng)一解釋一下：expr可以是表達式，也可以是列名

前后函數(shù)常用于：返回位于當前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

應(yīng)用場景：查詢前n名同學(xué)的成績和當前同學(xué)成績的差值

內(nèi)層SQL先通過LAG()函數(shù)得到前1名同學(xué)的成績，外層SQL再將當前同學(xué)和前1名同學(xué)的成績做差得到成績差值diff。

這里換成哈哥的測試表就有點尬了。。但你肯定明白這意思，來，讓我們尬查一下：

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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

這里我想問一下同學(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

2

3

4

5

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)容進行分組處理；

order by 是對窗口內(nèi)容分組后進行排序；

其實，還有更有意思的控制窗口范圍的方式~~

對于滑動窗口的范圍指定，有兩種方式，基于行和基于范圍，我跟你著重介紹常用的基于行來控制窗口范圍；

通常使用BETWEEN frame_start AND frame_end語法來表示行范圍，frame_start和frame_end可以支持如下關(guān)鍵字，來確定不同的動態(tài)行記錄：

CURRENT ROW 邊界是當前行，一般和其他范圍關(guān)鍵字一起使用

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);

1

2

3

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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

②計算當前行與前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);

1

2

3

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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

4、頭尾函數(shù)：FIRST_VALUE(expr)、LAST_VALUE(expr)

頭尾函數(shù)應(yīng)用于：返回第一個或最后一個expr的值；

應(yīng)用場景：截止到當前，按照日期排序查詢當前最大月收入和當前最小月收入。

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`);

1

2

3

4

5

mysql> 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`); +----+--------------------+-------+---------+-----------------------+-----------------------+ | id | product | sales | month | 當前最大月收入 | 當前最小月收入 | +----+--------------------+-------+---------+-----------------------+-----------------------+ | 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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

5、其他函數(shù)：nth_value() / nfile()

nfile()不常用，不再贅述；這里我們只提一下NTH_VALUE(expr,n)函數(shù)；

NTH_VALUE用途：返回窗口中第n個expr的值。

應(yīng)用場景：截止到當前，顯示陳哈哈包子鋪月利潤榜中排名第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`);

1

2

3

4

5

mysql> 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`); +----+--------------------+-------+---------+--------------------------------+--------------------------------+ | id | product | sales | month | 當前排名第二的月收入 | 當前排名第三的月收入 | +----+--------------------+-------+---------+--------------------------------+--------------------------------+ | 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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

本章小結(jié)

窗口函數(shù)就說到這里，窗口函數(shù)是我接觸MySQL8以后發(fā)現(xiàn)的新東西，突然感覺MySQL開發(fā)團隊還是很靈性的，每個版本都會新增一些玩兒法，當然也很實用，希望MySQL9.0會給我們帶來更多的驚喜。

好了，多了就不說了，我勸你耗子尾汁，但推薦你關(guān)注我，因為我會讓你在快樂中學(xué)會很多東西！

MySQL系列文章匯總與《MySQL江湖路 | 專欄目錄》

往期熱門MySQL系列文章：

原創(chuàng) | MySQL中特別實用的幾種SQL語句送給大家

原創(chuàng) | SQL優(yōu)化最干貨總結(jié) - MySQL（2020最新版）

原創(chuàng) | 為什么大家都說SELECT * 效率低

原創(chuàng) | 面試讓HR都能聽懂的MySQL鎖機制，歡聲笑語中搞懂MySQL鎖

原創(chuàng) | MySQL中的 utf8 并不是真正的UTF-8編碼 ! !

原創(chuàng) | MySQL數(shù)據(jù)中有很多換行符和回車符！！該咋辦？

原創(chuàng) | delete后加 limit是個好習(xí)慣么

原創(chuàng) | MySQL慢查詢，一口從天而降的鍋！

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。