MySQL8新特性窗口函數(shù)詳解
本文博主給大家詳細(xì)講解一波 MySQL8 的新特性：「窗口函數(shù)」，相信大伙看完一定能有所收獲??。
- 本文提供的 sql 示例都是基于 MySQL8，由博主親自執(zhí)行確?？捎?/li>
- 博主github地址：http://github.com/wayn111 ，歡迎大家關(guān)注，點(diǎn)個(gè)star
簡(jiǎn)介
MySQL8 窗口函數(shù)是一種特殊的函數(shù)，它可以在一組查詢行上執(zhí)行類似于聚合的操作，但是不會(huì)將查詢行折疊為單個(gè)輸出行，而是為每個(gè)查詢行生成一個(gè)結(jié)果。窗口函數(shù)可以用來(lái)處理復(fù)雜的報(bào)表統(tǒng)計(jì)分析場(chǎng)景，例如計(jì)算移動(dòng)平均值、累計(jì)和、排名等。其中博主認(rèn)為它展現(xiàn)的主要威力在于「它能夠讓我們?cè)诓恍薷脑姓Z(yǔ)句輸出結(jié)果的基礎(chǔ)上，直接添加新的聚合字段」。
一. 語(yǔ)法解析
窗口函數(shù)語(yǔ)法如下：
window_function_name ( [argument1, argument2, ...] )
OVER (
[ PARTITION BY col1, col2, ... ]
[ORDER BY col3, col4, ...]
[ ROWS | RANGE frame_start AND frame_end ]
)window_function_name
window_function_name 函數(shù)可以是聚合函數(shù)或者非聚合函數(shù)。MySQL8 支持以下幾類窗口函數(shù)，
- 序號(hào)函數(shù)：用于為窗口內(nèi)的每一行生成一個(gè)序號(hào)，例如 ROW_NUMBER()，RANK()，DENSE_RANK() 等。
- 分布函數(shù)：用于計(jì)算窗口內(nèi)的每一行在整個(gè)分區(qū)中的相對(duì)位置，例如 PERCENT_RANK()，CUME_DIST() 等。
- 前后函數(shù)：用于獲取窗口內(nèi)的當(dāng)前行的前后某一行的值，例如 LAG()，LEAD() 等。
- 頭尾函數(shù)：用于獲取窗口內(nèi)的第一行或最后一行的值，例如 FIRST_VALUE()，LAST_VALUE() 等。
- 聚合函數(shù)：用于計(jì)算窗口內(nèi)的某個(gè)字段的聚合值，例如 SUM()，AVG()，MIN()，MAX() 等。
圖片
MySQL官網(wǎng)提供
OVER
OVER 關(guān)鍵字很重要，用來(lái)標(biāo)識(shí)是否使用窗口函數(shù)，語(yǔ)法如下
over_clause:
{OVER (window_spec) | OVER window_name}兩種形式都定義了窗口函數(shù)應(yīng)該如何處理查詢行。它們的區(qū)別在于窗口是直接在 OVER() 中定義，還是基于 window_name 在 OVER 字句可以重復(fù)使用。
- OVER() 常規(guī)用法，窗口規(guī)范直接出現(xiàn)在 OVER 子句中的括號(hào)之間。
- OVER window_name 基于 Named Windows，是由查詢中其他地方的 WINDOW 子句定義的窗口規(guī)范的名稱，可以重復(fù)使用。本文后續(xù)會(huì)進(jìn)行講解。
PARTITION BY
PARTITION BY子句用來(lái)將查詢結(jié)果劃分為不同的分區(qū)，窗口函數(shù)在每個(gè)分區(qū)上分別執(zhí)行，語(yǔ)法如下
partition_clause:
PARTITION BY expr [, expr] ..ORDER BY
ORDER BY 子句用來(lái)對(duì)每個(gè)分區(qū)內(nèi)的查詢結(jié)果進(jìn)行排序，窗口函數(shù)將按照排序后的順序進(jìn)行計(jì)算，語(yǔ)法如下
order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...frame_clause
frame_clause 是窗口函數(shù)的一個(gè)可選子句，用來(lái)指定每個(gè)分區(qū)內(nèi)的數(shù)據(jù)范圍，可以是靜態(tài)的或動(dòng)態(tài)的。語(yǔ)法如下
frame_clause:
frame_units frame_extent
frame_units:
{ROWS | RANGE}其中，frame_units表示窗口范圍的單位，可以是ROWS或RANGE。ROWS表示基于行數(shù)，RANGE表示基于值的大小。frame_extent表示窗口范圍的起始位置和結(jié)束位置，可以是以下幾種形式：
- CURRENT ROW: 表示當(dāng)前行。
- UNBOUNDED PRECEDING: 表示分區(qū)中的第一行。
- UNBOUNDED FOLLOWING: 表示分區(qū)中的最后一行。
- expr PRECEDING: 表示當(dāng)前行減去expr的值。
- expr FOLLOWING: 表示當(dāng)前行加上expr的值。
例如，如果指定了ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING，則表示窗口范圍包括當(dāng)前行、前兩行和后一行。如果指定了RANGE BETWEEN 10 PRECEDING AND CURRENT ROW，則表示窗口范圍包括當(dāng)前行和值在當(dāng)前行減去10以內(nèi)的所有行。如果沒(méi)有指定frame_clause，則默認(rèn)為RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即從分區(qū)開(kāi)始到當(dāng)前行。
圖片
引用自網(wǎng)上
二. Named Windows
MySQL8的 Named Windows 是指在 WINDOW 子句中定義并命名的窗口，可以在 OVER 子句中通過(guò)窗口名來(lái)引用。使用 Named Windows 的好處是可以避免在多個(gè)OVER子句中重復(fù)定義相同的窗口，而只需要在 WINDOW 子句中定義一次，然后在 OVER 子句中引用即可。例如，下面的查詢使用了三個(gè)相同的窗口：
SELECT
val,
ROW_NUMBER () OVER (ORDER BY val) AS 'row_number',
RANK () OVER (ORDER BY val) AS 'rank',
DENSE_RANK () OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;可以使用Named Windows來(lái)簡(jiǎn)化為：
SELECT
val,
ROW_NUMBER () OVER w AS 'row_number',
RANK () OVER w AS 'rank',
DENSE_RANK () OVER w AS 'dense_rank'
FROM numbers WINDOW w AS (ORDER BY val);這樣就只需要在 WINDOW 子句中定義一個(gè)名為w的窗口，然后在三個(gè)OVER子句中引用它。
如果一個(gè) OVER 子句使用了 OVER (window_name ...) 而不是 OVER window_name，則可以在引用的窗口名后面添加其他子句來(lái)修改窗口。例如，下面的查詢定義了一個(gè)包含分區(qū)的窗口，并在兩個(gè) OVER 子句中使用不同的排序來(lái)修改窗口：
SELECT
DISTINCT year, country,
FIRST_VALUE (year) OVER (w ORDER BY year ASC) AS first,
FIRST_VALUE (year) OVER (w ORDER BY year DESC) AS last
FROM sales WINDOW w AS (PARTITION BY country);這樣就可以根據(jù)不同的排序來(lái)獲取每個(gè)國(guó)家的第一年和最后一年。
一個(gè)命名窗口的定義本身也可以以一個(gè)窗口名開(kāi)頭。這樣可以實(shí)現(xiàn)窗口之間的引用，但不能形成循環(huán)。例如，下面的查詢定義了三個(gè)命名窗口，其中第二個(gè)和第三個(gè)都引用了第一個(gè)：
SELECT
val,
SUM(val) OVER w1 AS sum_w1,
SUM(val) OVER w2 AS sum_w2,
SUM(val) OVER w3 AS sum_w3
FROM numbers
WINDOW
w1 AS (ORDER BY val),
w2 AS (w1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
w3 AS (w2 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);這樣就可以根據(jù)不同的范圍來(lái)計(jì)算每個(gè)值的累計(jì)和。
三. SQL 示例
下面以一個(gè)簡(jiǎn)單的示例表來(lái)說(shuō)明 MySQL8 窗口函數(shù)的用法，提前準(zhǔn)備 sql 腳本如下
CREATE TABLE `sales` (
`id` int NOT NULL,
`year` int DEFAULT NULL,
`country` varchar(20) DEFAULT NULL,
`product` varchar(20) DEFAULT NULL,
`profit` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (1, 2000, 'Finland', 'Computer', 1500);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (2, 2000, 'Finland', 'Phone', 100);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (3, 2001, 'Finland', 'Phone', 10);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (4, 2001, 'India', 'Calculator', 75);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (5, 2000, 'India', 'Calculator', 75);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (6, 2000, 'India', 'Computer', 1200);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (7, 2000, 'USA', 'Calculator', 75);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (8, 2000, 'USA', 'Computer', 1500);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (9, 2001, 'USA', 'Calculator', 50);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (12, 2002, 'USA', 'Computer', 1200);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (13, 2001, 'USA', 'TV', 150);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (14, 2002, 'USA', 'TV', 100);
INSERT INTO `test_db`.`sales` (`id`, `year`, `country`, `product`, `profit`) VALUES (15, 2001, 'USA', 'Computer', 1500);這是一個(gè)銷售信息表，包含年份、國(guó)家、產(chǎn)品和利潤(rùn)四個(gè)字段。讓我們基于窗口函數(shù)來(lái)進(jìn)行一些統(tǒng)計(jì)分析，例如：
問(wèn)題一
計(jì)算每個(gè)國(guó)家每年的總利潤(rùn)，并按照國(guó)家和年份排序
SELECT year, country,
SUM(profit) OVER (PARTITION BY country, year) AS total_profit
FROM sales
ORDER BY country, year;輸出結(jié)果：
+------+---------+--------------+
| year | country | total_profit |
+------+---------+--------------+
| 2000 | Finland | 1600 |
| 2000 | Finland | 1600 |
| 2001 | Finland | 10 |
| 2000 | India | 1275 |
| 2000 | India | 1275 |
| 2001 | India | 75 |
| 2000 | USA | 1575 |
| 2000 | USA | 1575 |
| 2001 | USA | 1700 |
| 2001 | USA | 1700 |
| 2001 | USA | 1700 |
| 2002 | USA | 1300 |
| 2002 | USA | 1300 |
+------+---------+--------------+可以看到，每個(gè)國(guó)家每年的總利潤(rùn)都被計(jì)算出來(lái)了，但是沒(méi)有折疊為單個(gè)輸出行，而是為每個(gè)查詢行生成了一個(gè)結(jié)果。
在這里就體現(xiàn)出博主說(shuō)的不修改原有結(jié)果的基礎(chǔ)上，添加聚合字段的威力。
問(wèn)題二
計(jì)算每個(gè)國(guó)家每種產(chǎn)品的銷售排名，并按照國(guó)家和排名排序
SELECT country, product, profit,
RANK() OVER (PARTITION BY country ORDER BY profit DESC) AS rank1
FROM sales
ORDER BY country, rank1;輸出結(jié)果：
+---------+------------+--------+-------+
| country | product | profit | rank1 |
+---------+------------+--------+-------+
| Finland | Computer | 1500 | 1 |
| Finland | Phone | 100 | 2 |
| Finland | Phone | 10 | 3 |
| India | Computer | 1200 | 1 |
| India | Calculator | 75 | 2 |
| India | Calculator | 75 | 2 |
| USA | Computer | 1500 | 1 |
| USA | Computer | 1500 | 1 |
| USA | Computer | 1200 | 3 |
| USA | TV | 150 | 4 |
| USA | TV | 100 | 5 |
| USA | Calculator | 75 | 6 |
| USA | Calculator | 50 | 7 |
+---------+------------+--------+-------+可以看到，每個(gè)國(guó)家每種產(chǎn)品的銷售排名都被計(jì)算出來(lái)了，使用了RANK()函數(shù)，它會(huì)給相同利潤(rùn)的產(chǎn)品分配相同的排名，并跳過(guò)之后的排名。細(xì)心的朋友可能會(huì)發(fā)現(xiàn)相同國(guó)家產(chǎn)品的銷售排名重復(fù)之后，下一名會(huì)跳名次，如果不想這樣可以使用 DENSE_RANK() 函數(shù)，
mysql> SELECT country, product, profit,
DENSE_RANK() OVER (PARTITION BY country ORDER BY profit DESC) AS rank1
FROM sales
ORDER BY country, rank1;
+---------+------------+--------+-------+
| country | product | profit | rank1 |
+---------+------------+--------+-------+
| Finland | Computer | 1500 | 1 |
| Finland | Phone | 100 | 2 |
| Finland | Phone | 10 | 3 |
| India | Computer | 1200 | 1 |
| India | Calculator | 75 | 2 |
| India | Calculator | 75 | 2 |
| USA | Computer | 1500 | 1 |
| USA | Computer | 1500 | 1 |
| USA | Computer | 1200 | 2 |
| USA | TV | 150 | 3 |
| USA | TV | 100 | 4 |
| USA | Calculator | 75 | 5 |
| USA | Calculator | 50 | 6 |
+---------+------------+--------+-------+問(wèn)題三
計(jì)算每個(gè)國(guó)家每種產(chǎn)品的累計(jì)利潤(rùn)，并按照國(guó)家和利潤(rùn)排序
SELECT country, product, profit,
SUM(profit) OVER (PARTITION BY country ORDER BY profit
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_profit
FROM sales
ORDER BY country, profit;輸出結(jié)果：
+---------+------------+--------+-------------------+
| country | product | profit | cumulative_profit |
+---------+------------+--------+-------------------+
| Finland | Phone | 10 | 10 |
| Finland | Phone | 100 | 110 |
| Finland | Computer | 1500 | 1610 |
| India | Calculator | 75 | 75 |
| India | Calculator | 75 | 150 |
| India | Computer | 1200 | 1350 |
| USA | Calculator | 50 | 50 |
| USA | Calculator | 75 | 125 |
| USA | TV | 100 | 225 |
| USA | TV | 150 | 375 |
| USA | Computer | 1200 | 1575 |
| USA | Computer | 1500 | 3075 |
| USA | Computer | 1500 | 4575 |
+---------+------------+--------+-------------------+可以看到，每個(gè)國(guó)家每種產(chǎn)品的累計(jì)利潤(rùn)都被計(jì)算出來(lái)了，使用了SUM()函數(shù)，并指定了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW作為窗口范圍，表示從分區(qū)開(kāi)始到當(dāng)前行。
問(wèn)題四
基于Named Window 重寫問(wèn)題三，sql 如下
SELECT country, product, profit,
SUM(profit) OVER w1 AS cumulative_profit
FROM sales
WINDOW
w1 as (PARTITION BY country ORDER BY profit
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY country, profit
;輸出結(jié)果：
+---------+------------+--------+-------------------+
| country | product | profit | cumulative_profit |
+---------+------------+--------+-------------------+
| Finland | Phone | 10 | 10 |
| Finland | Phone | 100 | 110 |
| Finland | Computer | 1500 | 1610 |
| India | Calculator | 75 | 75 |
| India | Calculator | 75 | 150 |
| India | Computer | 1200 | 1350 |
| USA | Calculator | 50 | 50 |
| USA | Calculator | 75 | 125 |
| USA | TV | 100 | 225 |
| USA | TV | 150 | 375 |
| USA | Computer | 1200 | 1575 |
| USA | Computer | 1500 | 3075 |
| USA | Computer | 1500 | 4575 |
+---------+------------+--------+-------------------+四. 窗口函數(shù)優(yōu)缺點(diǎn)
優(yōu)點(diǎn)：
- 窗口函數(shù)可以在不改變?cè)硇袛?shù)的情況下，對(duì)每個(gè)分區(qū)內(nèi)的查詢行進(jìn)行聚合、排序、排名等操作，提高了數(shù)據(jù)分析的靈活性和效率。
- 窗口函數(shù)可以使用滑動(dòng)窗口來(lái)處理動(dòng)態(tài)的數(shù)據(jù)范圍，例如計(jì)算移動(dòng)平均值、累計(jì)和等。
- 窗口函數(shù)可以與普通聚合函數(shù)、子查詢等結(jié)合使用，實(shí)現(xiàn)更復(fù)雜的查詢邏輯。
缺點(diǎn)：
- 窗口函數(shù)的語(yǔ)法較為復(fù)雜，需要注意OVER子句中的各個(gè)參數(shù)的含義和作用。
- 窗口函數(shù)的執(zhí)行效率可能不如普通聚合函數(shù)，因?yàn)樗枰獙?duì)每個(gè)分區(qū)內(nèi)的每個(gè)查詢行進(jìn)行計(jì)算，而不是折疊為單個(gè)輸出行。
- 窗口函數(shù)只能在SELECT列表和ORDER BY子句中使用，不能用于WHERE、GROUP BY、HAVING等子句中。
五、總結(jié)
窗口函數(shù)的應(yīng)用場(chǎng)景很廣，可以完成許多數(shù)據(jù)分析與挖掘任務(wù)。MySQL8 支持窗口函數(shù)是一個(gè)非常棒的特性，大大提高了 MySQL 在數(shù)據(jù)分析領(lǐng)域的競(jìng)爭(zhēng)力。希望通過(guò)這篇文章可以幫助大家對(duì) MySQL8 的窗口函數(shù)有一個(gè)初步的認(rèn)識(shí)。
