ウィンドウ関数¶

ウィンドウ関数は、クエリ結果の行全体で計算を実行します。HAVING句の後、ORDER BY句の前に実行されます。ウィンドウ関数を呼び出すには、次のようにOVER句を使用してウィンドウを指定する特別な構文が必要です。

function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [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のフレーム開始は現在の行の最初のピア行を指し、CURRENT ROWのフレーム終了は現在の行の最後のピア行を指します。

ROWSモードでは、expression PRECEDINGまたはexpression FOLLOWINGのフレームの開始と終了は、現在の行の前または後の指定された行数の開始または終了を定義します。expressionはINTEGER型である必要があります。

RANGEモードでは、expression PRECEDINGまたはexpression FOLLOWINGのフレームの開始と終了は、現在の行からのソートキーの値の差を定義します。ソートキーはexpressionと同じ型であるか、expressionと同じ型に変換できる必要があります。

GROUPSモードでは、expression PRECEDINGまたはexpression FOLLOWINGのフレームの開始と終了は、現在の行からのグループ数を定義します。グループには、ソートキーで同じ値を持つすべての行が含まれます。expressionの型はINTEGERまたはBIGINTである必要があります。

フレームが指定されていない場合、RANGE UNBOUNDED PRECEDINGのデフォルトフレームが使用されます。

例¶

次のクエリは、担当者ごとに価格で注文をランク付けします。

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

次のクエリは、フレーム定義における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

値関数¶

値関数は、関数を評価する際にNULL値をどのように処理するかを指定するオプションを提供します。NULLは無視することも(IGNORE NULLS)、尊重することも(RESPECT NULLS)できます。デフォルトでは、NULL値は尊重されます。IGNORE NULLSが指定されている場合、値式がNULLであるすべての行は計算から除外されます。IGNORE NULLSが指定され、すべての行で値式がNULLの場合、default_valueが返されます。指定されていない場合はnullが返されます。

first_value(x) -> [same as input]()¶: ウィンドウの最初の値を返します。

last_value(x) -> [same as input]()¶: ウィンドウの最後の値を返します。

nth_value(x, offset) -> [same as input]()¶: ウィンドウの先頭からの指定されたオフセット位置の値を返します。オフセットは1から始まります。オフセットは任意のスカラー式にすることができます。オフセットがNULLの場合、またはウィンドウ内の値の数より大きい場合、NULLが返されます。オフセットがゼロまたは負であることはエラーです。

lead(x[, offset [, default_value]]) -> [same as input]()¶: ウィンドウパーティション内の現在の行のoffset行後の値を返します。オフセットは0から始まり、これは現在の行です。オフセットは任意のスカラー式にすることができます。デフォルトのoffsetは1です。オフセットがnullの場合、nullが返されます。オフセットがパーティション内にある行を参照しない場合、default_valueが返されます。指定されていない場合はnullが返されます。

lag(x[, offset [, default_value]]) -> [same as input]()¶: ウィンドウパーティション内の現在の行のoffset行前の値を返します。オフセットは0から始まり、これは現在の行です。オフセットは任意のスカラー式にすることができます。デフォルトのoffsetは1です。オフセットがnullの場合、nullが返されます。オフセットがパーティション内にある行を参照しない場合、default_valueが返されます。指定されていない場合はnullが返されます。

ウィンドウ関数¶

例¶

集計関数¶

ランキング関数¶

値関数¶