ウィンドウ関数を使ったデータ分析

このトピックの内容：

このトピックでは、ウィンドウ関数に関する入門的な概念情報を説明します。ウィンドウ関数の使い方をすでにご存知の方は、以下のリファレンス情報で十分でしょう:

概要

ウィンドウ関数は、 パーティション として知られる関連行のグループ上で動作する分析的な SQL 関数です。パーティションは通常、商品カテゴリー、場所、期間、事業単位など、馴染みのある次元に沿った行の論理的なグループです。関数の結果は、暗黙的または明示的な ウィンドウフレーム に関して、各パーティションにわたって計算されます。ウィンドウフレームとは、固定または可変の、 現在の行 からの相対的な行のセットのことです。現在の行は、関数結果が現在計算されている1つの入力行です。関数の結果は、各パーティション内の行ごとに計算され、ウィンドウフレーム内の各行が現在の行として順番に計算されます。

この動作を定義する構文は、関数の OVER 句です。多くの場合、 OVER 句は、ウィンドウ関数を同じ名前の通常の SQL 関数（AVG や SUM など）と区別します。OVER 句は主に3つの要素から構成されます:

  • PARTITION BY 句

  • ORDER BY 句

  • ウィンドウフレームの仕様

対象の関数やクエリによっては、これらの要素はすべてオプションになります。空の OVER 句を持つウィンドウ関数は有効です: OVER()。しかし、ほとんどの分析クエリでは、ウィンドウ関数は1つ以上の明示的な OVER 句コンポーネントを必要とします。ウィンドウ関数は、他の SQL 関数をサポートしている任意のコンテキストで呼び出すことができます。以下のセクションでは、ウィンドウ関数の概念をより詳しく説明し、いくつかの入門的な例を示します。完全な構文については、 ウィンドウ関数の構文と使用法 を参照のこと。

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

ウィンドウ関数について学び始める良い方法は、通常の集計関数とウィンドウ関数の対応するものを比較することです。SUM 、 COUNT 、 AVG のような、いくつかの標準的な 集約関数 には、同じ名前の対応するウィンドウ関数があります。この2つを区別するために、次のことに注意してください:

  • 集計関数の場合、入力は行のグループであり、出力は1行です。

  • ウィンドウ関数の場合、入力はパーティション内の各行であり、出力は 入力行ごと に1行です。

例えば、 SUM 集計関数は、すべての入力行に対して単一の合計値を返しますが、ウィンドウ関数は、パーティション内の他のすべての行に対する各行（現在の行）の合計値を返します。

この仕組みを見るために、まず menu_itemsテーブルを作成し、ロードします。ここには、フードトラックのメニュー項目の売上原価と価格が格納されています。通常の AVG 関数を使って、異なるカテゴリーのメニュー項目の平均商品原価を求めます:

SELECT menu_category,
    AVG(menu_cogs_usd) avg_cogs
  FROM menu_items
  GROUP BY 1
  ORDER BY menu_category;
Copy
+---------------+------------+
| MENU_CATEGORY |   AVG_COGS |
|---------------+------------|
| Beverage      | 0.60000000 |
| Dessert       | 1.79166667 |
| Main          | 6.11046512 |
| Snack         | 3.10000000 |
+---------------+------------+

この関数は、 avg_cogs に対して1つのグループ化された結果を返すことに注意してください。

あるいは、 OVER 句を指定し、 AVG をウィンドウ関数として使うこともできます。（結果は60行のテーブルから15行に制限されます。）

SELECT menu_category,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category) avg_cogs
  FROM menu_items
  ORDER BY menu_category
  LIMIT 15;
Copy
+---------------+----------+
| MENU_CATEGORY | AVG_COGS |
|---------------+----------|
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
+---------------+----------+

