詳解MySQL8中的新特性窗口函數(shù)
簡介
MySQL8 窗口函數(shù)是一種特殊的函數(shù)，它可以在一組查詢行上執(zhí)行類似于聚合的操作，但是不會將查詢行折疊為單個(gè)輸出行，而是為每個(gè)查詢行生成一個(gè)結(jié)果。窗口函數(shù)可以用來處理復(fù)雜的報(bào)表統(tǒng)計(jì)分析場景，例如計(jì)算移動平均值、累計(jì)和、排名等。其中博主認(rèn)為它展現(xiàn)的主要威力在于它能夠讓我們在不修改原有語句輸出結(jié)果的基礎(chǔ)上，直接添加新的聚合字段。
一. 語法解析
窗口函數(shù)語法如下：
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ù)，
- 序號函數(shù)：用于為窗口內(nèi)的每一行生成一個(gè)序號，例如
ROW_NUMBER()，RANK()，DENSE_RANK()等。 - 分布函數(shù)：用于計(jì)算窗口內(nèi)的每一行在整個(gè)分區(qū)中的相對位置，例如
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()等。
OVER
OVER 關(guān)鍵字很重要，用來標(biāo)識是否使用窗口函數(shù)，語法如下
over_clause:
{OVER (window_spec) | OVER window_name}兩種形式都定義了窗口函數(shù)應(yīng)該如何處理查詢行。它們的區(qū)別在于窗口是直接在 OVER() 中定義，還是基于 window_name 在 OVER 字句可以重復(fù)使用。
OVER()常規(guī)用法，窗口規(guī)范直接出現(xiàn)在OVER子句中的括號之間。OVER window_name基于Named Windows，是由查詢中其他地方的WINDOW子句定義的窗口規(guī)范的名稱，可以重復(fù)使用。本文后續(xù)會進(jìn)行講解。
PARTITION BY
PARTITION BY子句用來將查詢結(jié)果劃分為不同的分區(qū)，窗口函數(shù)在每個(gè)分區(qū)上分別執(zhí)行，語法如下
partition_clause:
PARTITION BY expr [, expr] ..ORDER BY
ORDER BY 子句用來對每個(gè)分區(qū)內(nèi)的查詢結(jié)果進(jìn)行排序，窗口函數(shù)將按照排序后的順序進(jìn)行計(jì)算，語法如下
order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...frame_clause
frame_clause 是窗口函數(shù)的一個(gè)可選子句，用來指定每個(gè)分區(qū)內(nèi)的數(shù)據(jù)范圍，可以是靜態(tài)的或動態(tài)的。語法如下
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)的所有行。如果沒有指定frame_clause，則默認(rèn)為RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即從分區(qū)開始到當(dāng)前行。
二. Named Windows
MySQL8的 Named Windows 是指在 WINDOW 子句中定義并命名的窗口，可以在 OVER 子句中通過窗口名來引用。使用 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來簡化為：
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，則可以在引用的窗口名后面添加其他子句來修改窗口。例如，下面的查詢定義了一個(gè)包含分區(qū)的窗口，并在兩個(gè) OVER 子句中使用不同的排序來修改窗口：
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ù)不同的排序來獲取每個(gè)國家的第一年和最后一年。
一個(gè)命名窗口的定義本身也可以以一個(gè)窗口名開頭。這樣可以實(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ù)不同的范圍來計(jì)算每個(gè)值的累計(jì)和。
三. SQL 示例
下面以一個(gè)簡單的示例表來說明 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è)銷售信息表，包含年份、國家、產(chǎn)品和利潤四個(gè)字段。讓我們基于窗口函數(shù)來進(jìn)行一些統(tǒng)計(jì)分析，例如：
問題一
計(jì)算每個(gè)國家每年的總利潤，并按照國家和年份排序
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è)國家每年的總利潤都被計(jì)算出來了，但是沒有折疊為單個(gè)輸出行，而是為每個(gè)查詢行生成了一個(gè)結(jié)果。
在這里就體現(xiàn)出博主說的不修改原有結(jié)果的基礎(chǔ)上，添加聚合字段的威力。
問題二
計(jì)算每個(gè)國家每種產(chǎn)品的銷售排名，并按照國家和排名排序
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è)國家每種產(chǎn)品的銷售排名都被計(jì)算出來了，使用了RANK()函數(shù)，它會給相同利潤的產(chǎn)品分配相同的排名，并跳過之后的排名。細(xì)心的朋友可能會發(fā)現(xiàn)相同國家產(chǎn)品的銷售排名重復(fù)之后，下一名會跳名次，如果不想這樣可以使用 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;
輸出結(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 | 2 |
| USA | TV | 150 | 3 |
| USA | TV | 100 | 4 |
| USA | Calculator | 75 | 5 |
| USA | Calculator | 50 | 6 |
+---------+------------+--------+-------+
問題三
計(jì)算每個(gè)國家每種產(chǎn)品的累計(jì)利潤，并按照國家和利潤排序
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è)國家每種產(chǎn)品的累計(jì)利潤都被計(jì)算出來了，使用了SUM()函數(shù)，并指定了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW作為窗口范圍，表示從分區(qū)開始到當(dāng)前行。
問題四
基于Named Window 重寫問題三，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ù)可以在不改變原表行數(shù)的情況下，對每個(gè)分區(qū)內(nèi)的查詢行進(jìn)行聚合、排序、排名等操作，提高了數(shù)據(jù)分析的靈活性和效率。
- 窗口函數(shù)可以使用滑動窗口來處理動態(tài)的數(shù)據(jù)范圍，例如計(jì)算移動平均值、累計(jì)和等。
- 窗口函數(shù)可以與普通聚合函數(shù)、子查詢等結(jié)合使用，實(shí)現(xiàn)更復(fù)雜的查詢邏輯。
缺點(diǎn)：
- 窗口函數(shù)的語法較為復(fù)雜，需要注意
OVER子句中的各個(gè)參數(shù)的含義和作用。 - 窗口函數(shù)的執(zhí)行效率可能不如普通聚合函數(shù)，因?yàn)樗枰獙γ總€(gè)分區(qū)內(nèi)的每個(gè)查詢行進(jìn)行計(jì)算，而不是折疊為單個(gè)輸出行。
- 窗口函數(shù)只能在
SELECT列表和ORDER BY子句中使用，不能用于WHERE、GROUP BY、HAVING等子句中。
關(guān)于查詢性能這里，窗口函數(shù)的性能取決于多個(gè)因素，例如窗口函數(shù)的類型、窗口的大小、分區(qū)的數(shù)量、排序的代價(jià)等。一般來說，窗口函數(shù)的性能優(yōu)于使用子查詢或連接的方法，因?yàn)榇翱诤瘮?shù)只需要掃描一次數(shù)據(jù)，而子查詢或連接可能需要多次掃描或連接。
但是，并不是所有的窗口函數(shù)都能高效地計(jì)算。一些窗口函數(shù)，例如ROW_NUMBER()、RANK()、LEAD()等，只需要對分區(qū)內(nèi)的數(shù)據(jù)進(jìn)行排序，然后根據(jù)當(dāng)前行的位置來計(jì)算結(jié)果，這些窗口函數(shù)的性能較好。另一些窗口函數(shù)，例如SUM()、AVG()、MIN()、MAX()等，需要對分區(qū)內(nèi)或窗口內(nèi)的數(shù)據(jù)進(jìn)行聚合，這些窗口函數(shù)的性能較差。
為了提高窗口函數(shù)的性能，可以采用以下一些方法：
- 選擇合適的窗口函數(shù)，避免使用復(fù)雜或重復(fù)的窗口函數(shù)。
- 使用
Named Windows來定義和引用窗口，避免在多個(gè)OVER子句中重復(fù)定義相同的窗口。 - 盡量減少分區(qū)和排序的代價(jià)，使用索引或物化視圖來加速分區(qū)和排序。
- 盡量減少窗口的大小，使用合適的
frame_clause來限制窗口內(nèi)的數(shù)據(jù)范圍。 - 盡量使用并行處理來加速窗口函數(shù)的計(jì)算，利用多核或分布式系統(tǒng)來提高效率。
五、總結(jié)
窗口函數(shù)的應(yīng)用場景很廣，可以完成許多數(shù)據(jù)分析與挖掘任務(wù)。MySQL8 支持窗口函數(shù)是一個(gè)非常棒的特性，大大提高了 MySQL 在數(shù)據(jù)分析領(lǐng)域的競爭力。希望通過這篇文章可以幫助大家對 MySQL8 的窗口函數(shù)有一個(gè)初步的認(rèn)識。
以上就是詳解MySQL8中的新特性窗口函數(shù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL窗口函數(shù)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
my.cnf參數(shù)配置實(shí)現(xiàn)InnoDB引擎性能優(yōu)化
目前來說：InnoDB是為Mysql處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的。在數(shù)據(jù)量大的網(wǎng)站或是應(yīng)用中Innodb是倍受青睞的。另一方面，在數(shù)據(jù)庫的復(fù)制操作中Innodb也是能保證master和slave數(shù)據(jù)一致有一定的作用。2017-05-05
mysql 獲取今天、昨天0點(diǎn)時(shí)間戳的實(shí)例
今天小編就為大家分享一篇mysql 獲取今天、昨天0點(diǎn)時(shí)間戳的實(shí)例，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-05-05
MySQL DBA 常用手冊小結(jié)，使用mysql的朋友可以參考下。2011-11-11
mysql優(yōu)化之慢查詢分析+explain命令分析+優(yōu)化技巧總結(jié)
這篇文章主要介紹了mysql優(yōu)化之慢查詢分析，explain命令分析，優(yōu)化技巧總結(jié),需要的朋友可以參考下2023-02-02
本文介紹了如何使用MySQL的GROUPBY語句結(jié)合MAX函數(shù)來實(shí)現(xiàn)分組取字段最大值的操作,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-01-01
MySQL的EXPLAIN命令用于SQL語句的查詢執(zhí)行計(jì)劃(QEP)。如果你的頁面返回結(jié)果很慢,你就需要使用explain去分析你的sql是否需要優(yōu)化了.接下來通過本文給大家介紹Mysql Explain 命令，感興趣的朋友一起學(xué)習(xí)吧2016-10-10
C# Mysql 查詢 Rownum的解決方法，需要的朋友可以參考一下2013-03-03
抽取oracle數(shù)據(jù)到mysql數(shù)據(jù)庫的實(shí)現(xiàn)過程
今天小編就為大家分享一篇關(guān)于抽取oracle數(shù)據(jù)到mysql數(shù)據(jù)庫的實(shí)現(xiàn)過程，小編覺得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來看看吧2019-02-02
詳解數(shù)據(jù)庫連接的URL的寫法及總結(jié)
這篇文章主要介紹了詳解數(shù)據(jù)庫連接的URL的寫法及總結(jié)的相關(guān)資料這里提供了四種方法1、oracle.2、MySQL.3、SQL Server.4、DB2，需要的朋友可以參考下2017-07-07
這篇文章主要介紹了MySQL自定義函數(shù)及觸發(fā)器，自定義函數(shù)是一種對MySQL擴(kuò)展的途徑，其用法與內(nèi)置的函數(shù)相同，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的朋友可以參考一下2022-08-08