ウィンドウ関数

ウィンドウ関数は、関連する行のグループ(「ウィンドウ」)で動作します。

ウィンドウ関数が呼び出されるたびに、行(ウィンドウ内の現在の行)と現在の行を含む行のウィンドウが渡されます。ウィンドウ関数は、入力行ごとに1つの出力行を返します。出力は、関数に渡される個々の行と、関数に渡されるウィンドウ内の他の行の値に依存します。

一部のウィンドウ関数は順序に依存します。順序依存ウィンドウ関数には、主に次の2つのタイプがあります。

  • ランク関連関数。

  • ウィンドウフレーム関数。

ランク関連関数は、行の「ランク」に基づいて情報をリストします。例えば、年間利益の降順に店舗をランク付けすると、最も利益の高い店舗が1にランク付けされます。2番目に収益性の高い店舗は2などにランク付けされます。

ウィンドウフレーム関数を使用すると、ウィンドウ内の行のサブセットに対して移動合計や移動平均の計算などのローリング操作を実行できます。

ウィンドウ関数、ランク関連関数、またはウィンドウフレーム関数の知識が少ないユーザーは、 ウィンドウ関数の使用 にある概念についての資料に目を通すことをお勧めします。

このトピックの内容:

概要

ウィンドウとは何ですか?

ウィンドウは、関連する行のグループです。例えば、タイムスタンプに基づいてウィンドウを定義し、同じ月のすべての行を同じウィンドウにグループ化できます。または、場所に基づいてウィンドウを定義し、特定の都市のすべての行を同じウィンドウにグループ化することもできます。

ウィンドウは、0、1、または複数の行で構成できます。簡素化するために、Snowflakeのドキュメントでは通常、ウィンドウには複数の行が含まれていると書かれています。

ウィンドウ関数とは何ですか?

ウィンドウ関数は、行のウィンドウで動作する関数です。

通常、ウィンドウ関数には次の2つのパラメーターが渡されます。

  • 行。より正確には、ウィンドウ関数には0個以上の式が渡されます。ほとんどすべての場合、これらの式の少なくとも1つはその行の列を参照します。(ほとんどのウィンドウ関数は少なくとも1つの列または式を必要としますが、少数のランク関連関数などの一部のウィンドウ関数は明示的な列または式を必要としません。)

  • その行を含む関連行のウィンドウ。ウィンドウは、テーブル全体でも、テーブル内の行のサブセットでもかまいません。

    非ウィンドウ関数の場合、すべての引数は通常、関数に明示的に渡されます。例:

    MY_FUNCTION(引数1、引数2、...)

    ウィンドウ関数の動作は異なります。現在の行は通常の方法で引数として渡されますが、ウィンドウは OVER 句と呼ばれる別の句を介して渡されます。 OVER 句の構文については、後で説明します。

ウィンドウをサポートする関数のリスト

以下のリストは、すべてのウィンドウ関数を示しています。

ウィンドウフレーム関数としてリストされている一部の関数は、可能なすべてのタイプのウィンドウフレームをサポートしていないことに注意してください。

関数名

ウィンドウ

ウィンドウ フレーム

ランク関連

メモ

一般ウィンドウ

標準のウィンドウ構文を使用しません。

他のウィンドウ関数とは異なる構文を使用します。

他のウィンドウ関数とは異なる構文を使用します。

STDDEV_POP のエイリアス。

VAR_POP のエイリアス。

VAR_SAMP のエイリアス。

ランク関連

範囲ベースの累積ウィンドウフレームをサポートしますが、他のタイプのウィンドウフレームはサポートしません。

実際のウィンドウ関数ではありません(つまり、構文でウィンドウを使用しない)。ただし、行の範囲に対してウィンドウのような操作を実行します。

ビット単位の集計

ブール集計

ハッシュ

半構造化データの集計

線形回帰

統計と確率

カーディナリティの推定 .HyperLogLog使用

HLL のエイリアス。

集約関数ではなく、 HLL_ACCUMULATE または HLL_COMBINE からのスカラー入力を使用します。

類似度の推定 .MinHash使用

APPROXIMATE_SIMILARITY のエイリアス。