この関数は、各パーティション内の各行の平均を返し、パーティショニング列の値が変わると計算がリセットされることに注意してください。ウィンドウ関数の値をより明確にするために、関数定義に ORDER BY 句とウィンドウフレームを追加します。また、平均値だけでなく、生の menu_cogs_usd も返すことで、特定の計算がどのように機能するかを確認できるようにします。このクエリは「移動平均」の単純な例であり、明示的なウィンドウフレームに依存するローリング計算です。このような例については、 時系列データの分析 をご参照ください。

SELECT menu_category, menu_price_usd, menu_cogs_usd,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category ORDER BY menu_price_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
  FROM menu_items
  ORDER BY menu_category, menu_price_usd
  LIMIT 15;
Copy
+---------------+----------------+---------------+----------+
| MENU_CATEGORY | MENU_PRICE_USD | MENU_COGS_USD | AVG_COGS |
|---------------+----------------+---------------+----------|
| Beverage      |           2.00 |          0.50 |  0.58333 |
| Beverage      |           3.00 |          0.50 |  0.57500 |
| Beverage      |           3.00 |          0.75 |  0.63333 |
| Beverage      |           3.50 |          0.65 |  0.65000 |
| Dessert       |           3.00 |          0.50 |  0.91666 |
| Dessert       |           4.00 |          1.00 |  1.58333 |
| Dessert       |           5.00 |          1.25 |  2.08333 |
| Dessert       |           6.00 |          2.50 |  2.66666 |
| Dessert       |           6.00 |          2.50 |  2.75000 |
| Dessert       |           7.00 |          3.00 |  3.00000 |
| Main          |           5.00 |          1.50 |  2.03333 |
| Main          |           6.00 |          2.60 |  3.00000 |
| Main          |           6.00 |          2.00 |  2.33333 |
| Main          |           6.00 |          2.40 |  3.13333 |
| Main          |           8.00 |          4.00 |  3.66666 |
+---------------+----------------+---------------+----------+

ウィンドウフレームは、現在の行とそれに続く（パーティション内の）2つの行のみが考慮されるように平均計算を調整します。パーティションの最後の行には次の行がないため、例えば最後の Beverage の行の平均は、対応する menu_cogs_usd の値（0.65）と同じになります。ウィンドウ関数の出力は、関数に渡される個々の行と、ウィンドウフレームの対象となる他の行の値に依存します。

ウィンドウ関数の行の順序

先の AVG ウィンドウ関数の例では、関数定義内で ORDER BY 句を使用し、ウィンドウフレームが（この場合は menu_price_usd によって）ソートされたデータの対象となるようにしています。

2種類のウィンドウ関数が ORDER BY 句を必要とする:

  • ウィンドウフレームを明示したウィンドウ関数で、実行合計や移動平均の計算など、各パーティション内の行のサブセットに対してローリング演算を行います。ORDER BY 句がなければ、ウィンドウフレームは意味をなしません。「先行」行と「後続」行のセットは決定論的でなければなりません。

  • CUME_DIST 、 RANK 、 DENSE_RANK などのランキングウィンドウ関数は、行の「ランク」に基づいた情報を返します。例えば、1か月あたりの利益の降順に店舗をランク付けすると、最も利益の高い店舗が1にランク付けされます。2番目に収益性の高い店舗は2などにランク付けされます。

ウィンドウ関数の ORDER BY 句は、クエリの最終結果をソートするメインの ORDER BY 句と同じ構文をサポートしています。この2つの ORDER BY 句は別個のものです。OVER 句内の ORDER BY 句は、ウィンドウ関数が行を処理する順序のみを制御します。この句は、クエリ全体の出力を制御しません。多くの場合、ウィンドウ関数のクエリには両方のタイプの ORDER BY 句が含まれます。

OVER 句内の PARTITION BY 句と ORDER BY 句も独立しています。PARTITION BY 句なしで ORDER BY 句を使用でき、その逆も可能です。

クエリを書く前に、個々のウィンドウ関数の構文を確認してください。ORDER BY 句の構文要件は関数によって異なります:

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

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

  • ウィンドウ関数の中には、 ORDER BY 句が使えないものがあります。

  • ウィンドウ関数の中には、 ORDER BY 句を暗黙のウィンドウフレームとして解釈するものがあります。

