ウィンドウ関数

PostgreSQLのドキュメントは、ウィンドウ関数を以下のように定義しています：

ウィンドウ関数は、現在の行と何らかの関連性を持つテーブルの行セットに対して計算を実行します。これは、集約関数で行える種類の計算と似ています。しかし、通常の集約関数とは異なり、ウィンドウ関数を使用しても行が単一の出力行にグループ化されることはありません - 行はそれぞれのアイデンティティを保持します。裏側で、ウィンドウ関数はクエリ結果の現在の行だけでなく、それ以上のものにアクセスできます。

このドキュメントでは、Contourの式で使用するかもしれないいくつかのウィンドウ関数の構文を説明しています。ウィンドウ関数についてのより詳しい背景情報を知りたい場合は、以下の追加のリソースを参照してください：

• ウィンドウ関数（PostgreSQLのドキュメント）
• SQL ウィンドウ関数（Mode Analyticsのチュートリアル）
• SQL ウィンドウ関数入門（Apache Drill）
• 分析関数（Oracleのドキュメント）

基本的な構文

最も基本的な形で、ウィンドウ関数は次のように分解できます：

<関数> OVER <ウィンドウ>

ここで、関数はサポートされている集約関数の一つであり、ウィンドウはテーブル内の行のサブセットです。

ウィンドウを省略するには、()を使用します - これにより、関数はテーブル内のすべての行に適用されます。

次の例では、date行での最大値を含むエントリをすべての行に追加します。

-- 最大の日付を取得
MAX("date") OVER ()

PARTITION BY

窓の定義の前にオプショナルなPARTITION BY句を追加することもできます。PARTITION BYは、指定された行の値に基づいて窓内の行をグループ化します。集約関数は、それぞれのパーティションに個別に適用されます。

例えば、人物のレコードがあるテーブルでは、次の表現は男性と女性の総数を計算し、その行の性別の値に対して各行にカウントを追加します：

Copied!
1
COUNT("person_id") OVER (PARTITION BY "gender")

上記のコードは、SQLウィンドウ関数を利用しています。COUNT("person_id")は、person_idの数をカウントします。そして、OVER (PARTITION BY "gender")は、データをgenderの各値ごとに区切り（パーティション）、各パーティション内でカウントを行います。結果として、genderごとのperson_idの数を得ることができます。

Copied!
1
COUNT("person_id") OVER (PARTITION BY "gender")

上記のコードは、SQLのウィンドウ関数を使用しています。COUNT("person_id")で、person_idの数を数えます。その後、OVER (PARTITION BY "gender")により、データを"gender"の値ごとに分割（パーティション化）し、それぞれのパーティションで数を数えます。その結果、"gender"ごとのperson_idの数を得ることができます。

ORDER BY

ウィンドウを定義する式では、ウィンドウの範囲を指定し、テーブル内の行をどのように並べ替えるかを指定する必要があります。このサブ式は次のように単純化されます。

<テーブルの並べ替え方法> ROWS BETWEEN <開始位置> AND <終了位置>

ここで、「テーブルの並べ替え方法」とは、(1) 並べ替える行、(2) 昇順か降順かを指定するものです。

ウィンドウの範囲（「開始位置」と「終了位置」）を指定する方法は以下の通りです。

• UNBOUNDED PRECEDING: テーブルの先頭から現在の行まで。
• n PRECEDING (例: 2 PRECEDING): 現在の行の n 行前から現在の行まで。
• CURRENT ROW
• n FOLLOWING (例: 5 FOLLOWING): 現在の行から現在の行の n 行後まで。
• UNBOUNDED FOLLOWING: 現在の行からテーブルの終わりまで。

以下に各可能性がラベル付けされた例のテーブルを示します。

FIRST_NAME |
------------
Adam       |<-- UNBOUNDED PRECEDING (制約なしの前行)
...        |
Alison     |
Amanda     |
Jack       |
Jasmine    |
Jonathan   | <-- 1 PRECEDING (1つ前の行)
Leonard    | <-- CURRENT ROW (現在の行)
Mary       | <-- 1 FOLLOWING (1つ後の行)
Tracey     |
...        |
Zoe        | <-- UNBOUNDED FOLLOWING (制約なしの後行)

(ソース: blog.jooq.org)

したがって、売上記録のあるテーブルが与えられた場合、以下を使用して、過去 5 回の売上の平均値を計算できます:

AVG("sale_value") OVER (ORDER BY "date_of_sale" ASC 
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)

このコードは窓関数を使用しています。具体的には、売上値（"sale_value"）の移動平均を計算しています。各行について、その行を含む前の4行（合計5行）の売上値の平均を計算します。これらの行は、販売日（"date_of_sale"）の昇順に並べられます。

Copied!
1
このコードはウィンドウ関数を使用しています。具体的には、売り上げ値（"sale_value"）の移動平均を計算します。各行について、その行を含む前の4行（合計5行）の売り上げ値の平均を計算します。これらの行は、「売り上げ日」（"date_of_sale"）によって昇順に並べられます。