周波数推定 .省スペース使用

集約関数ではなく、 APPROX_TOP_K_ACCUMULATE または APPROX_TOP_K_COMBINE からのスカラー入力を使用します。

パーセンタイル推定 .t-Digest使用

集約関数ではなく、 APPROX_PERCENTILE_ACCUMULATE または APPROX_PERCENTILE_COMBINE からのスカラー入力を使用します。

ウィンドウ関数と NULL 値

一部の関数は NULL 値を無視します。例えば、 AVG は、次の式に基づいて、値 15NULL の平均を 3 として計算します。

(1 + 5) / 2 = 3

分子と分母の両方で、非 NULL 値のみが使用されます。

関数に渡されるすべての値が NULL の場合、関数は NULLを返します。

一部のウィンドウ関数には、複数の列を渡すことができます。例:

SELECT COUNT(col1, col2) FROM table1;

これらのインスタンスでは、個々の列が NULLの場合、関数は行を無視します。

例えば、次のクエリでは、 COUNT4 ではなく 1 を返します。これは、選択した列に4行のうち3行に少なくとも1つの NULL 値が含まれているためです。

テーブルを作成し、値を入力します。

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

テーブルをクエリします。

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

同様に、2つ以上の列を参照する式で SUM が呼び出され、それらの列の1つ以上が NULLである場合、式は NULLに評価され、行は無視されます。

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

この動作は、一部の列が NULLである場合に行を破棄しない GROUP BY の動作とは異なります。

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

使用例

店舗のチェーンを所有しているとします。次のクエリは、各店舗で生成されたチェーン全体の利益の割合を示しています。クエリは RATIO_TO_REPORT 関数を使用します。この関数は、現在の行から値(例:net_profit)を取得し、それを他のすべての行の対応する値の合計(net_profit)で除算します。

テーブルを作成してロードします。

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;

テーブルをクエリします。

SELECT branch_ID,
       net_profit,
       100 * RATIO_TO_REPORT(net_profit) OVER () AS percent_of_chain_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+------------+-------------------------+
| BRANCH_ID | NET_PROFIT | PERCENT_OF_CHAIN_PROFIT |
|-----------+------------+-------------------------|
|         1 |   10000.00 |             22.72727300 |
|         2 |   15000.00 |             34.09090900 |
|         3 |   10000.00 |             22.72727300 |
|         4 |    9000.00 |             20.45454500 |
+-----------+------------+-------------------------+

ウィンドウフレームとは何ですか?

ウィンドウフレームは、ウィンドウ内の行のサブグループです。サブセットを作成すると、指定された行のサブグループだけで値を計算できます。ウィンドウフレームは、 OVER 句の ORDER BY 副次句で追加の副次句として指定されます。

ウィンドウフレームの種類

Snowflakeは、2種類のウィンドウフレームをサポートしています。

累積

ウィンドウの先頭から現在の行まで、または現在の行からウィンドウの最後までのローリング値の計算を有効にします。

スライディング

現在の行を基準にして、ウィンドウ内の任意の2行(この値を含む)間のローリング値を計算できるようにします。

ウィンドウフレームに使用される構文を含むウィンドウフレームの詳細については、 ウィンドウフレームの構文と使用法 をご参照ください。

ウィンドウの構文と使用法

ウィンドウ構文

<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )

OVER 句は、関数がウィンドウ関数として使用されていることを指定します。

PARTITION BY サブ句を使用すると、行をサブグループにグループ化できます(例:都市、年など)。 PARTITION BY 句はオプションです。サブグループに分割することなく、行のグループ全体を分析できます。

ORDER BY 句は、ウィンドウ内の行を順序付けします。(これは、クエリの出力の順序付けとは異なります。クエリには、ウィンドウ内の行の順序を制御する ORDER BY 句 と、クエリ全体の出力順序を制御する OVER 句の外側にある別の ORDER BY 句があります。) ORDER BY 句は一部のウィンドウ関数ではオプションですが、他の関数では必須です。例えば、ウィンドウフレーム関数とランク関連関数では、データが意味のある順序になっている必要があるため、 ORDER BY サブ句が必要です。