注意

一般的に言って、 SQL は明示的な言語で、暗示的な句はほとんどありません。しかし、いくつかのウィンドウ関数では、 ORDER BY 句はウィンドウフレームを意味します。詳細については、 ウィンドウフレームの使用上の注意 をご参照ください。

明示的ではなく暗黙的な動作は、理解しにくい結果につながる可能性があるため、Snowflakeはウィンドウフレームを明示的に宣言することをお勧めします。

さまざまなタイプのウィンドウフレームを使う

ウィンドウフレームは明示的または暗黙的に定義されます。これらは、 OVER 句の中に ORDER BY 句があるかどうかで決まります:

  • 明示的なフレーム構文については、 構文 の windowFrameClause を参照のこと。パーティションの始まりから現在の行まで、現在の行からパーティションの終わりまで、あるいは完全に「境界のない」終わりから終わりまでなど、無制限の境界を定義できます。あるいは、パーティション内の現在の行からの相対的なオフセット（すべてを含む）を明示的に使用することもできます。

  • OVER 句に windowFrameClause が含まれていない場合、暗黙的なフレームがデフォルトで使用されます。デフォルトのフレームは、問題の関数によって異なります。 ウィンドウフレームの使用上の注意 もご参照ください。

範囲ベースと行ベースのウィンドウフレーム

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

行ベース:

現在の行からの 物理的 オフセットに基づいて、正確な行のシーケンスがフレームに属します。例えば、 5 PRECEDING は、現在の行の前の5行を意味します。オフセットは数値でなければなりません。ROWS モードは包括的であり、常に現在の行に対して相対的です。指定された前後の行の数がパーティションの限界を超えている場合、Snowflake はその値を NULL として扱います。

フレームの境界線が明示的に番号が振られているのではなく、オープンエンドになっている場合も、同様の物理的オフセットが適用されます。例えば、 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は、フレームが、現在の行の物理的に前の行のセット全体（0個以上）と、現在の行自体で構成されていることを意味します。

範囲ベース:

現在の行の ORDER BY 値からのオフセットが与えられると、行の 論理的 範囲がフレームに属します。例えば、 5 PRECEDING は、 ORDER BY の値を持つ行のうち、現在の行の ORDER BY の値に最大5（DESC の場合はプラス、 ASC の場合はマイナス）を足したまたは引いた値を持つ行を意味します。オフセット値には、数値または間隔を指定することができます。

フレームの境界が番号ではなくオープンエンドの場合、同様の論理オフセットが適用されます。例えば、 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW は、現在の行に物理的に先行するすべての行、現在の行そのもの、 および 現在の行と同じ ORDER BY 値を持つ隣接する行でフレームが構成されることを意味します。RANGE ウィンドウフレームの場合、 CURRENT ROW は物理的に現在の行を意味するのではなく、現在の物理的な行と同じ ORDER BY の値を持つすべての行を意味します。

ウィンドウ関数のクエリは、 ORDER BY 式、テーブルのデータ、およびフレームの正確な定義によって、非常に異なる結果を返す可能性があるため、 ROWS BETWEEN と RANGE BETWEEN ウィンドウフレームの区別は重要です。以下の例は、動作の違いを示しています。

オフセットを明示した RANGE BETWEEN と ROWS BETWEEN の比較

範囲ベースのウィンドウフレームには、 ORDER BY 列または式と、 RANGE BETWEEN 指定が必要です。ウィンドウフレームの論理的な境界は、現在の行の ORDER BY 値（数値定数または区間リテラル）に依存します。

たとえば、 heavy_weather という時系列テーブルを次のように定義します:

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

このテーブルのサンプル行は以下のようになります:

+-------------------------+--------+-------+-------------+
| START_TIME              | PRECIP | CITY  | COUNTY      |
|-------------------------+--------+-------+-------------|
| 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 |
+-------------------------+--------+-------+-------------+

