MySQL 9.0 版本注意事項
本節說明非彙總視窗函數，其針對查詢中的每一列，使用與該列相關的列執行計算。大多數彙總函數也可以作為視窗函數使用；請參閱第 14.19.1 節，〈彙總函數說明〉。
如需視窗函數使用資訊和範例，以及 OVER 子句、視窗、分割區、框架和同級等詞彙的定義，請參閱第 14.20.2 節，〈視窗函數概念與語法〉。
表 14.30 視窗函數
| 名稱 | 說明 |
|---|---|
CUME_DIST() |
累積分佈值 |
DENSE_RANK() |
目前列在其分割區內的排名，不含間隙 |
FIRST_VALUE() |
視窗框架第一列的引數值 |
LAG() |
分割區中落後於目前列的列之引數值 |
LAST_VALUE() |
視窗框架最後一列的引數值 |
LEAD() |
分割區中領先於目前列的列之引數值 |
NTH_VALUE() |
視窗框架第 N 列的引數值 |
NTILE() |
目前列在其分割區中的儲存桶編號。 |
PERCENT_RANK() |
百分比排名值 |
RANK() |
目前列在其分割區內的排名，含間隙 |
ROW_NUMBER() |
目前列在其分割區內的編號 |
在下列函數說明中，over_clause 代表 OVER 子句，如第 14.20.2 節，〈視窗函數概念與語法〉中所述。某些視窗函數允許 null_treatment 子句，其指定在計算結果時如何處理 NULL 值。此子句為選用。它是 SQL 標準的一部分，但 MySQL 實作僅允許 RESPECT NULLS (這也是預設值)。這表示計算結果時會考慮 NULL 值。IGNORE NULLS 會被剖析，但會產生錯誤。
CUME_DIST()over_clause傳回值在值群組中的累積分佈；也就是說，小於或等於目前列中值的分割區值百分比。這表示視窗分割區視窗排序中在目前列之前或與目前列同級的列數，除以視窗分割區中的總列數。傳回值範圍從 0 到 1。
此函數應搭配
ORDER BY使用，將分割區列排序為所需的順序。如果沒有ORDER BY，所有列都是同級，且值為N/N= 1，其中N是分割區大小。over_clause如第 14.20.2 節，〈視窗函數概念與語法〉中所述。下列查詢顯示
val資料行中的值集合，每列的CUME_DIST()值，以及類似的PERCENT_RANK()函數傳回的百分比排名值。為了方便參考，查詢也會使用ROW_NUMBER()顯示列號mysql> SELECT val, ROW_NUMBER() OVER w AS 'row_number', CUME_DIST() OVER w AS 'cume_dist', PERCENT_RANK() OVER w AS 'percent_rank' FROM numbers WINDOW w AS (ORDER BY val); +------+------------+--------------------+--------------+ | val | row_number | cume_dist | percent_rank | +------+------------+--------------------+--------------+ | 1 | 1 | 0.2222222222222222 | 0 | | 1 | 2 | 0.2222222222222222 | 0 | | 2 | 3 | 0.3333333333333333 | 0.25 | | 3 | 4 | 0.6666666666666666 | 0.375 | | 3 | 5 | 0.6666666666666666 | 0.375 | | 3 | 6 | 0.6666666666666666 | 0.375 | | 4 | 7 | 0.8888888888888888 | 0.75 | | 4 | 8 | 0.8888888888888888 | 0.75 | | 5 | 9 | 1 | 1 | +------+------------+--------------------+--------------+DENSE_RANK()over_clause傳回目前列在其分割區內的排名，不含間隙。同級被視為並列，並獲得相同的排名。此函數會將連續的排名指派給同級群組；結果是，大小大於一的群組不會產生不連續的排名編號。如需範例，請參閱
RANK()函數說明。此函數應與
ORDER BY一起使用，以將分割區的列排序成所需的順序。若沒有ORDER BY，則所有列都視為同等。over_clause如第 14.20.2 節，〈視窗函數概念與語法〉中所述。FIRST_VALUE([expr)null_treatment]over_clause傳回視窗框架中第一列的
expr值。over_clause的說明請參閱 第 14.20.2 節，「視窗函數概念和語法」。null_treatment的說明請參閱本節的簡介。以下查詢示範
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。LAG([expr[,N[,default]])null_treatment]over_clause傳回在其分割區中，落後（在目前列之前）目前列
N列的列之expr值。如果沒有這樣的列，則傳回值為default。例如，如果N為 3，則前三列的傳回值為default。如果缺少N或default，則預設值分別為 1 和NULL。N必須為字面非負整數。如果N為 0，則會針對目前列評估expr。N不能為NULL，且必須為介於0到263（含）之間的整數，可以採用下列任何形式未簽署的整數常數字面值
位置參數標記 (
?)使用者定義的變數
預存常式中的區域變數
over_clause的說明請參閱 第 14.20.2 節，「視窗函數概念和語法」。null_treatment的說明請參閱本節的簡介。LAG()（以及類似的LEAD()函數）通常用於計算列之間的差異。以下查詢顯示一組依時間排序的觀察值，以及每個觀察值，相鄰列的LAG()和LEAD()值，以及目前列和相鄰列之間的差異mysql> SELECT t, val, LAG(val) OVER w AS 'lag', LEAD(val) OVER w AS 'lead', val - LAG(val) OVER w AS 'lag diff', val - LEAD(val) OVER w AS 'lead diff' FROM series WINDOW w AS (ORDER BY t); +----------+------+------+------+----------+-----------+ | t | val | lag | lead | lag diff | lead diff | +----------+------+------+------+----------+-----------+ | 12:00:00 | 100 | NULL | 125 | NULL | -25 | | 13:00:00 | 125 | 100 | 132 | 25 | -7 | | 14:00:00 | 132 | 125 | 145 | 7 | -13 | | 15:00:00 | 145 | 132 | 140 | 13 | 5 | | 16:00:00 | 140 | 145 | 150 | -5 | -10 | | 17:00:00 | 150 | 140 | 200 | 10 | -50 | | 18:00:00 | 200 | 150 | NULL | 50 | NULL | +----------+------+------+------+----------+-----------+在此範例中，
LAG()和LEAD()呼叫使用預設的N和default值，分別為 1 和NULL。第一列顯示當
LAG()沒有先前的列時會發生什麼情況：該函數會傳回default值（在此情況下為NULL）。最後一列顯示當LEAD()沒有下一列時會發生同樣的情況。LAG()和LEAD()也用於計算總和而不是差異。請考慮此資料集，其中包含費波納契數列的前幾個數字mysql> SELECT n FROM fib ORDER BY n; +------+ | n | +------+ | 1 | | 1 | | 2 | | 3 | | 5 | | 8 | +------+以下查詢顯示目前列的相鄰列的
LAG()和LEAD()值。它也使用這些函數將目前列的值加上前面和後面列的值。其效果是產生費波納契數列中的下一個數字，以及下下一個數字mysql> SELECT n, LAG(n, 1, 0) OVER w AS 'lag', LEAD(n, 1, 0) OVER w AS 'lead', n + LAG(n, 1, 0) OVER w AS 'next_n', n + LEAD(n, 1, 0) OVER w AS 'next_next_n' FROM fib WINDOW w AS (ORDER BY n); +------+------+------+--------+-------------+ | n | lag | lead | next_n | next_next_n | +------+------+------+--------+-------------+ | 1 | 0 | 1 | 1 | 2 | | 1 | 1 | 2 | 2 | 3 | | 2 | 1 | 3 | 3 | 5 | | 3 | 2 | 5 | 5 | 8 | | 5 | 3 | 8 | 8 | 13 | | 8 | 5 | 0 | 13 | 8 | +------+------+------+--------+-------------+產生費波納契數列初始集合的一種方法是使用遞迴通用表格運算式。如需範例，請參閱 費波納契數列產生。
您無法對此函數的列引數使用負值。
LAST_VALUE([expr)null_treatment]over_clause傳回視窗框架中最後一列的
expr值。over_clause的說明請參閱 第 14.20.2 節，「視窗函數概念和語法」。null_treatment的說明請參閱本節的簡介。如需範例，請參閱
FIRST_VALUE()函數說明。LEAD([expr[,N[,default]])null_treatment]over_clause傳回在其分割區中，領先（在目前列之後）目前列
N列的列之expr值。如果沒有這樣的列，則傳回值為default。例如，如果N為 3，則最後三列的傳回值為default。如果缺少N或default，則預設值分別為 1 和NULL。N必須為字面非負整數。如果N為 0，則會針對目前列評估expr。N不能為NULL，且必須為介於0到263（含）之間的整數，可以採用下列任何形式未簽署的整數常數字面值
位置參數標記 (
?)使用者定義的變數
預存常式中的區域變數
over_clause的說明請參閱 第 14.20.2 節，「視窗函數概念和語法」。null_treatment的說明請參閱本節的簡介。如需範例，請參閱
LAG()函數說明。不允許對此函數的列引數使用負值。
NTH_VALUE([expr,N)from_first_last] [null_treatment]over_clause傳回視窗框架中第
N列的expr值。如果沒有這樣的列，則傳回值為NULL。N必須為字面正整數。from_first_last是 SQL 標準的一部分，但 MySQL 實作只允許FROM FIRST（這也是預設值）。這表示計算從視窗的第一列開始。FROM LAST會被剖析，但會產生錯誤。若要取得與FROM LAST相同的效果（從視窗的最後一列開始計算），請使用ORDER BY以反向排序。over_clause的說明請參閱 第 14.20.2 節，「視窗函數概念和語法」。null_treatment的說明請參閱本節的簡介。如需範例，請參閱
FIRST_VALUE()函數說明。您不能對此函數的列引數使用
NULL。NTILE(N)over_clause將分割區分成
N個群組（儲存貯體），為分割區中的每一列指派其儲存貯體編號，並傳回目前列在其分割區中的儲存貯體編號。例如，如果N為 4，NTILE()會將列分成四個儲存貯體。如果N為 100，NTILE()會將列分成 100 個儲存貯體。N必須為字面正整數。儲存貯體編號傳回值的範圍為 1 到N。N不能為NULL，且必須為介於0到263（含）之間的整數，可以採用下列任何形式未簽署的整數常數字面值
位置參數標記 (
?)使用者定義的變數
預存常式中的區域變數
此函數應與
ORDER BY一起使用，以將分割區的列排序成所需的順序。over_clause如第 14.20.2 節，〈視窗函數概念與語法〉中所述。以下查詢顯示
val欄位中值的集合，以及將列分成兩組或四組時所產生的百分位值。為了方便參考，此查詢也使用ROW_NUMBER()顯示列號mysql> SELECT val, ROW_NUMBER() OVER w AS 'row_number', NTILE(2) OVER w AS 'ntile2', NTILE(4) OVER w AS 'ntile4' FROM numbers WINDOW w AS (ORDER BY val); +------+------------+--------+--------+ | val | row_number | ntile2 | ntile4 | +------+------------+--------+--------+ | 1 | 1 | 1 | 1 | | 1 | 2 | 1 | 1 | | 2 | 3 | 1 | 1 | | 3 | 4 | 1 | 2 | | 3 | 5 | 1 | 2 | | 3 | 6 | 2 | 3 | | 4 | 7 | 2 | 3 | | 4 | 8 | 2 | 4 | | 5 | 9 | 2 | 4 | +------+------------+--------+--------+不允許使用
NTILE(NULL)這個建構。PERCENT_RANK()over_clause傳回分割區中小於目前列值的數值的百分比，但不包含最高值。傳回值的範圍從 0 到 1，並代表列的相對排名，計算公式如下，其中
rank為列的排名，而rows為分割區列的數量(rank - 1) / (rows - 1)此函數應與
ORDER BY一起使用，以將分割區的列排序成所需的順序。若沒有ORDER BY，則所有列都視為同等。over_clause如第 14.20.2 節，〈視窗函數概念與語法〉中所述。如需範例，請參閱
CUME_DIST()函數說明。RANK()over_clause傳回目前列在其分割區中的排名，包含間隙。同等值被視為平手並獲得相同的排名。如果存在大小大於一的群組，此函數不會為同等群組指派連續排名；結果為非連續排名編號。
此函數應與
ORDER BY一起使用，以將分割區的列排序成所需的順序。若沒有ORDER BY，則所有列都視為同等。over_clause如第 14.20.2 節，〈視窗函數概念與語法〉中所述。以下查詢顯示
RANK()（產生含間隙的排名）與DENSE_RANK()（產生不含間隙的排名）之間的差異。此查詢顯示val欄位中一組值中每個成員的排名值，其中包含一些重複項。RANK()為同等值（重複項）指派相同的排名值，且下一個較大的值的排名會高出同等值數量減一。DENSE_RANK()也會為同等值指派相同的排名值，但下一個較大的值的排名會大 1。為了方便參考，此查詢也使用ROW_NUMBER()顯示列號mysql> 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); +------+------------+------+------------+ | val | row_number | rank | dense_rank | +------+------------+------+------------+ | 1 | 1 | 1 | 1 | | 1 | 2 | 1 | 1 | | 2 | 3 | 3 | 2 | | 3 | 4 | 4 | 3 | | 3 | 5 | 4 | 3 | | 3 | 6 | 4 | 3 | | 4 | 7 | 7 | 4 | | 4 | 8 | 7 | 4 | | 5 | 9 | 9 | 5 | +------+------------+------+------------+ROW_NUMBER()over_clause傳回目前列在其分割區中的編號。列號的範圍從 1 到分割區列的數量。
ORDER BY會影響列的編號順序。若沒有ORDER BY，列的編號將不確定。ROW_NUMBER()為同等值指派不同的列號。若要為同等值指派相同的值，請使用RANK()或DENSE_RANK()。如需範例，請參閱RANK()函數說明。over_clause如第 14.20.2 節，〈視窗函數概念與語法〉中所述。