之前我給粉絲們搞過個投票，尋找mysql中那個
最熟悉的陌生人~~mysql中哪些技術點是你既熟悉又陌生的？
前三名和我預料大差不差，分別是：
1、游標2、窗口函數3、聚簇索引
這三個點雖然平時用得少，但在面試中卻常被問到。值得一提的是，很多面試官對問題竟然也是一知半解。。
今天我想和你聊聊窗口函數，mysql從8.0開始支持窗口函數，或許你們公司的mysql版本還無法讓你爽一把，但我建議你要在本地搞一個試試，真香！
好了，廢話不多說，老規矩，先上開胃小菜，看看今天的測試表數據吧。
本文用來演示用的測試表是chh_baozipu，翻譯過來就是emmm…陳哈哈的包子鋪。悄悄告訴你，哈哥今年盤了個包子鋪賣包子，這張表就是包子鋪這半年的利潤~
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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、怎么理解窗口?
其實窗口的概念是非常重要的，要想學會窗口函數，可不能只知其一不知其二；我們得搞清楚窗口代表著啥，才知道什么時候該用它。
拿測試表舉個簡單的例子，統計一下：包子鋪的豬肉大蔥包子這半年截至每月累計利潤。
|
1
2
|
select *,sum(sales) over(order by `month`) as 累計利潤 from chh_baozipu where product='豬肉大蔥包子'; |
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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這行的窗口就是前兩行，以此類推（如下圖）。
可見，窗口就是范圍的意思，可以理解為一些記錄（行）的集合；窗口函數也就是在滿足某種條件的記錄集合上執行計算的特殊函數。
對于每條記錄都要在此窗口內執行函數，有的函數隨著記錄不同，窗口大小都是固定的，這種屬于靜態窗口；有的函數則相反，不同的記錄對應著不同的窗口，這種動態變化的窗口叫滑動窗口?？赐瓯疚脑倩貋砜催@句話相信會理解的更透徹[手動狗頭]。
2、什么是窗口函數
窗口函數也叫
olap函數（online anallytical processing），可以對數據進行實時分析處理。
窗口函數多用在什么場景？主要有以下兩類：
- 排名問題，例如：查包子鋪利潤月排名；
- topn問題，例如：查每種包子利潤最高的兩個月；
我們常見的窗口函數和聚合函數有這些：
-
專用窗口函數：
rank()，dense_rank()，row_number() -
聚合函數：
max()，min()，count()，sum()，avg()
因為聚合函數也可以放在窗口函數中使用，因此窗口函數和普通聚合函數也很容易被混淆，二者區別如下：
-
聚合函數是將多條記錄聚合為一條；而窗口函數是每條記錄都會執行，有幾條記錄執行完還是幾條。 - 聚合函數也可以用于窗口函數中，這個我會舉例說明。
二、窗口函數用法
基本語法：
|
1
2
3
4
|
<窗口函數> over (partition by <用于分組的列名> order by <用于排序的列名>);-- over關鍵字用于指定函數的窗口范圍，-- partition by 用于對表分組，-- order by子句用于對分組后的結果進行排序。 |
注意：窗口函數是對where或者group by子句處理后的結果再進行二次操作，因此會按照sql語句的運行順序，窗口函數一般放在select子句中（from前），例如上一條sql，可以往上拖著看看~
窗口函數都有哪些？懶得畫了，借lulin916老哥的導圖一用~~
- 序號函數：row_number() / rank() / dense_rank()
- 分布函數：percent_rank() / cume_dist()
- 前后函數：lag() / lead()
- 頭尾函數：first_val() / last_val()
- 其他函數：nth_value() / nfile()
讓我們來分別舉例看一看：
1、序號函數：row_number() / rank() / dense_rank()
row_number()：順序排序 —— 1、2、3
rank()：并列排序，跳過重復序號 —— 1、1、3
dense_rank()：并列排序，不跳過重復序號 —— 1、1、2
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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) |
如上述示例可見，三個窗口函數服務與不同的三個典型業務需求，這三種足以應對我們的排序統計。
以后同學們在面試或筆試時被問到時，請不要再說自查詢嵌套之類的lowb方案了，不然可別說你認識我~狗子們
2、分布函數：percent_rank() / cume_dist()
這個分布函數基本不用，不講。有興趣的同學自行百度~
3、前后函數：lag(expr,n) / lead(expr,n)
expr后面還會涉及到，統一解釋一下：expr可以是
表達式，也可以是列名
前后函數常用于：返回位于當前行的前n行（lag(expr,n)）或后n行（lead(expr,n)）的expr的值
應用場景：查詢前n名同學的成績和當前同學成績的差值
內層sql先通過lag()函數得到前1名同學的成績，外層sql再將當前同學和前1名同學的成績做差得到成績差值diff。
這里換成哈哥的測試表就有點尬了。。但你肯定明白這意思，來，讓我們尬查一下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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) |
這里我想問一下同學們是不是發現這條sql和前面sql不同？有哪幾個地方不同呢？
|
1
2
3
4
5
|
select *,lag(sales,1) over win as pro_lag,lead(sales,1) over win as pro_leadfrom chh_baozipu where product='豬肉大蔥包子'window win as (partition by product order by sales desc); |
1、把窗口提取出來設置了別名
其實，這種是把窗口提了出來，設置別名為：win，像我們寫sql時用別名一樣，這樣看起來會簡潔舒服一些，是吧。
有人問程序員要什么簡潔？別人看不懂才會覺得代碼牛b啊。這種同學一看就是沒被社會毒打過，等你遇到百年一見的祖傳代碼時候，你就懂啥叫大道至簡了（借胖哥圖一用）。
2、窗口中增加了partition by product
這個關鍵字在over子句中，也就意味著控制了窗口的內容，在上面基礎語法中我告訴你over中有兩個個關鍵詞：
-
partition by是對窗口內容進行分組處理； -
order by是對窗口內容分組后進行排序；
其實，還有更有意思的控制窗口范圍的方式~~
對于滑動窗口的范圍指定，有兩種方式，基于行和基于范圍，我跟你著重介紹常用的基于行來控制窗口范圍；
通常使用between frame_start and frame_end語法來表示行范圍，frame_start和frame_end可以支持如下關鍵字，來確定不同的動態行記錄：
- current row 邊界是當前行，一般和其他范圍關鍵字一起使用
- unbounded preceding 邊界是分區中的第一行
- unbounded following 邊界是分區中的最后一行
- expr preceding 邊界是當前行減去expr的值
- expr following 邊界是當前行加上expr的值
來看幾個例子：
①計算當前行與前n行（共n+1行）的聚合窗口函數
下例中控制窗口大小為當前月+前兩個月的利潤總和，來看一下效果：
|
1
2
3
|
select *,sum(sales) over win as '近三個月利潤相加'from chh_baozipu window win as (partition by product order by `month` rows 2 preceding); |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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) |
②計算當前行與前n1行、后n2行的聚合窗口函數
下例中控制窗口大小為當前月前一個月到后一個月的利潤總和，來看一下效果：
|
1
2
3
|
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
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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、頭尾函數：first_value(expr)、last_value(expr)
頭尾函數應用于：返回第一個或最后一個expr的值；
應用場景：截止到當前，按照日期排序查詢當前最大月收入和當前最小月收入。
|
1
2
3
4
5
|
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
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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) |
5、其他函數：nth_value() / nfile()
nfile()不常用，不再贅述；這里我們只提一下nth_value(expr,n)函數；
nth_value用途：返回窗口中第n個expr的值。
應用場景：截止到當前，顯示陳哈哈包子鋪月利潤榜中排名第2和第3的成績的利潤。
|
1
2
3
4
5
|
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
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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) |
本章小結
窗口函數就說到這里，窗口函數是我接觸mysql8以后發現的新東西，突然感覺mysql開發團隊還是很靈性的，每個版本都會新增一些玩兒法，當然也很實用，希望mysql9.0會給我們帶來更多的驚喜。
到此這篇關于mysql窗口函數的具體使用的文章就介紹到這了,更多相關mysql窗口函數內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/qq_39390545/article/details/121279735