precip （降水量）列の3時間移動平均（AVG）を、 start_time で並べられたウィンドウフレームを用いて計算するクエリがあるとします:

AVG(precip)
  OVER(ORDER BY start_time
    RANGE BETWEEN CURRENT ROW AND INTERVAL '3 hours' FOLLOWING)
Copy

上記のサンプル行を考えると、現在の行が 2021-12-30 11:23:00.000 （最初のサンプル行）である場合、次の2行のみがフレーム内に入ります（2021-12-30 11:43:00.000 と 2021-12-30 13:53:00.000）。それ以降のタイムスタンプは3時間以上後です。

しかし、ウィンドウフレームを1日間隔に変更すると、現在の行に続くサンプル行はすべて同じ日付のタイムスタンプを持つため、フレーム内に収まります（2021-12-30）:

RANGE BETWEEN CURRENT ROW AND INTERVAL '1 day' FOLLOWING
Copy

この構文を RANGE BETWEEN から ROWS BETWEEN に変更した場合、フレームは、 ORDER BY 式が返す値に関係なく、1行、3行、10行など、現在の行に次の正確な順序の行数を加えた、正確な行数を表す固定境界を指定しなければならなくなります。

明示的な数値オフセットの RANGE BETWEEN 例 もご参照ください。

RANGE BETWEEN と ROWS BETWEEN をオープンエンドのバウンダリーで比較

以下の例では、同じ行のセットに対して以下のウィンドウフレームを計算した結果を比較しています:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Copy

この例では、 menu_items という小さなテーブルから選択します。 menu_itemsテーブルを作成してロードする をご参照ください。

SUM ウィンドウ関数は、 menu_category パーティションごとに menu_price_usd の値を集計します。ROWS BETWEEN 構文を使えば、各パーティション内で実行合計がどのように累計されるかを簡単に見ることができます。

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;
Copy
+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      5.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     18.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |      6.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

RANGE BETWEEN 構文を、それ以外は同じクエリで使用する場合、その計算方法は最初はそれほど明白ではありません。 現在の行 の解釈が異なるため、現在の行そのものと、その行と同じ ORDER BY 値を持つ隣接する行に依存します。

例えば、結果の2行目と3行目の sum_price の値は、 ORDER BY の値が同じであるため、両方とも 8.00 となります。この動作は、結果セットの他の2か所でも発生し、 sum_price が 24.00 と 12.00 として連続して計算されています。

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;
Copy
+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

累積およびスライド計算用のウィンドウフレーム

ウィンドウフレームは、累積計算や移動計算など、さまざまなタイプの分析クエリを実行するための非常に柔軟なメカニズムです。例えば、累積和を返すには、固定点から始まり、パーティション全体を通して行ごとに移動するウィンドウフレームを指定することができます:

OVER(PARTITION BY col1 ORDER BY col2 ROWS UNBOUNDED PRECEDING)
Copy

このタイプのフレームの別の例としては、次のようなものがあります:

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Copy

これらのフレームの対象となる行の数は可変ですが、フレームの始点と終点は固定であり、数値やインターバルの境界ではなく、名前付きの境界を使用します。

ウィンドウ関数の計算を特定の行数（または範囲）にわたって前方にスライドさせたい場合は、明示的なオフセットを使用することができます:

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
Copy

この場合、結果は最大7行（3＋現在の行＋3）からなるスライディングフレームとなります。このタイプのフレームの別の例としては、次のようなものがあります:

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)
Copy

ウィンドウフレームには、名前付き境界線と明示的なオフセットを混在させることができます。

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

スライディングウィンドウフレームは、パーティション内の行を「スライドして通過する」固定幅のフレームで、毎回パーティションの異なるスライスをカバーします。フレーム内の行数は変わりませんが、パーティションの最初や最後では行数が少なくなることがあります。

スライディングウィンドウは、固定サイズの間隔（日数など）に基づく移動平均の計算によく使われます。間隔のサイズは一定ですが、間隔の実際の値は時間の経過とともに（または他の何らかのディメンションで）変化するため、平均は「移動」しています。

