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

版本 9.0

8.4 目前
8.0

8.0 日文

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

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 節「視窗函式框架規格」。