ウィンドウ関数の使用

ウィンドウ関数は、関連する行(例:日付や場所)のグループであるウィンドウで動作します。このトピックでは、Snowflakeでサポートされている次のようなさまざまな種類のウィンドウ関数の使用方法について説明します。

  • 一般的なウィンドウ機能。

  • ランクを計算するウィンドウ関数(例:最高、2番目)。

  • 累積ウィンドウフレームとスライディングウィンドウフレームをサポートするウィンドウ関数。

このドキュメントは、ウィンドウ機能の知識が少ない読者を対象としています。これらの機能の知識が豊富な読者は、次の参照資料にお探しの情報が掲載されている可能性があります。

このトピックの内容:

ウィンドウの概念

ウィンドウ は行のグループです。ウィンドウには、0、1、または複数の行を含めることができます。ただし、簡素化するために、通常、ウィンドウには「複数の行」が含まれるとだけ言われています。

ウィンドウ内のすべての行は、場所(例:同じ都市のすべて)、または時間(例:同じ会計年度のすべて)など、何らかの方法で関連付けられています。

ウィンドウを使用する関数は、 ウィンドウ関数 です。

ウィンドウ関数として分類された関数は、スカラー関数とは異なる種類の質問に答えるのに役立ちます。

  • スカラー関数を使用するクエリは、単一の行に関する質問に答え、その行のデータのみを使用します。

  • ウィンドウ関数を使用したクエリは、同じウィンドウ内の 他の行 との行の関係に関する質問に答えます。

例えば、チェーン展開されている5つの店舗のうち、1つの支店を管理するとします。店舗の利益を計算するには、特定の店舗に関する収益やコストなどの情報のみを調べる必要があります。そのクエリには、スカラー関数を使用します。

店舗の利益を 他の店舗と比較 するには、店舗だけでなく他の店舗に関する情報も計算する必要があります。そのクエリには、ウィンドウ関数を使用します。

ウィンドウ関数は、2つの引数を取るものと考えることができます。最初の引数は、たとえば、収益や利益などの計算で使用する列または式です。2番目の引数は、ウィンドウ(つまり、比較で使用される行のグループ)を定義します。ウィンドウには、現在の行(店舗)とそれを比較する他の行(同じチェーン内の他の店舗)の両方が含まれます。

店舗チェーン全体の利益に対する店舗の割合を計算するには、店舗の利益をすべての店舗の合計利益で除算(支店の利益/チェーンの利益)します。

このトピックでは、ウィンドウ関数の説明に役立つように、ウィンドウ関数がある場合とない場合の両方で、会社の利益に対する支店の割合を計算する方法を示します。

次の例は、ウィンドウ関数を 使用せずに 、チェーンの利益に対する店舗の割合を計算する方法の1つを示しています。

テーブルの作成、データの読み込み、各店舗の利益の計算から始めます。

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 AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales) AS chain_profit,
       store_profit / chain_profit * 100 AS store_percentage_of_chain_profit
     FROM store_sales
     ORDER BY branch_ID;
+-----------+--------------+--------------+----------------------------------+
| BRANCH_ID | STORE_PROFIT | CHAIN_PROFIT | STORE_PERCENTAGE_OF_CHAIN_PROFIT |
|-----------+--------------+--------------+----------------------------------|
|         1 |     10000.00 |     44000.00 |                      22.72727300 |
|         2 |     15000.00 |     44000.00 |                      34.09090900 |
|         3 |     10000.00 |     44000.00 |                      22.72727300 |
|         4 |      9000.00 |     44000.00 |                      20.45454500 |
+-----------+--------------+--------------+----------------------------------+
Copy

各都市のすべての店舗の割合として各店舗の利益が表示される、より詳細なレポートが必要な場合は以下のクエリを使用します。

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales AS s2 WHERE s2.city = s1.city) AS city_profit,
       store_profit / city_profit * 100 AS store_percentage_of_city_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
|         1 |     10000.00 |    25000.00 |                     40.00000000 |
|         2 |     15000.00 |    25000.00 |                     60.00000000 |
|         3 |     10000.00 |    19000.00 |                     52.63157900 |
|         4 |      9000.00 |    19000.00 |                     47.36842100 |
+-----------+--------------+-------------+---------------------------------+
Copy