例えば、株式市場のアナリストは、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日（この値を含む）の平均価格を返します。

次の例では、月の最初の7日間に小さな（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 |
|    ... |   ... |           ... |
+--------+-------+---------------+

ランキングウィンドウ関数

ランキングウィンドウ関数の構文は、他のウィンドウ関数の構文と本質的に同じです。例外は次のとおりです。

  • ランキングウィンドウ関数には、 OVER 句の中に ORDER BY 句が必要です。

  • RANK 自体などの一部のランキング関数では、入力引数は不要です。RANK 関数の場合、返される値は、 OVER 句内の ORDER BY 句で決定された数値ランキングのみに基づいています。したがって、列名または式を関数に渡す必要はありません。

最も単純なランキング関数は RANK です。この関数は次のように使用できます:

  • 収益（売上）の最高値から最低値まで、営業担当者をランク付けします。

  • 1人当たりの GDP （1人当たりの収入）に基づいて、国を最高から最低までランク付けします。

  • 大気汚染に基づいて国を最低から最高までランク付けします。

この関数は、単純に、順序付けられた行の集合における行の順位を数値で特定します。1列目はランク1、2列目はランク2、といった具合です。次の例は、 Amount Sold に基づく営業担当者の順位です:

+-------------+-------------+------+
| Salesperson | Amount Sold | Rank |
|-------------+-------------+------|
| Smith       |        2000 |    1 |
| Jones       |        1500 |    2 |
| Torkelson   |        1200 |    3 |
| Dolenz      |        1100 |    4 |
+-------------+-------------+------+

順位をつける前に、行はすでにソートされていなければなりません。したがって、 OVER 句の中で ORDER BY 句を使用しなければなりません。

次の例を考えてみましょう。店舗チェーンの支店間で店舗の利益がランク付けされている場所、つまり、店舗が1番目、2番目、3番目などのランクを知りたいとします。この例では、各店舗をその都市内での収益性でランク付けしています。行は降順（最高の利益が最初）に配置されるため、最も利益のある店舗のランクは1です。

SELECT city, branch_ID, net_profit,
       RANK() OVER (PARTITION BY city ORDER BY net_profit DESC) AS rank
    FROM store_sales
    ORDER BY city, rank;
+-----------+-----------+------------+------+
| CITY      | BRANCH_ID | NET_PROFIT | RANK |
|-----------+-----------+------------+------|
| Montreal  |         3 |   10000.00 |    1 |
| Montreal  |         4 |    9000.00 |    2 |
| Vancouver |         2 |   15000.00 |    1 |
| Vancouver |         1 |   10000.00 |    2 |
+-----------+-----------+------------+------+
Copy

注釈

net_profit 列は、 RANK 関数の引数として渡す必要は ありません。代わりに、入力行は net_profit でソートされます。RANK 関数は、パーティション内の行（1、2、3など）の位置を返すだけで済みます。

ランキング関数の出力は次の要素に依存します。

  • 関数に渡される個々の行。

  • パーティション内の他の行の値。

  • パーティション内のすべての行の順序。

Snowflakeはいくつかの異なるランキング関数を提供します。これらの関数のリストと構文の詳細については、 ウィンドウ関数 を参照してください。

市内の他の店舗に対してだけでなく、チェーン内の他のすべての店舗に対して店舗をランク付けするには、以下のクエリを使用します。

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

次のクエリは、最初の 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

図解例

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

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

  • 日次売上

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

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

  • 今週の総売上

  • 3日移動平均（つまり、当日と過去2日間の平均）

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

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

このクエリの 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

追加の例

このセクションでは、ウィンドウ関数の例をさらに示し、 PARTITION BY 句と ORDER BY 句がどのように連動するかを説明します。

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

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

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

値の合計に対する値の比率を計算する

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

PARTITION BY 句は、 city 列のパーティションを定義します。特定の都市内の店舗だけでなく、チェーン全体に対する利益率を表示する場合は、 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
言語: 日本語