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