ほぼ同じことを実行する関数があると便利です。対象店舗の利益をすべての店舗の利益の合計(または特定の店舗グループ、例えば、同じ都市のすべての店舗の利益の合計)で割ります。このような関数は2つの引数を取ることができます。1つは計算を実行する列で、もう1つは比較する行を指定します。2番目の列は、 WHERE 句のようなものです。この関数は、次のような方法で使用できます(これは擬似コードであり、 有効な SQLではありません)。

SELECT branch_ID,
       PERCENTAGE(net_profit, <where_condition>)
    FROM store_sales;
Copy

この関数は、現在の行(現在の店舗)の利益を<where_condition>に一致したすべての店舗の利益の合計で除算します。

例えば、各都市の各店舗の利益の割合を計算するには、擬似コードは次のようになります。

SELECT branch_ID,
       PERCENTAGE(net_profit, 'city')
     FROM store_sales;
Copy

SQL は上記の構文はサポートしていませんが、現在の行と定義された行グループの両方に基づいて結果を返すウィンドウ関数の概念はサポートしています。

Snowflakeには PERCENTAGE という名前の関数はありませんが、現在の行の値をウィンドウ内のすべての行の値の合計で除算する RATIO_TO_REPORT という名前の関数があります。上記のクエリに相当するものを次に示します。

SELECT branch_ID,
       city,
       100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY city)
    FROM store_sales AS s1
    ORDER BY city, branch_ID;
+-----------+-----------+------------------------------------------------------------+
| BRANCH_ID | CITY      | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER (PARTITION BY CITY) |
|-----------+-----------+------------------------------------------------------------|
|         3 | Montreal  |                                                52.63157900 |
|         4 | Montreal  |                                                47.36842100 |
|         1 | Vancouver |                                                40.00000000 |
|         2 | Vancouver |                                                60.00000000 |
+-----------+-----------+------------------------------------------------------------+
Copy

OVER() 句は、計算で使用される行のグループを定義します。これは、以前の PERCENTAGE 関数の仮想の2番目の引数(<where_condition>)と同じ役割を果たします。

PARTITION BY サブ句を使用すると、そのウィンドウをサブウィンドウに分割できます。この場合、都市ごとに1つです。(ステートメントが明示的に PARTITION BY 句を使用しない場合、ウィンドウ関数は入力全体を単一のウィンドウとして処理します。)

特定の都市内の店舗だけでなく、チェーン全体に対する利益率を表示する場合は、 PARTITION BY 句を省略します。

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

順序依存ウィンドウ関数

前のクエリの結果は、 PARTITION BYサブ句によって選択された行の順序に依存しません。店舗の企業利益の割合は、クエリが他の店舗のデータを読み取る順序に依存しません。

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

  • ランク関連のウィンドウ関数。

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

一部の関数は、ランク関連の関数とウィンドウフレーム関数の両方です。

順序に依存する関数は、 OVER 句の ORDER BY サブ句を使用します。例:

select order_sensitive_function(column_name) over ( [partition by <col2>] order by <col3> )
...
Copy

ORDER BY 句は、 ASC (昇順)または DESC (降順)で使用できます。デフォルトは昇順です。

OVER 句の ORDER BY サブ句は、クエリの最終出力をソートする ORDER BY 句とは別です。)

ウィンドウフレーム関数

ウィンドウフレーム は、ウィンドウ内の行のサブセットです。

ウィンドウフレーム関数は、ウィンドウフレームを使用して、移動平均などを計算します。

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

  • 累積。

  • スライディング。

累積ウィンドウフレーム

累積ウィンドウは、固定ポイントから始まり、新しい行ごとに拡大する可変幅ウィンドウです。例えば、「今月のこれまでの売上」は、月の最初から始まり、新しい日のデータが追加されるたびに増加する累積ウィンドウを使用して計算されます。

+--------+-------+--------------+
| Day of | Sales | Sales So Far |
| Month  | Today | This Month   |
|--------+-------+--------------+
|      1 |    10 |           10 |
|      2 |    10 |           20 |
|      3 |    10 |           30 |
|    ... |   ... |          ... |
+--------+-------+--------------+
Copy