「最後の5つの販売」がウィンドウです。OVERの後のサブ式は、テーブルを日付でソートし、各行について、前の4行と現在の行を通じて平均を計算します。

すべてをまとめて

以下の複雑な例は、上記のすべての構文を組み合わせています。 この式は、現在の販売までの製品カテゴリー別の販売数の累積を示しています。

COUNT("sale_id") OVER (PARTITION BY "product_category" 
    ORDER BY "date_of_sale" ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

このSQLコードは、売上データを製品カテゴリーごとにパーティショニング（分割）し、売上日の昇順に並べ替えます。そして、過去の全ての行から現在の行までの間で、それぞれの製品カテゴリーの「sale_id」の数をカウントします。

詳細に説明すると:

• COUNT("sale_id"): "sale_id"の数をカウントします。
• OVER (PARTITION BY "product_category"): "product_category"ごとにデータを分割します。つまり、各製品カテゴリーで別々にカウントします。
• ORDER BY "date_of_sale" ASC: 各製品カテゴリー内でのデータを"date_of_sale"の昇順に並べ替えます。昇順（ASC）は最も古い日付から最も新しい日付へという順序です。
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 現在の行と、その行より前の全ての行（無限大に遡る）を範囲としてカウントします。

この結果、各製品カテゴリーの日付順の累積売上数を取得できます。 エントリがパーティションごとにソートされていることに注意してください。つまり、テーブルが最初にパーティション化され、その後、各パーティション内で行がソートされます。

その他の例

購入したアイテムを記録するテーブルがあるとします。次のウィンドウ関数を使用して、購入の累計を示す新しい行を導出できます：

SUM("item_cost") OVER (ORDER BY "purchase_date" ASC 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
-- 上記のコードは、購入日(purchase_date)で昇順に並べたデータに対して、
-- 最初の行から現在の行までの範囲でアイテムコスト(item_cost)の合計を計算します。

カテゴリーごとに累計を計算するには、パーティションを追加できます：

SUM("item_cost") OVER (PARTITION BY "category" 
    ORDER BY "purchase_date" ASC 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

上記のコードは、特定のカテゴリにおける商品コストの累積合計を計算します。購入日を昇順に並べ替え、現在の行から見て前方の範囲（始点未定から現在の行まで）にある商品コストの合計を求めます。

詳細：

• SUM("item_cost")：「item_cost」の合計を求めます。
• PARTITION BY "category"：「category」ごとにデータをパーティション（分割）します。つまり、各カテゴリのデータを個別に処理します。
• ORDER BY "purchase_date" ASC：各パーティションを「purchase_date」の昇順に並べ替えます。
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：各行に対して、その行から見て前方にある全ての行（始点未定から現在の行まで）を対象範囲とします。 これにより、「カテゴリー」行で行を分割し、各カテゴリー内の行を購入日でソートし、そのカテゴリー内のすべてのアイテムのコストの累計を計算します。

隕石データを用いた例

以下の例は、隕石着陸データセットを使用して自分で試すことができます。このデータセットは、NASA Data Portal経由で The Meteoritical Society から提供されています。

この式は、各クラスで最大の隕石を計算します:

MAX("mass") OVER (PARTITION BY "class" ) -- "class"によってパーティション分けされた各グループでの"mass"の最大値を計算します

上記のウィンドウ関数で新しい行 max_size_by_class を導出すると：

… その結果得られるテーブルは以下のようになります：

隕石のクラス別の質量の累積和（逐次合計）を計算するには：

Copied!
1
2
3
4
SUM("mass") OVER (PARTITION BY "class"   -- "class"でグループ化します
    ORDER BY "year" ASC                  -- "year"の昇順に並べます
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 現在の行とその前のすべての行を対象にします
)

これは、隕石のクラスでテーブルをパーティション分けし、各パーティションを日付順に並べ替え、そして各行で、「mass」行の現在の行とすべての前の行の合計を計算し、その合計を現在の行の新しい行として追加します。

隕石のクラスごとのmassの合計（ランニングではない）を計算するには：

Copied!
1
SUM("mass") OVER (PARTITION BY "class")  -- "class"でグループ化した上で、"mass"の合計値を計算します

この集計自体はそれほど有用ではないかもしれませんが、これを拡張することで、より興味深い統計値を計算できます。つまり、この隕石がクラスの総質量に対してどの程度の割合を占めているのかを計算できます。

Copied!
1
"mass" / (SUM("mass") OVER (PARTITION BY "class")) * 100 -- 質量を、クラスごとの質量の合計で割り、100を掛ける

それぞれのクラスで見つけた隕石の総数（数）を計算するには：

Copied!
1
2
-- "class"ごとに"id"の数を数える
COUNT("id") OVER (PARTITION BY "class")

非決定性