ウィンドウ関数¶

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

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

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

ランク関連関数。

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

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

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

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

このトピックの内容：

概要¶

ウィンドウとは何ですか？¶

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

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

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

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

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

行。より正確には、ウィンドウ関数に0個以上の式が渡されます。ほとんどすべての場合、これらの式の少なくとも1つはその行の列を参照します。（ほとんどのウィンドウ関数は少なくとも1つの列または式を必要としますが、少数のランク関連関数といった一部のウィンドウ関数は、明示的な列または式を必要としません。）
その行を含む関連行のウィンドウ。ウィンドウは、テーブル全体でも、テーブル内の行のサブセットでもかまいません。

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

MY_FUNCTION（引数1、引数2、...）

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

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

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

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

関数名	ウィンドウ	ウィンドウフレーム	ランク関連	メモ
一般ウィンドウ
ANY_VALUE	✔
AVG	✔	✔
CONDITIONAL_CHANGE_EVENT	✔
CONDITIONAL_TRUE_EVENT	✔
CORR	✔
COUNT	✔	✔
COUNT_IF	✔	✔
COVAR_POP	✔
COVAR_SAMP	✔
LISTAGG	✔			標準のウィンドウ構文を使用しません。
MAX	✔	✔
MEDIAN	✔
MIN	✔	✔
MODE	✔
PERCENTILE_CONT	✔			他のウィンドウ関数とは異なる構文を使用します。
PERCENTILE_DISC	✔			他のウィンドウ関数とは異なる構文を使用します。
RATIO_TO_REPORT	✔
STDDEV, STDDEV_SAMP	✔	✔		STDDEV と STDDEV_SAMP はエイリアスです。
STDDEV_POP	✔	✔
SUM	✔	✔
VAR_POP	✔	✔
VAR_SAMP	✔	✔
VARIANCE_POP	✔	✔		VAR_POP のエイリアス。
VARIANCE , VARIANCE_SAMP	✔	✔		VAR_SAMP のエイリアス。

ランク関連
CUME_DIST	✔		✔
DENSE_RANK	✔	✔	✔
FIRST_VALUE	✔	✔	✔
LAG	✔		✔
LAST_VALUE	✔	✔	✔
LEAD	✔		✔
NTH_VALUE	✔	✔	✔
NTILE	✔		✔
PERCENT_RANK	✔	✔	✔	範囲ベースの累積ウィンドウフレームをサポートしますが、他のタイプのウィンドウフレームはサポートしません。
RANK	✔	✔	✔
ROW_NUMBER	✔		✔
ビット単位の集計
BITAND_AGG	✔
BITOR_AGG	✔
BITXOR_AGG	✔
ブール集計
BOOLAND_AGG	✔
BOOLOR_AGG	✔
BOOLXOR_AGG	✔
ハッシュ
HASH_AGG	✔
半構造化データの集計
ARRAY_AGG	✔
OBJECT_AGG	✔
異なる値のカウント
ARRAY_UNION_AGG	✔
ARRAY_UNIQUE_AGG	✔
線形回帰
REGR_AVGX	✔
REGR_AVGY	✔
REGR_COUNT	✔
REGR_INTERCEPT	✔
REGR_R2	✔
REGR_SLOPE	✔
REGR_SXX	✔
REGR_SXY	✔
REGR_SYY	✔
統計と確率
KURTOSIS	✔
カーディナリティの推定 . （ HyperLogLog を使用）
APPROX_COUNT_DISTINCT	✔			HLL のエイリアス。
HLL	✔
HLL_ACCUMULATE	✔
HLL_COMBINE	✔
HLL_ESTIMATE				集約関数ではなく、 HLL_ACCUMULATE または HLL_COMBINE からのスカラー入力を使用します。
HLL_EXPORT	✔
HLL_IMPORT	✔
類似度の推定 . （ MinHash を使用）
APPROXIMATE_JACCARD_INDEX	✔			APPROXIMATE_SIMILARITY のエイリアス。
APPROXIMATE_SIMILARITY	✔
MINHASH	✔
MINHASH_COMBINE	✔
頻度推定 . （省スペースを使用）
APPROX_TOP_K	✔
APPROX_TOP_K_ACCUMULATE	✔
APPROX_TOP_K_COMBINE	✔
APPROX_TOP_K_ESTIMATE				集約関数ではなく、 APPROX_TOP_K_ACCUMULATE または APPROX_TOP_K_COMBINE からのスカラー入力を使用します。
パーセンタイル推定 . （ t-Digest を使用）
APPROX_PERCENTILE	✔
APPROX_PERCENTILE_ACCUMULATE	✔
APPROX_PERCENTILE_COMBINE	✔
APPROX_PERCENTILE_ESTIMATE				集約関数ではなく、 APPROX_PERCENTILE_ACCUMULATE または APPROX_PERCENTILE_COMBINE からのスカラー入力を使用します。

ウィンドウ関数と NULL 値¶

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

(1 + 5) / 2 = 3

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

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

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

SELECT COUNT(col1, col2) FROM table1;

Copy

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

例えば、次のクエリでは、 COUNT は 4 ではなく 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.

Copy

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

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

Copy

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

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

Copy

この動作は、一部の列が 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 |
+-------+-------+

Copy

使用例¶

店舗のチェーンを所有しているとします。次のクエリは、各店舗で生成されたチェーン全体の利益の割合を示しています。クエリは 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;

Copy

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

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 |
+-----------+------------+-------------------------+