多くの場合、累積ウィンドウは定期的に0から再開します。例えば、上の表に2月の売り上げが表示されている場合、3月1日にウィンドウフレームの開始点は3月1日になり、今月の売り上げは0にリセットされ、3月1日からカウントアップを開始します。

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

車がフェンスと平行に走行する際に、車のサイドウィンドウの外に見えるものを考えることで、スライディングウィンドウフレームを想像してもらえます。フェンスポストの間隔が均等で、ウィンドウの幅がポスト間の距離の整数倍の場合、表示されるフェンスポストの数は一定のままです。ただし、走行すると「古い」フェンスポストがビューから移動し、「新しい」フェンスポストがビューに移動するため、常に同じ数のフェンスポストが表示されていても、時間の経過とともに同じフェンスポストが表示されることはありません。

スライディングウィンドウフレームは、ウィンドウの行に沿って「スライド」する固定幅のフレームで、毎回異なるウィンドウスライスを表示します。フェンスポストを通り過ぎる車と同様に、ウィンドウフレームはデータに沿って移動し、古い行がフレームから消え、新しい行が表示されるため、フレームの幅(フレーム内の行数)は常に同じになります。

スライディングウィンドウは、移動平均の計算によく使用されます。移動平均とは、固定サイズの間隔(例:日数)に基づいて計算される平均です。間隔のサイズは一定ですが、ウィンドウがスライドするにつれて間隔の実際の値は時間の経過とともに(または他の何らかの要因で)変化するため、平均は「移動」しています。

例えば、株式市場のアナリストは、13週間の株価の移動平均の一部に基づいて株式を分析することがよくあります。今日の移動平均価格は、今日の終わりの価格と直近の13週間の各日の終わりの価格の平均です。株式が週5日取引され、過去13週間に休日がなかった場合、移動平均は最新の65取引日(今日を含む)の平均価格です。

次の例は、6月の最終日と7月の最初の数日間の株価の13週間(91日)移動平均がどうなるかを示しています。

  • 6月30日に、関数は4月1日から6月30日(この値を含む)の平均価格を返します。

  • 7月1日に、関数は4月2日から7月1日(この値を含む)の平均価格を返します。

  • 7月2日に、関数は4月3日から7月2日(この値を含む)の平均価格を返します。

  • など。

注釈

スライディングウィンドウは固定幅ですが、ウィンドウが最初に新しいデータソースに適用されたとき、データソースにはウィンドウを満たすのに十分なデータがない場合があります。例えば、記録保持が4月1日に開始された場合、4月1日から6月29日までは、スライドウィンドウに含まれるデータは91日未満です。ウィンドウの幅は、ウィンドウが塗りつぶされた後にのみ一定になります。

以下の例では、月の最初の7日間に小さな(3日間)スライディングウィンドウを使用しています。これは、ウィンドウ内の合計を計算するための最新の3つの値を常に保持しながら、ウィンドウフレームがウィンドウ上をスライドする方法を示しています。この図では、期間の開始時にウィンドウがいっぱいにならない可能性があることを考慮しています。

7日間のウィンドウの3日間のスライディングウィンドウフレーム

また、対応するテーブルでわかるように、最後の列には最近3日間の売上データの合計が含まれています。例えば、4日目の列値は 36 であり、これは2日目、3日目、および4日目(11 + 12 + 13)の売上の合計です。

+--------+-------+---------------+
| Day of | Sales | Most Recent   |
| Month  | Today | 3 Days' Sales |
|--------+-------+---------------+
|      1 |    10 |            10 |
|      2 |    11 |            21 |
|      3 |    12 |            33 |
|      4 |    13 |            36 |
|      5 |    14 |            39 |
|    ... |   ... |           ... |
+--------+-------+---------------+
Copy

順序依存ウィンドウ関数に関する詳細情報

PARTITION BY 句および ORDER BY 句は独立しています。 PARTITION BY 句なしで ORDER BY 句を使用できます。市内の他の店舗に対してだけでなく、チェーン内の他のすべての店舗に対して店舗をランク付けするには、以下のクエリを使用します。

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
Copy

注釈

