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

このトピックの内容:

Snowflakeは、 ウィンドウ関数 として知られる分析的な SQL 関数を多数サポートしています。各機能の詳細については、個々のリファレンスページに記載されています。このセクションの目的は、 OVER 句の主なコンポーネントの詳細な構文を含め、ウィンドウ関数の一部またはすべてに適用される一般的な参照情報を提供することです:

  • PARTITION BY 句

  • ORDER BY 句

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

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

構文

<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )
Copy

条件:

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
Copy

条件:

windowFrameClause ::=

{
    { ROWS | RANGE } UNBOUNDED PRECEDING
  | { ROWS | RANGE } <n> PRECEDING
  | { ROWS | RANGE } CURRENT ROW
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}
Copy

パラメーター

OVER( [ windowDefinition ] )

この関数がウィンドウ関数として使用されることを指定し、関数が動作するウィンドウを指定します。OVER 句には括弧を入れなければなりませんが、当該関数の要件によっては空でもよいです。空の OVER 句はパーティションを持たず、デフォルトのウィンドウ・フレームを意味します。

PARTITION BY expr1

製品、都市、年などのパーティションに行をグループ化します。入力行はパーティションごとにグループ化され、各パーティションに対して関数が計算されます。PARTITION BY 句は任意です。行の集合を1つのパーティションとして分析することができます。

ORDER BY expr2

各パーティション内の行、またはパーティションが指定されていない場合は行全体の順序を指定します。この ORDER BY 句は、クエリの最終結果で返されるすべての行の順序を制御する ORDER BY 句とは異なります。ORDER BY 句は一部のウィンドウ関数では任意ですが、他の関数では必須です。例えば、 RANK や NTILE のようなランキング・ウィンドウ関数は、入力データが意味のある順番に並んでいる必要があります。

ウィンドウ関数の ORDER BY 句は、 ASC/DESC (昇順/降順)の順序と NULL の処理に関して、クエリのメイン ORDER BY 句と同様の規則に従います。詳細については、 ORDER BY をご参照ください。

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

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

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

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

  • ウィンドウ関数の中には、 ORDER BY 句に暗黙のウィンドウフレームを適用するものがあります。詳細については、 ウィンドウフレームの使用上の注意 をご参照ください。

{ ROWS | RANGE }

物理的な行数 (ROWS) または論理的に計算された行数 (RANGE) を定義します。 範囲ベースと行ベースのウィンドウフレーム をご参照ください。

どちらのタイプのフレームも、暗黙的な名前付き境界または明示的なオフセット値を使用して、始点と終点を指定します。名前付きバウンダリーは、キーワード CURRENT ROW 、 UNBOUNDED PRECEDING 、 UNBOUNDED FOLLOWING で定義されます。明示的なオフセットは、数値または間隔（n PRECEDING または n FOLLOWING）で定義されます。

{ RANGE BETWEEN n PRECEDING | n FOLLOWING }

明示的なオフセットを持つ範囲ベースのウィンドウフレームを指定します。

明示的なオフセットを持つ RANGE BETWEEN ウィンドウ・フレームは、 ORDER BY 式を1つだけ持つ必要があります。その式には次のデータ型がサポートされています。:

  • DATE, TIMESTAMP, TIMESTAMP_LTZ , TIMESTAMP_NTZ (DATETIME) , TIMESTAMP_TZ

  • INT 、 FLOAT などを含む NUMBER

TIME および他のSnowflakeデータ型は、このタイプのウィンドウフレームが使用されている場合はサポートされません。その他のウィンドウ・フレームについては、 ORDER BY 式で、 VARCHAR のような他のデータ型を使うことができます。

RANGE BETWEEN ウィンドウフレームの場合、 n は符号なし定数（0を含む正の数値）または正の INTERVAL 定数である必要があります:

  • expr2 が数値データ型の場合、 n は符号なし定数である必要があります。

  • expr2 が TIMESTAMP データ型の場合、 n は INTERVAL 定数 である必要があります。例: INTERVAL '12 hours' または INTERVAL '3 days' 。

  • expr2 が DATE データ型である場合、 n は符号なし定数でも INTERVAL 定数でもよいですが、フレームの開始と終了では、 n の値に同じデータ型を使用する必要があります。

