MySQL8新特性窗口函數詳解

作者：waynaqua 2023-07-03 08:20:35

窗口函數的應用場景很廣，可以完成許多數據分析與挖掘任務。MySQL8 支持窗口函數是一個非常棒的特性，大大提高了 MySQL 在數據分析領域的競爭力。

本文博主給大家詳細講解一波 MySQL8 的新特性：「窗口函數」，相信大伙看完一定能有所收獲??。

本文提供的 sql 示例都是基于 MySQL8，由博主親自執行確保可用
博主github地址：http://github.com/wayn111 ，歡迎大家關注，點個star

簡介

MySQL8 窗口函數是一種特殊的函數，它可以在一組查詢行上執行類似于聚合的操作，但是不會將查詢行折疊為單個輸出行，而是為每個查詢行生成一個結果。窗口函數可以用來處理復雜的報表統計分析場景，例如計算移動平均值、累計和、排名等。其中博主認為它展現的主要威力在于「它能夠讓我們在不修改原有語句輸出結果的基礎上，直接添加新的聚合字段」。

一. 語法解析

窗口函數語法如下：

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() 等。
分布函數：用于計算窗口內的每一行在整個分區中的相對位置，例如 PERCENT_RANK()，CUME_DIST() 等。
前后函數：用于獲取窗口內的當前行的前后某一行的值，例如 LAG()，LEAD() 等。
頭尾函數：用于獲取窗口內的第一行或最后一行的值，例如 FIRST_VALUE()，LAST_VALUE() 等。
聚合函數：用于計算窗口內的某個字段的聚合值，例如 SUM()，AVG()，MIN()，MAX() 等。

圖片

MySQL官網提供

OVER

OVER 關鍵字很重要，用來標識是否使用窗口函數，語法如下

over_clause:
    {OVER (window_spec) | OVER window_name}

兩種形式都定義了窗口函數應該如何處理查詢行。它們的區別在于窗口是直接在 OVER() 中定義，還是基于 window_name 在 OVER 字句可以重復使用。

OVER() 常規用法，窗口規范直接出現在 OVER 子句中的括號之間。
OVER window_name 基于 Named Windows，是由查詢中其他地方的 WINDOW 子句定義的窗口規范的名稱，可以重復使用。本文后續會進行講解。

PARTITION BY

PARTITION BY子句用來將查詢結果劃分為不同的分區，窗口函數在每個分區上分別執行，語法如下

partition_clause:
    PARTITION BY expr [, expr] ..

ORDER BY

ORDER BY 子句用來對每個分區內的查詢結果進行排序，窗口函數將按照排序后的順序進行計算，語法如下

order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

frame_clause

frame_clause 是窗口函數的一個可選子句，用來指定每個分區內的數據范圍，可以是靜態的或動態的。語法如下

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

其中，frame_units表示窗口范圍的單位，可以是ROWS或RANGE。ROWS表示基于行數，RANGE表示基于值的大小。frame_extent表示窗口范圍的起始位置和結束位置，可以是以下幾種形式：

CURRENT ROW: 表示當前行。
UNBOUNDED PRECEDING: 表示分區中的第一行。
UNBOUNDED FOLLOWING: 表示分區中的最后一行。
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，即從分區開始到當前行。

圖片

引用自網上

二. 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，則可以在引用的窗口名后面添加其他子句來修改窗口。例如，下面的查詢定義了一個包含分區的窗口，并在兩個 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);

這樣就可以根據不同的排序來獲取每個國家的第一年和最后一年。

一個命名窗口的定義本身也可以以一個窗口名開頭。這樣可以實現窗口之間的引用，但不能形成循環。例如，下面的查詢定義了三個命名窗口，其中第二個和第三個都引用了第一個：

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);

這是一個銷售信息表，包含年份、國家、產品和利潤四個字段。讓我們基于窗口函數來進行一些統計分析，例如：

問題一

計算每個國家每年的總利潤，并按照國家和年份排序

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()函數，它會給相同利潤的產品分配相同的排名，并跳過之后的排名。細心的朋友可能會發現相同國家產品的銷售排名重復之后，下一名會跳名次，如果不想這樣可以使用 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作為窗口范圍，表示從分區開始到當前行。

問題四

基于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              |
+---------+------------+--------+-------------------+

四. 窗口函數優缺點

優點：

窗口函數可以在不改變原表行數的情況下，對每個分區內的查詢行進行聚合、排序、排名等操作，提高了數據分析的靈活性和效率。
窗口函數可以使用滑動窗口來處理動態的數據范圍，例如計算移動平均值、累計和等。
窗口函數可以與普通聚合函數、子查詢等結合使用，實現更復雜的查詢邏輯。

缺點：

窗口函數的語法較為復雜，需要注意OVER子句中的各個參數的含義和作用。
窗口函數的執行效率可能不如普通聚合函數，因為它需要對每個分區內的每個查詢行進行計算，而不是折疊為單個輸出行。
窗口函數只能在SELECT列表和ORDER BY子句中使用，不能用于WHERE、GROUP BY、HAVING等子句中。

五、總結

窗口函數的應用場景很廣，可以完成許多數據分析與挖掘任務。MySQL8 支持窗口函數是一個非常棒的特性，大大提高了 MySQL 在數據分析領域的競爭力。希望通過這篇文章可以幫助大家對 MySQL8 的窗口函數有一個初步的認識。

責任編輯：武曉燕來源： waynblog

MySQL 窗口函數