OVER 句内の ORDER BY サブ句は、ウィンドウ関数が行を処理する順序のみを制御します。この句は、クエリ全体の出力を制御しません。クエリの出力順序を制御するには、クエリの最上位に外側の ORDER BY 句が必要です。これらの ORDER BY 句は独立しており、相互に排他的ではありません。次のクエリは、最初の ORDER BY 句を使用してウィンドウ関数による処理を制御し、2番目の ORDER BY 句を使用してクエリ全体の出力の順序を制御します。

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
  ORDER BY branch_ID;
Copy

一部のウィンドウ関数は順序依存であり、一部はそうではありません。

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

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

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

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

注意

ほとんどの場合、 SQL は明示的な言語です。言語は指示されたことを実行します。それ以上でもそれ以下でもありません。暗黙的な句はほとんどありません。つまり、ある句が暗黙的に、表示されない別の句を作成する場合です。一部のウィンドウ関数では、 ORDER BY 句が暗黙的に追加の句(累積ウィンドウフレーム句)を追加します。これにより、動作が変わります。暗黙的なウィンドウフレーム句の詳細については、 ウィンドウフレームの使用上の注意 に記載されています。

明示的ではなく暗黙的な動作は、理解しにくい結果につながる可能性があるため、Snowflakeは暗黙的なウィンドウフレームを避けることをお勧めします。代わりに、すべてのウィンドウフレームを明示的にしてください。

次の図は、ウィンドウ関数、ウィンドウフレーム関数、およびランク関連関数の関係を示しています。

                      Window Functions

  Order-insensitive Functions      Order-sensitive Functions
+-----------------------------+--------------------------------------------------------------------+
|                             | Rank-related Functions                                             |
|                             +--------------------------------------------------------------------+
|                             | Functions that can be both Rank-related and Window Frame functions |
|                             +--------------------------------------------------------------------+
|                             | Window Frame Functions                                             |
+-----------------------------+--------------------------------------------------------------------+
Copy

上記には次が示されています。

  • 一部のウィンドウ関数は順序に依存しません。

  • 一部のウィンドウ関数は順序に依存します。

  • 順序依存ウィンドウ関数は、次の2つのカテゴリに分類されます。

    • ランク関連関数。

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

  • 一部の順序依存関数は、ランク関連関数とウィンドウフレーム関数の両方です。

ウィンドウ関数と集計関数

多くのウィンドウ関数と 集計関数 は同じ名前を持っています。例えば、 SUM() ウィンドウ関数と SUM() 集約関数があります。

2つの使用法を区別するには、次のことに注意してください。

  • 集約関数の場合、入力は複数の行であり、出力は1行です。

  • ウィンドウ関数の場合、2つの入力、行のウィンドウ、およびそのウィンドウ内の単一の行があり、出力は 入力行ごとに 1行です。

集約関数は、 SUM などの結合されたすべての行について何かを伝えますが、個々の行については何も伝えません。

ウィンドウ関数は、ウィンドウ内の他のすべての行に対する現在の行に関する情報を提供します。

次の SQL ステートメントは、入力行のグループ全体に対して1行を返す SUM()集計関数の使用と、入力行グループ内の各行に1行を返す SUM()ウィンドウ関数の使用の違いを示しています。

SELECT SUM(net_profit) 
    FROM store_sales;
+-----------------+
| SUM(NET_PROFIT) |
|-----------------|
|        44000.00 |
+-----------------+
SELECT branch_ID, SUM(net_profit) OVER ()
    FROM store_sales
    ORDER BY branch_id;
+-----------+-------------------------+
| BRANCH_ID | SUM(NET_PROFIT) OVER () |
|-----------+-------------------------|
|         1 |                44000.00 |
|         2 |                44000.00 |
|         3 |                44000.00 |
|         4 |                44000.00 |
+-----------+-------------------------+
Copy

ウィンドウ関数の呼び出し

このセクションでは、ウィンドウ関数を呼び出す構文の詳細について説明します。

ウィンドウおよびウィンドウフレームは、 OVER 句を使用して指定されます。

... OVER ( [ PARTITION BY <expr1> ]
           [ ORDER BY <expr2> [ { cumulativeFrame | slidingFrame } ] ]
         ) ...
Copy

