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