ORDER BY 式が昇順の場合(ASC)、構文 n FOLLOWING は「x より大きい(または遅い)値を持つ行」を意味し、 n PRECEDING は「x より小さい(または早い)値を持つ行」を意味し、 x は現在の行の ORDER BY 値です。ORDER BY 式が降順（DESC）の場合は、その逆になります。(オフセット 0 PRECEDING と 0 FOLLOWING は CURRENT ROW と等価です)。

RANGE BETWEEN の制限事項

以下のウィンドウ関数のサブセットは、明示的なオフセットを持つ RANGE BETWEEN 構文をサポートしています:

加えて、次のことにも注意してください。

  • これらの関数の DISTINCT バージョンはこの構文をサポートしていません。

  • この構文で COUNT window 関数を使用する場合、以下の制限が適用されます。

    • 入力引数は1つのみ。

    • COUNT(table.*) ワイルドカード・クエリはサポートされていません。たとえば、次は実行できません。

      COUNT(t1.*) OVER(ORDER BY col1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
      Copy

  • フレームの開始位置と終了位置が論理的に逆転するようなフレームを指定することはできません。例えば、以下のフレームは、フレームの終了行が開始行より前にあるため、エラーを返します。

    ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING AND 4 PRECEDING
ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING AND 2 PRECEDING
    Copy

RANGE BETWEEN ORDER BY 式に NULL 値が含まれる場合の動作

RANGE BETWEEN ウィンドウフレームが使用され、 ORDER BY 列が NULL 値を含む場合、以下の動作に注意してください:

  • ORDER BY 句で NULLS FIRST を指定した場合、 ORDER BY 列に NULL を持つ行は、 UNBOUNDED PRECEDING フレームに含まれます。

  • ORDER BY 句で NULLS LAST を指定した場合、 ORDER BY 列に NULL を持つ行は、 UNBOUNDED FOLLOWING フレームに含まれます。

  • ORDER BY 列に NULL を持つ行は、現在の行の ORDER BY 値が NULL である場合にのみ、明示的オフセット枠境界に含まれます。

RANGE BETWEEN ORDER BY 句に NULL の値がある例 をご参照ください。

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

  • 一部のウィンドウ関数はウィンドウフレームをサポートしています。ただし、ウィンドウフレーム構文のサポートは機能によって異なります。ウィンドウフレームが指定されていない場合、デフォルトは関数によって異なります。

    • 非ランキング関数（ COUNT 、 MAX 、 MIN 、 SUM など）の場合、デフォルトは以下のウィンドウフレーム（ ANSI 標準準拠）です。

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      Copy

    • ランキング関数（ FIRST_VALUE、 LAST_VALUE、 NTH_VALUE など）の場合、デフォルトはウィンドウ全体です。

      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      Copy

      この動作は、 ANSI 標準に 準拠していない ことにご注意ください。

      注釈

      わかりやすくするために、Snowflakeでは暗黙的なウィンドウフレームを避けることをお勧めします。クエリでウィンドウフレームを使用する場合は、明示的なウィンドウフレームを定義します。

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

例

このセクションでは、ウィンドウ関数のさまざまな使い方の例を紹介します。その他の例については、 ウィンドウ関数を使ったデータ分析 および各機能のページをご参照ください。

使用例

店舗のチェーンを所有しているとします。次のクエリは、チェーン全体の利益のうち、各店舗が生み出す利益の割合を示しています。クエリは 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

開始位置が拘束されないウィンドウフレーム

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

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

境界のない開始位置を持つウィンドウ・フレームを使用するクエリを実行し、出力を表示します。各パーティションの各行の累積値の COUNT, SUM, AVG, MIN, MAX を返します:

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

デフォルトのウィンドウフレームセマンティクス（ ROWS 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つの列（数値と文字列）に対して、現在の行を含むスライディングウィンドウの前後で、 MIN 関数の結果を返します:

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つの列（数値と文字列）に対して、現在の行を含むスライディングウィンドウの前後で、 MAX 関数の結果を返します:

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 句内の 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 句に加えて）クエリー自体に ORDER BY 句を指定します:

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

先の例には、 ORDER BY 句が 2つ あります:

  • 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

明示的な数値オフセットの RANGE BETWEEN 例

次の例では、 RANGE BETWEEN の構文を使用し、オフセットを明示的に数値で指定しています。この例を実行するには、以下の指示に従ってください： menu_itemsテーブルを作成してロードする INTERVAL オフセットを使用した同様の例については、 ローリング計算にウィンドウ集約を使用する をご参照ください。

以下のクエリは、フードトラックで提供されるメニューのカテゴリーについて平均売上原価を計算します。窓関数は結果を分割しない。したがって、平均は、範囲ベースのフレームに従った、完全な結果セット全体にわたって計算されます。

枠の境界は、現在の行の商品価格に2を足したものです（例えば、最初の行＝0.50＋2.00）。列がこの2ドルの範囲内にあるとき、枠に入る資格があります。

SELECT menu_category, menu_cogs_usd,
    AVG(menu_cogs_usd)
      OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  GROUP BY menu_category, menu_cogs_usd
  ORDER BY menu_category, menu_cogs_usd;
Copy
+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage      |          0.50 |  1.18333 |
| Beverage      |          0.65 |  1.37857 |
| Beverage      |          0.75 |  1.50000 |
| Dessert       |          0.50 |  1.18333 |
| Dessert       |          1.00 |  1.87500 |
| Dessert       |          1.25 |  2.05000 |
| Dessert       |          2.50 |  3.16666 |
| Dessert       |          3.00 |  3.50000 |
| Snack         |          1.25 |  2.05000 |
| Snack         |          2.25 |  2.93750 |
| Snack         |          4.00 |  4.00000 |
+---------------+---------------+----------+

例えば、最初の行の avg_cogs 値は1.1833です。これは、0.50から2.50の間に入るすべての menu_cogs_usd 値の合計を、それらの行の数で割ったものとして計算されます:

(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333

最後から2番目の行のavg_cogsの値は2.93750です。これは、2.25から4.25の間に入るすべての menu_cogs_usd 値の合計を、それらの行の数で割ったものとして計算されます:

(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750

最後の行は、 avg_cogs と menu_cogs_usd の両方で4.0を返しています。この結果が正確なのは、この行だけがフレームに属しているからです。4.0は結果全体における最大値 menu_cogs_usd 、1行のフレームとなります。「以下」の行はありません。

このクエリには、ウィンドウ関数のための ORDER BY 句と、クエリの最終結果のための ORDER BY 句があることにご注意ください。最終的な ORDER BY の出力は、窓関数の結果の計算に影響を与えません。関数を計算するための順序付き結果セットは、最終クエリが表示しない中間結果セットです。

RANGE BETWEEN ORDER BY 句に NULL の値がある例

nulls テーブルには5つの行があり、2つは c1 列に NULL があります。テーブルの作成とロードは次のとおりです。

CREATE OR REPLACE TABLE nulls(c1 int, c2 int);

INSERT INTO nulls VALUES
  (1,10),
  (2,20),
  (3,30),
  (NULL,20),
  (NULL,50);
Copy

NULLS LAST が指定され、ウィンドウフレームが明示的なオフセットを使用する場合、 c1 に NULL を持つ行は、現在の行の ORDER BY の値が NULL である場合にのみフレームに含まれます。以下のクエリは、 3 行が現在の行である場合、 50 の合計を返します。以下の NULL の行はフレームに含まれません。

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
  FROM nulls;
Copy
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |     30 |
|             2 | 20 |     60 |
|             3 | 30 |     50 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

NULLS LAST が指定され、ウィンドウ・フレームが UNBOUNDED FOLLOWING を使用する場合、 NULL が c1 に含まれる行がフレームに含まれます。以下のクエリは、 3 行が現在の行である場合、 120 の合計を返します。以下の NULL の両行がフレームに含まれます。

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
  FROM nulls;
Copy
+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |    130 |
|             2 | 20 |    130 |
|             3 | 30 |    120 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

heavy_weatherテーブルの作成とロード

heavy_weather テーブル（いくつかのウィンドウ機能の 例 で使用される）を作成し、行を挿入するには、このスクリプトを実行します。

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));

INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;
Copy
言語: 日本語