ウィンドウフレームの構文については、 ウィンドウ関数 で詳しく説明しています。このトピックでは、特にウィンドウ関数に必要な構文のサブセットに焦点を当てています。

  • PARTITION BY サブ句は、データをウィンドウに分割します。たとえば、1か月以上の月間売上の累計を計算する場合、データを月ごとに分割できます。これにより、1月の現在の売上累計を1つ、2月の現在の売上累計をもう1つ計算できるようになります。

  • ORDER BY サブ句は、主にランク関連ウィンドウ関数と、スライドおよび累積ウィンドウフレーム関数用です。各ウィンドウ内の行の順序を決定します。

図解例

この例では、販売のシナリオを使用して、このトピックで前述した概念の多くを説明します。

先週の販売に基づいた値を示す財務レポートを生成する必要があると仮定します。

  • 日次売上

  • 週内のランキング (つまり、売上をその週の上位から下位に向かってランク付け)

    これには、 ランク関連のウィンドウ関数RANK)を使用します。

  • 今週のこれまでの売上 (つまり、週の初めから現在日を含む、すべての日の「累計」)

    これには、 ウィンドウ関数SUM)と 累積ウィンドウフレーム を使用します。

  • 今週の総売上

    これには、単純なウィンドウ関数として SUM を使用します。

  • 3日移動平均 (つまり、現在日と過去2日間の平均)

    これには、 ウィンドウ関数 とした(AVG)と スライドウィンドウフレーム を使用します。

レポートは次のようになります。

+--------+-------+------+--------------+-------------+--------------+
| Day of | Sales | Rank | Sales So Far | Total Sales | 3-Day Moving |
| Week   | Today |      | This Week    | This Week   | Average      |
|--------+-------+------+--------------+-------------|--------------+
|      1 |    10 |    4 |           10 |          84 |         10.0 |
|      2 |    14 |    3 |           24 |          84 |         12.0 |
|      3 |     6 |    5 |           30 |          84 |         10.0 |
|      4 |     6 |    5 |           36 |          84 |          9.0 |
|      5 |    14 |    3 |           50 |          84 |         10.0 |
|      6 |    16 |    2 |           66 |          84 |         11.0 |
|      7 |    18 |    1 |           84 |          84 |         12.0 |
+--------+-------+------+--------------+-------------+--------------+
Copy

このクエリの SQL はやや複雑です。この説明では、単一のクエリとして表示するのではなく、個別の列のために SQL を分割します。

実際のシナリオでは、何年ものデータがあるため、特定の1週間のデータの合計と平均を計算するには、1週間のウィンドウを使用するか、次のようなフィルターを使用する必要があります。

... WHERE date >= start_of_relevant_week and date <= end_of_relevant_week ...
Copy

ただし、この例では、テーブルには最新の1週間分のデータのみが含まれていると想定しています。

CREATE TABLE store_sales_2 (
    day INTEGER,
    sales_today INTEGER
    );
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table STORE_SALES_2 successfully created. |
+-------------------------------------------+
INSERT INTO store_sales_2 (day, sales_today) VALUES
    (1, 10),
    (2, 14),
    (3,  6),
    (4,  6),
    (5, 14),
    (6, 16),
    (7, 18);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       7 |
+-------------------------+
Copy

販売ランクの計算

Rank 列は、 RANK 関数を使用して計算されます。

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+
| DAY | SALES_TODAY | RANK |
|-----+-------------+------|
|   1 |          10 |    5 |
|   2 |          14 |    3 |
|   3 |           6 |    6 |
|   4 |           6 |    6 |
|   5 |          14 |    3 |
|   6 |          16 |    2 |
|   7 |          18 |    1 |
+-----+-------------+------+
Copy

期間には7日ありますが、5の異なるランク(1、2、3、5、6)しかないことに注意してください。同順位が2つ(3位と6位)であったため、ランク4または7の行はありません。

今週のこれまでの売上の計算

Sales So Far This Week 列は、累積ウィンドウフレームを持つウィンドウ関数として SUM を使用して計算されます。

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------------------------+
| DAY | SALES_TODAY | SALES SO FAR THIS WEEK |
|-----+-------------+------------------------|
|   1 |          10 |                     10 |
|   2 |          14 |                     24 |
|   3 |           6 |                     30 |
|   4 |           6 |                     36 |
|   5 |          14 |                     50 |
|   6 |          16 |                     66 |
|   7 |          18 |                     84 |
+-----+-------------+------------------------+
Copy

