MySQL :: MySQL 8.4 參考手冊 :: 14.20.3 視窗函數框架規格

版本 8.4

9.0
8.0

8.0 日文

MySQL 8.4 參考手冊 / ... / 視窗函數框架規格

14.20.3 視窗函數框架規格

與視窗函數搭配使用的視窗定義可以包含框架子句。框架是目前分割區的子集，而框架子句會指定如何定義子集。

框架是相對於目前列決定的，這使得框架能夠根據目前列在其分割區內的位置在分割區內移動。範例

透過將框架定義為從分割區開始到目前列的所有列，您可以計算每列的執行總計。
透過將框架定義為在目前列的任一側延伸 N 列，您可以計算滾動平均值。

下列查詢示範如何使用移動框架來計算每個時間排序的 level 值群組內的執行總計，以及從目前列和緊接在其前後的列計算的滾動平均值

mysql> SELECT
         time, subject, val,
         SUM(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS UNBOUNDED PRECEDING)
           AS running_total,
         AVG(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
           AS running_average
       FROM observations;
+----------+---------+------+---------------+-----------------+
| time     | subject | val  | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 07:00:00 | st113   |   10 |            10 |          9.5000 |
| 07:15:00 | st113   |    9 |            19 |         14.6667 |
| 07:30:00 | st113   |   25 |            44 |         18.0000 |
| 07:45:00 | st113   |   20 |            64 |         22.5000 |
| 07:00:00 | xh458   |    0 |             0 |          5.0000 |
| 07:15:00 | xh458   |   10 |            10 |          5.0000 |
| 07:30:00 | xh458   |    5 |            15 |         15.0000 |
| 07:45:00 | xh458   |   30 |            45 |         20.0000 |
| 08:00:00 | xh458   |   25 |            70 |         27.5000 |
+----------+---------+------+---------------+-----------------+

對於 running_average 資料行，沒有位於第一列之前的框架列，也沒有位於最後一列之後的框架列。在這些情況下，AVG() 會計算可用列的平均值。

用作視窗函數的彙總函數會在目前列框架中的列上運作，這些非彙總視窗函數也會

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

標準 SQL 指定在整個分割區上運作的視窗函數不應有框架子句。MySQL 允許此類函數使用框架子句，但會忽略它。即使指定框架，這些函數也會使用整個分割區

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

框架子句 (如果提供) 具有此語法

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

如果沒有框架子句，預設框架取決於是否出現 ORDER BY 子句，如本節稍後所述。

frame_units 值會指出目前列與框架列之間的關係類型

ROWS：框架是由開始和結束列位置定義。偏移量是與目前列號的列號差異。
RANGE：框架是由值範圍內的列定義。偏移量是與目前列值的列值差異。

frame_extent 值會指出框架的起點和終點。您可以僅指定框架的起點 (在這種情況下，目前列隱含為終點)，或者使用 BETWEEN 來指定框架的起點和終點

frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

使用 BETWEEN 語法時，frame_start 不得晚於 frame_end。

允許的 frame_start 和 frame_end 值具有以下含義

CURRENT ROW：對於 ROWS，界限是目前列。對於 RANGE，界限是目前列的同層列。
UNBOUNDED PRECEDING：界限是第一個分割區列。
UNBOUNDED FOLLOWING：界限是最後一個分割區列。
expr PRECEDING：對於 ROWS，界限是目前列之前的 expr 列。對於 RANGE，界限是值等於目前列值減去 expr 的列；如果目前列值為 NULL，則界限是列的同層列。
對於 expr PRECEDING (和 expr FOLLOWING)，expr 可以是 ? 參數標記 (用於預備陳述式)、非負數值常值，或形式為 INTERVAL val unit 的時間間隔。對於 INTERVAL 運算式，val 會指定非負數間隔值，而 unit 則是關鍵字，表示應該以何種單位解譯值。(如需允許的 units 指定子的詳細資訊，請參閱DATE_ADD() 函數在第 14.7 節，「日期與時間函數」中的說明。)
數值或時間 expr 上的 RANGE 需要分別針對數值或時間運算式使用 ORDER BY。
有效 expr PRECEDING 和 expr FOLLOWING 指示器的範例
```
10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING
```
expr FOLLOWING：對於 ROWS，界限是目前列之後的 expr 列。對於 RANGE，界限是值等於目前列的值加上 expr 的列；如果目前列的值為 NULL，界限則是該列的同值列。
關於 expr 的允許值，請參閱 expr PRECEDING 的描述。

以下查詢示範了 FIRST_VALUE()、LAST_VALUE() 和兩個 NTH_VALUE() 的實例。

mysql> SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

每個函數都使用目前框架中的列，根據顯示的視窗定義，框架從第一個分割區列延伸到目前列。對於 NTH_VALUE() 的呼叫，目前框架並不總是包含請求的列；在這種情況下，傳回值為 NULL。

在沒有框架子句的情況下，預設框架取決於是否存在 ORDER BY 子句

使用 ORDER BY：預設框架包含從分割區開始到目前列的列，包括目前列的所有同值列（根據 ORDER BY 子句與目前列相等的列）。預設值等同於此框架規格
```
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```
不使用 ORDER BY：預設框架包含所有分割區列（因為沒有 ORDER BY，所有分割區列都是同值列）。預設值等同於此框架規格
```
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
```

由於預設框架會因 ORDER BY 的存在與否而有所不同，因此在查詢中加入 ORDER BY 以取得確定性的結果可能會變更結果。（例如，SUM() 產生的值可能會變更。）若要取得相同的結果，但依 ORDER BY 排序，請提供明確的框架規格，無論是否存在 ORDER BY 都要使用。

當目前列的值為 NULL 時，框架規格的意義可能不明顯。假設是這種情況，以下範例說明各種框架規格如何套用

ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING
框架從 NULL 開始，並在 NULL 停止，因此僅包含值為 NULL 的列。
ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING
框架從 NULL 開始，並在分割區結尾停止。由於 ASC 排序會將 NULL 值放在最前面，因此框架是整個分割區。
ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING
框架從 NULL 開始，並在分割區結尾停止。由於 DESC 排序會將 NULL 值放在最後面，因此框架僅為 NULL 值。
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING
框架從 NULL 開始，並在分割區結尾停止。由於 ASC 排序會將 NULL 值放在最前面，因此框架是整個分割區。
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
框架從 NULL 開始，並在 NULL 停止，因此僅包含值為 NULL 的列。
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING
框架從 NULL 開始，並在 NULL 停止，因此僅包含值為 NULL 的列。
ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
框架從分割區的開頭開始，並在值為 NULL 的列停止。由於 ASC 排序會將 NULL 值放在最前面，因此框架僅為 NULL 值。