MySQL :: MySQL 8.4 參考手冊 :: 14.20.2 視窗函數概念與語法

版本 8.4

9.0
8.0

8.0 日文

MySQL 8.4 參考手冊 / ... / 視窗函數概念與語法

14.20.2 視窗函數概念與語法

本節說明如何使用視窗函數。範例使用與GROUPING()函數討論中相同的銷售資訊資料集，如14.19.2 節，「GROUP BY 修飾詞」所述。

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

視窗函數對一組查詢列執行類似彙總的操作。然而，彙總操作會將查詢列分組為單一結果列，而視窗函數則為每個查詢列產生結果。

函數評估所發生的列稱為目前列。
與目前列相關且執行函數評估的查詢列組成目前列的視窗。

例如，使用銷售資訊表，這兩個查詢執行彙總操作，產生所有列作為群組的單一全域總和，以及依國家分組的總和。

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

相反地，視窗操作不會將查詢列群組摺疊成單一輸出列。而是為每個列產生結果。與先前的查詢一樣，下列查詢使用 SUM()，但這次是作為視窗函數。

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

查詢中的每個視窗操作都以包含 OVER 子句表示，該子句指定如何將查詢列分割為群組，以供視窗函數處理。

第一個 OVER 子句為空，這會將整組查詢列視為單一分割區。因此，視窗函數會產生全域總和，但對每個列都執行此操作。
第二個 OVER 子句會依國家分割列，產生每個分割區（每個國家）的總和。函數會為每個分割區列產生此總和。

視窗函數僅允許在選取清單和 ORDER BY 子句中使用。查詢結果列由 FROM 子句決定，在 WHERE、GROUP BY 和 HAVING 處理之後，視窗執行發生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前。

許多彙總函數允許使用 OVER 子句，因此可以作為視窗函數或非視窗函數使用，具體取決於 OVER 子句是否存在。

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

有關每個彙總函數的詳細資訊，請參閱14.19.1 節，「彙總函數描述」。

MySQL 也支援僅用作視窗函數的非彙總函數。對於這些函數，OVER 子句是強制性的。

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

有關每個非彙總函數的詳細資訊，請參閱14.20.1 節，「視窗函數描述」。

作為其中一個非彙總視窗函數的範例，此查詢使用 ROW_NUMBER()，它會產生每個分割區中每個列的列號。在此範例中，列會依國家編號。依預設，分割區列是未排序的，且列編號是不確定的。若要排序分割區列，請在視窗定義中包含 ORDER BY 子句。查詢使用未排序和排序的分割區（row_num1 和 row_num2 資料行）來說明省略和包含 ORDER BY 之間的差異。

mysql> SELECT
         year, country, product, profit,
         ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
         ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
       FROM sales;
+------+---------+------------+--------+----------+----------+
| year | country | product    | profit | row_num1 | row_num2 |
+------+---------+------------+--------+----------+----------+
| 2000 | Finland | Computer   |   1500 |        2 |        1 |
| 2000 | Finland | Phone      |    100 |        1 |        2 |
| 2001 | Finland | Phone      |     10 |        3 |        3 |
| 2000 | India   | Calculator |     75 |        2 |        1 |
| 2000 | India   | Calculator |     75 |        3 |        2 |
| 2000 | India   | Computer   |   1200 |        1 |        3 |
| 2000 | USA     | Calculator |     75 |        5 |        1 |
| 2000 | USA     | Computer   |   1500 |        4 |        2 |
| 2001 | USA     | Calculator |     50 |        2 |        3 |
| 2001 | USA     | Computer   |   1500 |        3 |        4 |
| 2001 | USA     | Computer   |   1200 |        7 |        5 |
| 2001 | USA     | TV         |    150 |        1 |        6 |
| 2001 | USA     | TV         |    100 |        6 |        7 |
+------+---------+------------+--------+----------+----------+

如先前所述，若要使用視窗函數（或將彙總函數視為視窗函數），請在函數呼叫之後包含 OVER 子句。OVER 子句有兩種形式。

over_clause:
    {OVER (window_spec) | OVER window_name}

這兩種形式都定義視窗函數應如何處理查詢列。它們的不同之處在於視窗是直接在 OVER 子句中定義，還是由對查詢中其他位置定義的具名視窗的參考所提供。

在第一種情況下，視窗規格會直接出現在 OVER 子句中，在括號之間。
在第二種情況下，window_name 是查詢中其他地方由 WINDOW 子句定義的視窗規格名稱。詳細資訊請參閱第 14.20.4 節，〈具名視窗〉。

對於 OVER (window_spec) 語法，視窗規格有幾個部分，皆為選用。

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

如果 OVER() 為空，則視窗包含所有查詢列，且視窗函式會使用所有列計算結果。否則，括號內存在的子句會決定使用哪些查詢列來計算函式結果，以及如何對它們進行分割和排序。

window_name：由查詢中其他地方的 WINDOW 子句定義的視窗名稱。如果 window_name 單獨出現在 OVER 子句中，則它會完整定義視窗。如果也給定了分割、排序或框架子句，則它們會修改具名視窗的解釋。詳細資訊請參閱第 14.20.4 節，〈具名視窗〉。
partition_clause：PARTITION BY 子句表示如何將查詢列分割成群組。給定列的視窗函式結果是基於包含該列的分割區中的列。如果省略 PARTITION BY，則會有一個包含所有查詢列的單一分割區。

注意

視窗函式的分割與資料表分割不同。如需資料表分割的相關資訊，請參閱第 26 章，分割。

partition_clause 具有以下語法
```
partition_clause:
    PARTITION BY expr [, expr] ...
```
標準 SQL 要求 PARTITION BY 後面只能接欄位名稱。MySQL 延伸允許使用表達式，而不僅限於欄位名稱。例如，如果資料表包含名為 ts 的 TIMESTAMP 欄位，標準 SQL 允許 PARTITION BY ts，但不允許 PARTITION BY HOUR(ts)，而 MySQL 兩者都允許。
order_clause：ORDER BY 子句表示如何排序每個分割區中的列。根據 ORDER BY 子句相等的分割區列會被視為對等項目。如果省略 ORDER BY，則分割區列為未排序，不表示處理順序，且所有分割區列皆為對等項目。
order_clause 具有以下語法
```
order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
```
每個 ORDER BY 表達式可以選擇性地接上 ASC 或 DESC，以表示排序方向。如果未指定方向，則預設為 ASC。NULL 值在遞增排序中會排序在最前面，在遞減排序中會排序在最後面。
視窗定義中的 ORDER BY 會在個別的分割區內應用。若要對整個結果集進行排序，請在查詢頂層包含 ORDER BY。
frame_clause：框架是目前分割區的子集，而框架子句指定如何定義該子集。框架子句本身有多個子子句。詳細資訊請參閱第 14.20.3 節，〈視窗函式框架規格〉。