ウィンドウ関数の使用¶

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

順序依存関数の最も単純なサブカテゴリは、ランク関連の関数サブカテゴリです。

最も単純なランク関連の関数は RANK 関数です。ランク関数は、単にランクをリストします。これは、行の順序付けられたウィンドウ内の行の順序です。ウィンドウの最初の行のランクは1、ウィンドウの2番目の行のランクは2などです。以下の例は、販売量に基づいた営業担当者のランクの順序を示しています。

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

Copy

RANK 関数は行をソートしません。 RANK が呼び出される前に、ウィンドウ内で行がすでに順序どおりになっている必要があります。行を整理するには、 OVER 句の ORDER BY サブ句を使用します。

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

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

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

• 関数に渡されたウィンドウ内の他の行の値。

• ウィンドウ内のすべての行の順序。

Snowflakeは、いくつかの異なるランク関連機能を提供します。これらの関数名、およびランク関連のウィンドウ関数の構文の詳細については、 ウィンドウ関数 に掲載されています。

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

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

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

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

• 累積。

• スライディング。

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

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

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

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

例えば、株式市場のアナリストは、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つの値を常に保持しながら、ウィンドウフレームがウィンドウ上をスライドする方法を示しています。この図では、期間の開始時にウィンドウがいっぱいにならない可能性があることを考慮しています。

また、対応するテーブルでわかるように、最後の列には最近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

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

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

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

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

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

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

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

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

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

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

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

• 順序依存ウィンドウ関数は、次の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

販売ランクの計算¶

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 句のあるウィンドウ¶

ウィンドウ句の 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個の指定された行を処理する固定幅ウィンドウです。

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

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