このクエリは、行を日付順に並べ替えてから、日付ごとに、ウィンドウの開始から現在日(現在日を含む)までの売上の合計を計算します。

今週の総売上の計算

Total Sales This Week 列は、単純なウィンドウ関数として SUM を使用して計算されます。

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER ()
               AS total_sales
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+-------------+
| DAY | SALES_TODAY | TOTAL_SALES |
|-----+-------------+-------------|
|   1 |          10 |          84 |
|   2 |          14 |          84 |
|   3 |           6 |          84 |
|   4 |           6 |          84 |
|   5 |          14 |          84 |
|   6 |          16 |          84 |
|   7 |          18 |          84 |
+-----+-------------+-------------+
Copy

3日移動平均の計算

3-Day Moving Average 列は、スライディングウィンドウフレームを持つウィンドウ関数として AVG を使用して計算されます。

SELECT day, 
       sales_today, 
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+----------------------+
| DAY | SALES_TODAY | 3-DAY MOVING AVERAGE |
|-----+-------------+----------------------|
|   1 |          10 |               10.000 |
|   2 |          14 |               12.000 |
|   3 |           6 |               10.000 |
|   4 |           6 |                8.666 |
|   5 |          14 |                8.666 |
|   6 |          16 |               12.000 |
|   7 |          18 |               16.000 |
+-----+-------------+----------------------+
Copy

このスライディングウィンドウフレームと前述の累積ウィンドウフレームの違いは、単純に出発点です。

  • スライディングウィンドウフレームでは、開始点が右にスライドします。

  • 累積ウィンドウフレームでは、開始点が固定され、ウィンドウ内の追加行ごとにフレームが累積され続けます。

まとめ

クエリの最終バージョンは次のとおりです。

  • ランク。

  • 累積ウィンドウフレーム。

  • 単純なウィンドウ関数。

  • スライディングウィンドウ関数。

    SELECT day, 
           sales_today, 
           RANK()
               OVER (ORDER BY sales_today DESC) AS Rank,
           SUM(sales_today)
               OVER (ORDER BY day
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                   AS "SALES SO FAR THIS WEEK",
           SUM(sales_today)
               OVER ()
                   AS total_sales,
           AVG(sales_today)
               OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
                   AS "3-DAY MOVING AVERAGE"
        FROM store_sales_2
        ORDER BY day;
    +-----+-------------+------+------------------------+-------------+----------------------+
    | DAY | SALES_TODAY | RANK | SALES SO FAR THIS WEEK | TOTAL_SALES | 3-DAY MOVING AVERAGE |
    |-----+-------------+------+------------------------+-------------+----------------------|
    |   1 |          10 |    5 |                     10 |          84 |               10.000 |
    |   2 |          14 |    3 |                     24 |          84 |               12.000 |
    |   3 |           6 |    6 |                     30 |          84 |               10.000 |
    |   4 |           6 |    6 |                     36 |          84 |                8.666 |
    |   5 |          14 |    3 |                     50 |          84 |                8.666 |
    |   6 |          16 |    2 |                     66 |          84 |               12.000 |
    |   7 |          18 |    1 |                     84 |          84 |               16.000 |
    +-----+-------------+------+------------------------+-------------+----------------------+
    
    Copy

追加の例

このセクションでは、ウィンドウ関数でサブ句のウィンドウ処理を使用するためのより詳細な例を示し、これらのサブ句がどのように連携するかを示します。

これらの例では、次のテーブルとデータを使用します。

CREATE TABLE sales (sales_date DATE, quantity INTEGER);

INSERT INTO sales (sales_date, quantity) VALUES
    ('2018-01-01', 1),
    ('2018-01-02', 3),
    ('2018-01-03', 5),
    ('2018-02-01', 2)
    ;
Copy

注釈

これらの例の多くは、 2つの ORDER BY 句を使用します。1つはウィンドウ句のために、もう1つは結果セットを最も読みやすい順序で配置するために用います。このトピックでは、 ORDER BY 句への参照は通常、ウィンドウ内の句への参照です。

PARTITION BY 句のあるウィンドウ

ウィンドウ句の PARTITION BY サブ句は、入力式の値に基づいてデータを個別のサブセットに分割します。 SELECT ステートメントは各サブセットに適用され、出力には各サブセットの行が含まれます。

これは GROUP BY 句の動作と似ていますが、同一ではないことに注意します。

次の例は、毎月の販売数量を示し、 PARTITION BY 句を使用してデータを1か月のサブセットに分割します。

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date)) AS QUANTITY
    FROM sales
    ORDER BY sales_date;
