視窗函數¶

視窗函數對查詢結果的各個資料列執行計算。它們在 HAVING 子句之後但在 ORDER BY 子句之前執行。呼叫視窗函數需要使用 OVER 子句的特殊語法來指定視窗，如下所示

function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [frame]
)

frame 是以下其中之一

{RANGE|ROWS|GROUPS} frame_start {RANGE|ROWS|GROUPS} BETWEEN frame_start AND frame_end

frame_start 和 frame_end 可以是以下任何一項

UNBOUNDED PRECEDING
expression PRECEDING
CURRENT ROW
expression FOLLOWING
UNBOUNDED FOLLOWING

視窗定義有 3 個組成部分

• PARTITION BY 子句將輸入資料列分隔到不同的分割區中。這類似於 GROUP BY 子句如何將資料列分隔到不同的群組以進行聚合函數。如果未指定 PARTITION BY，則整個輸入將被視為單一分割區。

• ORDER BY 子句決定視窗函數處理輸入資料列的順序。如果未指定 ORDER BY，則順序未定義。請注意，視窗函數中的 ORDER BY 子句不支援序數。您需要使用實際的表達式

• frame 子句指定要由函數針對給定輸入資料列處理的資料列滑動視窗。框架可以是 ROWS 類型、RANGE 類型或 GROUPS 類型，並且它從 frame_start 執行到 frame_end。如果未指定 frame_end，則使用預設值 CURRENT ROW。

  在 ROWS 模式中，CURRENT ROW 具體指當前資料列。在 RANGE 和 GROUPS 模式中，CURRENT ROW 指的是當前資料列的任何同級資料列，以用於 ORDER BY。如果未指定 ORDER BY，則所有資料列都視為當前資料列的同級資料列。在 RANGE 和 GROUPS 模式中，CURRENT ROW 的 frame 開始是指當前資料列的第一個同級資料列，而 CURRENT ROW 的 frame 結束是指當前資料列的最後一個同級資料列。

  在 ROWS 模式中，expression PRECEDING 或 expression FOLLOWING 的 frame 開始和結束將 frame 的開始或結束定義為當前資料列之前或之後的指定資料列數。expression 的類型必須為 INTEGER。

  在 RANGE 模式中，expression PRECEDING 或 expression FOLLOWING 的 frame 開始和結束將 frame 的開始或結束定義為排序鍵與當前資料列的值差異。排序鍵必須與 expression 的類型相同，或可以強制轉換為與 expression 相同的類型。

  在 GROUPS 模式中，expression PRECEDING 或 expression FOLLOWING 的 frame 開始和結束將 frame 的開始或結束定義為與當前資料列的群組數量。一個群組包含排序鍵上具有相同值的所有資料列。expression 的類型必須為 INTEGER 或 BIGINT。

  如果未指定 frame，則使用預設 frame RANGE UNBOUNDED PRECEDING。

範例¶

以下查詢按價格對每個業務員的訂單進行排名

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

以下查詢示範 frame 定義中 ROWS、RANGE 和 GROUPS 之間的差異

SELECT
    ARRAY_AGG(v) OVER (
        ORDER BY k ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM (
    VALUES (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd'), (5, 'e')
) t(k, v); -- ['a', 'b'], ['a', 'b', 'c'], ['b', 'c', 'd'], ['c', 'd', 'e'], ['d', 'e']

SELECT
    ARRAY_AGG(v) OVER (
        ORDER BY k ASC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM (
    VALUES (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd'), (5, 'e')
) t(k, v); -- ['a', 'b'], ['a', 'b'], ['c', 'd'], ['c', 'd'], ['e']

SELECT
    ARRAY_AGG(v) OVER (
        ORDER BY k ASC GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM (
    VALUES (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd'), (5, 'e')
) t(k, v); -- ['a', 'b', 'c', 'd'], ['a', 'b', 'c', 'd'], ['a', 'b', 'c', 'd', 'e'], ['a', 'b', 'c', 'd', 'e'], ['c', 'd', 'e']

聚合函數¶

所有 聚合函數 都可以透過新增 OVER 子句用作視窗函數。將針對目前資料列視窗框架內的各個資料列計算聚合函數。

例如，以下查詢會產生每個業務員每日訂單價格的滾動總和

SELECT clerk, orderdate, orderkey, totalprice,
       sum(totalprice) OVER (PARTITION BY clerk
                             ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey

排名函數¶

cume_dist() -> double()¶

傳回一組值中某個值的累積分佈。結果是視窗分割區的視窗順序中在該資料列之前或與之同級的資料列數量，除以視窗分割區中的資料列總數。因此，順序中的任何相等值都將評估為相同的分佈值。

dense_rank() -> bigint()¶

傳回值群組中值的排名。這與 rank() 類似，但相同的值不會在序列中產生間隙。

ntile(n) -> bigint()¶

將每個視窗分割區的列分成 n 個儲存桶，範圍從 1 到最多 n。儲存桶值最多相差 1。如果分割區中的列數無法平均分配到儲存桶數量，則剩餘的值會每個儲存桶分配一個，從第一個儲存桶開始。

例如，如果有 6 列和 4 個儲存桶，則儲存桶值將如下所示：1 1 2 2 3 4

percent_rank() -> double()¶

傳回值群組中值的百分比排名。結果為 (r - 1) / (n - 1)，其中 r 是列的 rank()，而 n 是視窗分割區中的總列數。

rank() -> bigint()¶

傳回值群組中值的排名。排名是一加上與該列非同級的列之前面的列數。因此，排序中相同的值會在序列中產生間隙。排名是針對每個視窗分割區執行的。

row_number() -> bigint()¶

根據視窗分割區內列的排序，為每一列傳回一個從一開始的唯一循序數字。

值函數¶

值函數提供一個選項，可在評估函數時指定如何處理空值。空值可以被忽略 (IGNORE NULLS) 或尊重 (RESPECT NULLS)。預設情況下，會尊重空值。如果指定 IGNORE NULLS，則所有值表達式為 null 的列都會從計算中排除。如果指定 IGNORE NULLS 且所有列的值表達式都為 null，則會傳回 default_value，如果未指定，則會傳回 null。

first_value(x) -> [與輸入相同]()¶

傳回視窗的第一個值。

last_value(x) -> [與輸入相同]()¶

傳回視窗的最後一個值。

nth_value(x, offset) -> [與輸入相同]()¶

傳回視窗開頭指定偏移量的值。偏移量從 1 開始。偏移量可以是任何純量表達式。如果偏移量為 null 或大於視窗中的值數，則傳回 null。偏移量為零或負數會是錯誤。

lead(x[, offset [, default_value]]) -> [與輸入相同]()¶

傳回視窗分割區中目前列之後 offset 列的值。偏移量從 0 開始，也就是目前列。偏移量可以是任何純量表達式。預設的 offset 為 1。如果偏移量為 null，則傳回 null。如果偏移量參照的列不在分割區內，則會傳回 default_value，如果未指定，則會傳回 null。

lag(x[, offset [, default_value]]) -> [與輸入相同]()¶

傳回視窗分割區中目前列之前 offset 列的值。偏移量從 0 開始，也就是目前列。偏移量可以是任何純量表達式。預設的 offset 為 1。如果偏移量為 null，則傳回 null。如果偏移量參照的列不在分割區內，則會傳回 default_value，如果未指定，則會傳回 null。