注釈

ウィンドウフレームをサポートする関数では、変更/強化された構文が使用されます。詳細については、 ウィンドウフレームの構文と使用法 (このトピック)をご参照ください。

ウィンドウの使用上の注意

  • OVER 句は、関数が動作するウィンドウを指定します。この句は、次のコンポーネントの1つ(または両方)で構成されます。

    • PARTITION BY 式1: ウィンドウのパーティション(ある場合)を定義する副次句(つまり、関数を適用する前にデータをグループ化する方法)。

    • ORDER BY 式2: ウィンドウ内の行の順序を決定する副次句。

      ORDER BY サブ句は、クエリ ORDER BY 句のルールと同様のルールに従います。例えば、 ASC/DESC (昇順/降順)および NULL の処理に関してです。サポートされている追加オプションの詳細については、 ORDER BY クエリ構成を参照してください。

      さまざまな関数が ORDER BY 句をさまざまな方法で処理します。

      • 一部のウィンドウ関数には ORDER BY 句が必要です。

      • 一部のウィンドウ関数では、 ORDER BY 句が禁止されています。

      • 一部のウィンドウ関数は、存在する場合は ORDER BY 句を使用しますが、必須ではありません。

      • 一部のウィンドウ関数は、 ORDER BY 句を暗黙的な累積ウィンドウフレーム句として扱います。(暗黙的なウィンドウフレームの詳細については、 ウィンドウフレームの使用上の注意 に記載されています。)

ウィンドウフレームの構文と使用法

ウィンドウフレームの構文

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] )

条件:

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }
slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

注釈

ウィンドウフレームでは、ウィンドウ内のデータが既知の順序であることが必要です。したがって、 ORDER BY 句は一般的なウィンドウ関数構文ではオプションですが、 OVER 句内の ORDER BY はウィンドウフレーム構文では 必須 です。

ウィンドウフレームの使用上の注意

  • 累積ウィンドウフレームの場合:

    • ROWS パーティションの先頭または末尾から現在の行までのすべての行を使用して、(指定された ORDER BY 副次句に従って)現在の行の結果を計算します。

    • RANGEROWS と似ていますが、現在の行と同じ値を持つ行の結果を計算するだけです(指定された ORDER BY 副次句に従う)。

  • スライディングウィンドウフレームの場合:

    • ROWS は包括的であり、常に現在の行に対して相対的です。

    • RANGE はサポートされていません。

    • 指定された数の前後の ROWS がウィンドウの制限を超えている場合、Snowflakeは値を NULL として扱います。

  • ウィンドウフレームが指定されていない場合、デフォルトは関数によって異なります。

    • 非ランク関連関数(COUNTMIN / MAXSUM)の場合、デフォルトは次の累積ウィンドウフレームです( ANSI 標準に準拠)。

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    • ランク関連関数(FIRST_VALUELAST_VALUENTH_VALUE)の場合、デフォルトは次のウィンドウ全体です。

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      これは ANSI 標準とは 異なります

一般的なヒント

  • この構文は、ウィンドウ関数のオプションとして OVER 句のすべての副次句を示しています。これは仕様によるものです(つまり、括弧内の副次句なしで OVER を使用できます)。これは、特定のシナリオ(例:並列処理をオフにする)で役立ちます。

  • PARTITION BY は、常に GROUP BY と互換性があるとは限りません。

  • クエリで複数のウィンドウ関数を使用する場合、通常、各関数の入力データセットを同じ方法でパーティション分割する必要があります。例えば、次の最初のステートメントは、次の2番目のステートメントよりも正しい可能性が高くなります。

    SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;
    
    SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
    

    エラーメッセージ SQL compilation error: ... is not a valid group by expression は、多くの場合、 SELECT ステートメントの「プロジェクト」句の異なる列が同じ方法でパーティション分割されていないため、異なる行数を生成する可能性があることを示しています。

追加の例は、 ウィンドウ関数の使用 に記載されています。

累積ウィンドウフレームの例

値が含まれるテーブルを作成および生成します。

CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
    (200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
    (300, 1, null), (300, 1, null);

累積ウィンドウフレームを使用するクエリを実行し、出力を表示します。テーブルの指定されたウィンドウ内の行の累積カウント、合計、最小、最大を返します。

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+

テーブルの指定されたウィンドウ内の行の範囲ごとの累積カウント、合計、最小、最大を返します。

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

デフォルトのウィンドウフレームセマンティクス( RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)を使用して、上記のクエリと同じ結果を返します。

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

スライディングウィンドウフレームの例

値が含まれるテーブルを作成および生成します。

CREATE TABLE example_sliding
  (p INT, o INT, i INT, r INT, s VARCHAR(100));

INSERT INTO example_sliding VALUES
  (100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
  (200,11,NULL,90,'ninety'),
  (300,12,12,30,'thirty'),
  (400,13,NULL,20,'twenty');

現在の行の前、後、および包括的なスライディングウィンドウ全体の2つの列(数値および文字列)の最小値を返します。

select p, o, i AS i_col,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
    s,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S       | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           2 |           1 | seventy | NULL        | forty       | forty       |
| 100 |  2 |     2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |     3 |           1 |           5 |           2 | forty   | seventy     | fifty       | fifty       |
| 100 |  4 |  NULL |           1 |           5 |           3 | ninety  | forty       | fifty       | fifty       |
| 100 |  5 |     5 |           2 |           6 |           5 | fifty   | forty       | thirty      | fifty       |
| 100 |  6 |     6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |     7 |        NULL |          10 |           7 | forty   | NULL        | n_u_l_l     | forty       |
| 200 |  8 |  NULL |           7 |          10 |           7 | n_u_l_l | forty       | n_u_l_l     | forty       |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | ninety      | n_u_l_l     |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | forty       | ninety      | n_u_l_l     |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | n_u_l_l     | NULL        | ninety      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

現在の行の前、後、および包括的なスライディングウィンドウ全体の2つの列(数値および文字列)の最大値を返します。

SELECT p, o, i AS i_col,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
    s,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S       | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           3 |           3 | seventy | NULL        | thirty      | thirty      |
| 100 |  2 |     2 |           1 |           5 |           5 | thirty  | seventy     | ninety      | thirty      |
| 100 |  3 |     3 |           2 |           6 |           6 | forty   | thirty      | thirty      | thirty      |
| 100 |  4 |  NULL |           3 |           6 |           6 | ninety  | thirty      | thirty      | thirty      |
| 100 |  5 |     5 |           3 |           6 |           6 | fifty   | thirty      | thirty      | thirty      |
| 100 |  6 |     6 |           5 |        NULL |           6 | thirty  | ninety      | NULL        | thirty      |
| 200 |  7 |     7 |        NULL |          10 |          10 | forty   | NULL        | twenty      | twenty      |
| 200 |  8 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | twenty      | twenty      |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | n_u_l_l     | twenty      | twenty      |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | n_u_l_l     | ninety      | twenty      |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | twenty      | NULL        | twenty      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

現在の行の前、後、および包括的なスライディングウィンドウ全体の数値列の合計を返します。

SELECT p, o, r AS r_col,
    SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+
|   P |  O | R_COL | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+-------+-------------+-------------+-------------|
| 100 |  1 |    70 |        NULL |         180 |         280 |
| 100 |  2 |    30 |        NULL |         170 |         310 |
| 100 |  3 |    40 |          70 |          80 |         310 |
| 100 |  4 |    90 |         100 |          30 |         240 |
| 100 |  5 |    50 |         140 |        NULL |         210 |
| 100 |  6 |    30 |         160 |        NULL |         170 |
| 200 |  7 |    40 |        NULL |         110 |         150 |
| 200 |  8 |  NULL |        NULL |         110 |         150 |
| 200 |  9 |  NULL |          40 |          90 |         150 |
| 200 | 10 |    20 |          40 |        NULL |         110 |
| 200 | 11 |    90 |          40 |        NULL |         110 |
| 300 | 12 |    30 |        NULL |        NULL |          30 |
| 400 | 13 |    20 |        NULL |        NULL |          20 |
+-----+----+-------+-------------+-------------+-------------+