+-----------+----------+
| MONTH_NUM | QUANTITY |
|-----------+----------|
|         1 |        9 |
|         1 |        9 |
|         1 |        9 |
|         2 |        2 |
+-----------+----------+
Copy

ご覧のとおり、最初の3行は重複しています。第1月には3つの入力行があり、ウィンドウ関数は各入力行に対して1つの出力行を生成するため、出力には第1月に対して3つの出力行があります。 SUM 関数は、累積関数またはスライド関数としては使用されません。これはウィンドウ全体に適用され、毎回ウィンドウ全体に同じ値を返すため、この関数は上記のように重複した値を取得します。

DISTINCT キーワードを使用して、重複を減らすことができます。

SELECT DISTINCT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date))
    FROM sales
    ORDER BY MONTH_NUM;
+-----------+-----------------------------------------------------+
| MONTH_NUM | SUM(QUANTITY) OVER (PARTITION BY MONTH(SALES_DATE)) |
|-----------+-----------------------------------------------------|
|         1 |                                                   9 |
|         2 |                                                   2 |
+-----------+-----------------------------------------------------+
Copy

この特定のケースでは、ウィンドウ句ではなく GROUP BY 句を使用できます。例:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity)
    FROM sales
    GROUP BY MONTH_NUM
    ORDER BY MONTH_NUM;
+-----------+---------------+
| MONTH_NUM | SUM(QUANTITY) |
|-----------+---------------|
|         1 |             9 |
|         2 |             2 |
+-----------+---------------+
Copy

ただし、 GROUP BY はウィンドウ処理ほど柔軟ではありません。また、より複雑なクエリでは、ウィンドウ句を GROUP BY に置き換えることが常に可能とは限りません。

ORDER BY 句のあるウィンドウ

ORDER BY 句は、各ウィンドウ(および複数のパーティションがある場合は各パーティション)内のデータの順序を制御します。これは、新しい行が追加されたときに、時間の経過とともに「累計」を表示する場合に便利です。

累計は、ウィンドウの先頭から現在の行(両端を含む)まで、または現在の行からウィンドウの末尾までのいずれかで計算できます。

クエリでは、「スライディング」ウィンドウを使用できます。これは、現在の行(たとえば、現在の行を含む10の最新の行)に対してN個の指定された行を処理する固定幅ウィンドウです。

累積ウィンドウとスライドウィンドウの両方について、以下で説明します。

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

「累積」ウィンドウフレームでは、値はウィンドウの先頭から現在の行まで(または現在の行からウィンドウの最後まで)計算されます。

