人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動態(tài)

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

發(fā)布日期:2021-12-10 08:53 | 文章來源:站長之家

  之前我給粉絲們搞過個投票,尋找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處理。

實時開通

自選配置、實時開通

免備案

全球線路精選!

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問服務(wù)

1對1客戶咨詢顧問

在線
客服

在線客服:7*24小時在線

客服
熱線

400-630-3752
7*24小時客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部