MySQL窗口函數(shù)的具體使用

更新時(shí)間：2021年11月15日 15:47:41 作者：_陳哈哈

本文主要介紹了MySQL窗口函數(shù)的具體使用，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

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)

怎么說(shuō)？什么時(shí)候來(lái)我店里，請(qǐng)大家吃面餡兒包子。

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

1、怎么理解窗口?

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

拿測(cè)試表舉個(gè)簡(jiǎn)單的例子，統(tǒng)計(jì)一下：包子鋪的豬肉大蔥包子這半年截至每月累計(jì)利潤(rùn)。

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

mysql> SELECT *,SUM(sales) over(ORDER BY `month`) as 累計(jì)利潤(rùn) from chh_baozipu where product='豬肉大蔥包子';
+----+--------------------+-------+---------+--------------+
| id | product            | sales | month   | 累計(jì)利潤(rùn)     |
+----+--------------------+-------+---------+--------------+
|  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可以看出，對(duì)于第一行id=6這行的窗口就是第一行，對(duì)于第二行id=5這行的窗口就是前兩行，以此類(lèi)推（如下圖）。

在這里插入圖片描述

可見(jiàn)，窗口就是范圍的意思，可以理解為一些記錄（行）的集合；窗口函數(shù)也就是在滿(mǎn)足某種條件的記錄集合上執(zhí)行計(jì)算的特殊函數(shù)。

對(duì)于每條記錄都要在此窗口內(nèi)執(zhí)行函數(shù)，有的函數(shù)隨著記錄不同，窗口大小都是固定的，這種屬于靜態(tài)窗口；有的函數(shù)則相反，不同的記錄對(duì)應(yīng)著不同的窗口，這種動(dòng)態(tài)變化的窗口叫滑動(dòng)窗口?？赐瓯疚脑倩貋?lái)看這句話相信會(huì)理解的更透徹[手動(dòng)狗頭]。

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

窗口函數(shù)也叫OLAP函數(shù)（Online Anallytical Processing），可以對(duì)數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理。

窗口函數(shù)多用在什么場(chǎng)景？主要有以下兩類(lèi)：

排名問(wèn)題，例如：查包子鋪利潤(rùn)月排名；
TOPN問(wèn)題，例如：查每種包子利潤(rùn)最高的兩個(gè)月；

我們常見(jiàn)的窗口函數(shù)和聚合函數(shù)有這些：

專(zhuān)用窗口函數(shù)：rank()，dense_rank()，row_number()
聚合函數(shù)：max()，min()，count()，sum()，avg()

因?yàn)榫酆虾瘮?shù)也可以放在窗口函數(shù)中使用，因此窗口函數(shù)和普通聚合函數(shù)也很容易被混淆，二者區(qū)別如下：

聚合函數(shù)是將多條記錄聚合為一條；而窗口函數(shù)是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條。
聚合函數(shù)也可以用于窗口函數(shù)中，這個(gè)我會(huì)舉例說(shuō)明。

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

基本語(yǔ)法：

<窗口函數(shù)> OVER (PARTITION BY <用于分組的列名> ORDER BY <用于排序的列名>);
-- over關(guān)鍵字用于指定函數(shù)的窗口范圍，
-- partition by 用于對(duì)表分組，
-- order by子句用于對(duì)分組后的結(jié)果進(jìn)行排序。

注意：窗口函數(shù)是對(duì)where或者group by子句處理后的結(jié)果再進(jìn)行二次操作，因此會(huì)按照SQL語(yǔ)句的運(yùn)行順序，窗口函數(shù)一般放在select子句中（from前），例如上一條SQL，可以往上拖著看看~

窗口函數(shù)都有哪些？懶得畫(huà)了，借lulin916老哥的導(dǎo)圖一用~~

在這里插入圖片描述

序號(hào)函數(shù)：row_number() / rank() / dense_rank()
分布函數(shù)：percent_rank() / cume_dist()
前后函數(shù)：lag() / lead()
頭尾函數(shù)：first_val() / last_val()
其他函數(shù)：nth_value() / nfile()

讓我們來(lái)分別舉例看一看：

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

ROW_NUMBER()：順序排序 —— 1、2、3

RANK()：并列排序，跳過(guò)重復(fù)序號(hào) —— 1、1、3

DENSE_RANK()：并列排序，不跳過(guò)重復(fù)序號(hào) —— 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)

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

以后同學(xué)們?cè)诿嬖嚮蚬P試時(shí)被問(wèn)到時(shí)，請(qǐng)不要再說(shuō)自查詢(xún)嵌套之類(lèi)的lowB方案了，不然可別說(shuō)你認(rèn)識(shí)我~狗子們

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

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

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

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

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