SELECT MONTH(sales_date) AS MONTH_NUM, 
       quantity, 
       SUM(quantity) OVER (ORDER BY MONTH(sales_date)
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

クエリ結果には、 CUMULATIVE_SUM_QUANTITY 列の計算方法を示す追加のコメントが含まれます。

+-----------+----------+-------------------------+
| MONTH_NUM | QUANTITY | CUMULATIVE_SUM_QUANTITY |
|-----------+----------+-------------------------|
|         1 |        1 |                       1 |  -- sum = 1
|         1 |        3 |                       4 |  -- sum = 1 + 3
|         1 |        5 |                       9 |  -- sum = 1 + 3 + 5
|         2 |        2 |                      11 |  -- sum = 1 + 3 + 5 + 2
+-----------+----------+-------------------------+
Copy

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

金融の世界では、アナリストはしばしば「移動平均」を研究します。

たとえば、X軸が時間であり、Y軸が過去13週間にわたる在庫の平均価格を示すグラフがあるとします(つまり、「13週移動平均」)。株価の13週間移動平均のグラフでは、6月30日に表示される株価は、6月30日の株価ではなく、6月30日までの13週間(つまり、4月1日~6月30日)の 平均 価格です。7月1日の値は、4月2日〜7月1日の平均株価で、7月2日の値は、4月3日〜7月2日の平均株価と続きます。ウィンドウは事実上、毎日最新の日の値を移動平均に追加し、最も古い日の値を削除します。これにより、日々の変動が滑らかになり、傾向を認識しやすくなります。

移動平均は、「スライディングウィンドウ」を使用して計算できます。ウィンドウには行単位の特定の幅があります。上記の株価の例では、13週間は91日であるため、スライドウィンドウは91日になります。測定が1日1回(たとえば、1日の終わりに)行われる場合、ウィンドウの「幅」は91行になります。

91行の幅のウィンドウを定義するには、

SELECT AVG(price) OVER (ORDER BY timestamp1
                        ROWS BETWEEN 90 PRECEDING AND CURRENT ROW)
    FROM sales;
Copy

注釈

ウィンドウの開始時は、幅が91日未満である可能性があります。たとえば、株式の13週移動平均株価が必要だとします。株式が最初、4月1に作成された場合、4月3日には価格情報が3日分しか存在しないため、ウィンドウの幅は3行のみになります。

次の例は、2つのサンプルを保持するのに十分な幅のスライディングウィンドウで合計した結果を示しています。

SELECT MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
           AS SLIDING_SUM_QUANTITY
  FROM sales
  ORDER BY sales_date;
Copy

クエリ結果には、 SLIDING_SUM_QUANTITY 列の計算方法を示す追加のコメントが含まれます。

+-----------+----------+----------------------+
| MONTH_NUM | QUANTITY | SLIDING_SUM_QUANTITY |
|-----------+----------+----------------------+
|         1 |        1 |                   1  |  -- sum = 1
|         1 |        3 |                   4  |  -- sum = 1 + 3
|         1 |        5 |                   8  |  -- sum = 3 + 5 (1 is no longer in the window)
|         2 |        2 |                   7  |  -- sum = 5 + 2 (3 is no longer in the window)
+-----------+----------+----------------------+
Copy

「スライディングウィンドウ」機能には ORDER BY 句が必要です。スライディングウィンドウは、行がスライディングウィンドウに出入りする順序を知る必要があります。

PARTITION BY および ORDER BY 句のあるウィンドウ

PARTITION BY 句と ORDER BY 句を組み合わせて、パーティション内で累計を取得できます。この例では、パーティションは1か月であり、合計はパーティション内でのみ適用されるため、新しい月の初めに合計は 0 にリセットされます。

SELECT MONTH(sales_date) AS MONTH_NUM,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) ORDER BY sales_date)
          AS MONTHLY_CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

クエリ結果には、 MONTHLY_CUMULATIVE_SUM_QUANTITY 列の計算方法を示す追加のコメントが含まれます。

+-----------+---------------------------------+
| MONTH_NUM | MONTHLY_CUMULATIVE_SUM_QUANTITY |
|-----------+---------------------------------+
|         1 |                               1 |  -- sum = 1
|         1 |                               4 |  -- sum = 1 + 3
|         1 |                               9 |  -- sum = 1 + 3 + 5
|         2 |                               2 |  -- sum = 0 + 2 (new month)
+-----------+---------------------------------+
Copy

パーティションとスライディングウィンドウを組み合わせることができます。以下の例では、スライディングウィンドウの幅は通常2行ですが、新しいパーティション(つまり、新しい月)に達するたびに、スライディングウィンドウはそのパーティションの最初の行のみで始まります。

SELECT
       MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) 
                           ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
         AS MONTHLY_SLIDING_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

クエリ結果には、 MONTHLY_SLIDING_SUM_QUANTITY 列の計算方法を示す追加のコメントが含まれます。

+-----------+----------+------------------------------+
| MONTH_NUM | QUANTITY | MONTHLY_SLIDING_SUM_QUANTITY |
|-----------+----------+------------------------------+
|         1 |        1 |                           1  |  -- sum = 1
|         1 |        3 |                           4  |  -- sum = 1 + 3
|         1 |        5 |                           8  |  -- sum = 3 + 5
|         2 |        2 |                           2  |  -- sum = 0 + 2 (new month)
+-----------+----------+------------------------------+
Copy