Copy

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

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

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

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

累積:: ウィンドウの先頭から現在の行まで、または現在の行からウィンドウの最後までのローリング値の計算を有効にします。
スライディング:: 現在の行を基準にして、ウィンドウ内の任意の2行（この値を含む）間のローリング値を計算できるようにします。

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

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

ウィンドウ構文¶

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

Copy

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

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

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

注釈

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

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

OVER 句は、関数が動作するウィンドウを指定します。この句は、次のコンポーネントの1つ（または両方）で構成されます。
- PARTITION BY expr1: ウィンドウのパーティション（ある場合）を定義するサブ句（つまり、関数を適用する前にデータをグループ化する方法）。
- ORDER BY expr2: ウィンドウ内の行の順序を決定するサブ句。
  
  ORDER BY サブ句は、 ASC/DESC （昇順/降順）および NULL のなどの処理で、クエリ ORDER BY 句のルールと同様のルールに従います。サポートされている追加オプションの詳細については、 ORDER BY クエリ構成を参照してください。
  
  さまざまな関数が ORDER BY 句をさまざまな方法で処理します。
  一部のウィンドウ関数には ORDER BY 句が必要です。
  
  一部のウィンドウ関数では、 ORDER BY 句が禁止されています。
  
  一部のウィンドウ関数は、存在する場合は ORDER BY 句を使用しますが、必須ではありません。
  
  一部のウィンドウ関数は、ORDER BY句を暗黙的な累積ウィンドウフレーム句として扱います。（暗黙的なウィンドウフレームの詳細については、ウィンドウフレームの使用上の注意に記載されています。）
  
  注釈
  
  わかりやすくするために、Snowflakeでは暗黙的なウィンドウフレームを避けることをお勧めします。クエリでウィンドウフレームを使用する場合は、明示的なウィンドウフレームにします。

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

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

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

Copy

条件：

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

Copy

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
    }

Copy

注釈

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

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

累積ウィンドウフレームの場合:
- ROWS パーティションの先頭または末尾から現在の行までのすべての行を使用して、（指定された ORDER BY 副次句に従って）現在の行の結果を計算します。
- RANGE は ROWS と似ていますが、現在の行と同じ値を持つ行の結果を計算するだけです（指定された ORDER BY 副次句に従う）。
スライディングウィンドウフレームの場合:
- ROWS は包括的であり、常に現在の行に対して相対的です。
- RANGE はサポートされていません。
- 指定された数の前後の ROWS がウィンドウの制限を超えている場合、Snowflakeは値を NULL として扱います。
ウィンドウフレームが指定されていない場合、デフォルトは関数によって異なります。
- 非ランク関連関数（COUNT、 MAX、 MIN、 SUM）の場合、デフォルトは次の累積ウィンドウフレームです（ANSI 標準に準拠）。
  
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- ランク関連関数（FIRST_VALUE、 LAST_VALUE、 NTH_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) ... ;
```
Copy
エラーメッセージ 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);

Copy

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

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 |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+

Copy

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

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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Copy

デフォルトのウィンドウフレームセマンティクス（ 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 |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Copy

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

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

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');

Copy

現在の行の前、後、および包括的なスライディングウィンドウ全体の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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Copy

現在の行の前、後、および包括的なスライディングウィンドウ全体の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      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Copy

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

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 |
+-----+----+-------+-------------+-------------+-------------+

Copy

ランク関連関数の例¶

次の例は、それぞれが販売した合計金額（ドル）に基づいて営業担当者をランク付けする方法を示しています。クエリは OVER 句を使用して、各営業担当者の総売上を含むウィンドウを作成します。 OVER 句内の ORDER BY 副次句は、これらの行を売上合計の降順（つまり、最高から最低）に並べます。次に、クエリは他の営業担当者に対する各営業担当者のランクを計算します。

最初に、テーブルを作成してデータを挿入します。

CREATE TABLE sales_table (salesperson_name VARCHAR, sales_in_dollars INTEGER);
INSERT INTO sales_table (salesperson_name, sales_in_dollars) VALUES
    ('Smith', 600),
    ('Jones', 1000),
    ('Torkelson', 700),
    ('Dolenz', 800);

Copy

次に、データをクエリします。

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

出力は必ずしもランク順であるとは限りません。以下に示すように、 ORDER BY 句（ OVER 句の外側）を使用して、ランク順に出力を強制的に表示できます。

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY 3;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

上記の例には 2つの ORDER BY 句があります。

1つはランキングの順序を制御します。
1つは出力の順序を制御します。

これらの句は独立しています。例えば、総売上に基づいて（上記のように）ランキングを並べ替えることができますが、販売員の姓に基づいて出力行を並べ替えることもできます。

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY salesperson_name;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Dolenz           |              800 |          2 |
| Jones            |             1000 |          1 |
| Smith            |              600 |          4 |
| Torkelson        |              700 |          3 |
+------------------+------------------+------------+

Copy

ウィンドウ関数¶

概要¶

ウィンドウとは何ですか？¶

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

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

ウィンドウ関数と NULL 値¶

使用例¶

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

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

ランク関連関数とは¶

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

ウィンドウ構文¶

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

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

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

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

ランク関連のウィンドウ関数の構文と使用法¶

ランク関連のウィンドウ関数の構文¶

ランク関連のウィンドウ関数の使用上の注意¶

一般的なヒント¶

例¶

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

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

ランク関連関数の例¶