應(yīng)用場(chǎng)景：查詢(xún)前n名同學(xué)的成績(jī)和當(dāng)前同學(xué)成績(jī)的差值

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

這里換成哈哥的測(cè)試表就有點(diǎn)尬了。。但你肯定明白這意思，來(lái)，讓我們尬查一下：

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)

這里我想問(wèn)一下同學(xué)們是不是發(fā)現(xiàn)這條SQL和前面SQL不同？有哪幾個(gè)地方不同呢？

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、把窗口提取出來(lái)設(shè)置了別名

其實(shí)，這種是把窗口提了出來(lái)，設(shè)置別名為：win，像我們寫(xiě)SQL時(shí)用別名一樣，這樣看起來(lái)會(huì)簡(jiǎn)潔舒服一些，是吧。

有人問(wèn)程序員要什么簡(jiǎn)潔？別人看不懂才會(huì)覺(jué)得代碼牛B啊。這種同學(xué)一看就是沒(méi)被社會(huì)毒打過(guò)，等你遇到百年一見(jiàn)的祖?zhèn)鞔a時(shí)候，你就懂啥叫大道至簡(jiǎn)了（借胖哥圖一用）。

在這里插入圖片描述

2、窗口中增加了PARTITION BY product

這個(gè)關(guān)鍵字在over子句中，也就意味著控制了窗口的內(nèi)容，在上面基礎(chǔ)語(yǔ)法中我告訴你over中有兩個(gè)個(gè)關(guān)鍵詞：

partition by 是對(duì)窗口內(nèi)容進(jìn)行分組處理；
order by 是對(duì)窗口內(nèi)容分組后進(jìn)行排序；

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

對(duì)于滑動(dòng)窗口的范圍指定，有兩種方式，基于行和基于范圍，我跟你著重介紹常用的基于行來(lái)控制窗口范圍；

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

CURRENT ROW 邊界是當(dāng)前行，一般和其他范圍關(guān)鍵字一起使用
UNBOUNDED PRECEDING 邊界是分區(qū)中的第一行
UNBOUNDED FOLLOWING 邊界是分區(qū)中的最后一行
expr PRECEDING 邊界是當(dāng)前行減去expr的值
expr FOLLOWING 邊界是當(dāng)前行加上expr的值

來(lái)看幾個(gè)例子：

①計(jì)算當(dāng)前行與前n行（共n+1行）的聚合窗口函數(shù)

下例中控制窗口大小為當(dāng)前月+前兩個(gè)月的利潤(rùn)總和，來(lái)看一下效果：

SELECT *,SUM(sales) OVER win as '近三個(gè)月利潤(rùn)相加'
FROM chh_baozipu 
WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);

mysql> SELECT *,SUM(sales) OVER win as '近三個(gè)月利潤(rùn)相加'
    -> FROM chh_baozipu 
	-> WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
+----+--------------------+-------+---------+--------------------------+
| id | product            | sales | month   | 近三個(gè)月利潤(rùn)相加         |
+----+--------------------+-------+---------+--------------------------+
|  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)

②計(jì)算當(dāng)前行與前n1行、后n2行的聚合窗口函數(shù)

下例中控制窗口大小為當(dāng)前月前一個(gè)月到后一個(gè)月的利潤(rùn)總和，來(lái)看一下效果：

SELECT *,SUM(sales) OVER win as '前三個(gè)月利潤(rùn)相加' 
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 '前一個(gè)月到下一個(gè)月利潤(rùn)相加' FROM chh_baozipu WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
+----+--------------------+-------+---------+--------------------------+
| id | product            | sales | month   |前一個(gè)月到下一個(gè)月利潤(rùn)相加|
+----+--------------------+-------+---------+--------------------------+
|  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)用于：返回第一個(gè)或最后一個(gè)expr的值；

應(yīng)用場(chǎng)景：截止到當(dāng)前，按照日期排序查詢(xún)當(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個(gè)expr的值。

應(yīng)用場(chǎng)景：截止到當(dāng)前，顯示陳哈哈包子鋪月利潤(rùn)榜中排名第2和第3的成績(jī)的利潤(rùn)。

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ù)就說(shuō)到這里，窗口函數(shù)是我接觸MySQL8以后發(fā)現(xiàn)的新東西，突然感覺(jué)MySQL開(kāi)發(fā)團(tuán)隊(duì)還是很靈性的，每個(gè)版本都會(huì)新增一些玩兒法，當(dāng)然也很實(shí)用，希望MySQL9.0會(huì)給我們帶來(lái)更多的驚喜。

到此這篇關(guān)于MySQL窗口函數(shù)的具體使用的文章就介紹到這了,更多相關(guān)MySQL窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: