Window関数の基本
最近久しぶりにWindow関数を詳しく見てみたので、何回かに分けて解説しようと思います。以降、PostgreSQLを例にして解説しますが、PostgreSQLのWindow関数はSQL標準に対応している部分が多いので、他のDBMSでも同じよう使える部分が多いと思います。
Window関数とは？
Wikipediaより、
SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。
一見GROUP BY句に似ていますが、Window関数はあくまでも関数なので返却される行数には影響しません。
- テーブルの状態
=# SELECT * FROM w;
color | id | value
--------+----+-------
red | 1 | 120
blue | 2 | 100
red | 2 | 250
yellow | 1 | 160
yellow | 2 | 210
red | 3 | 120
(6 rows)
- GROUP BYで集計
結果行数は3行になっています。
=# SELECT color, sum(value) FROM w GROUP BY color;
color | sum
--------+-----
yellow | 370
blue | 100
red | 490
(3 rows)
- Window関数で集計
結果行数は、変わらず6行です。sum()関数の結果は変わっていないことに注目です。
=# SELECT color, sum(value) over (partition by color) FROM w;
color | sum
--------+-----
blue | 100
red | 490
red | 490
red | 490
yellow | 370
yellow | 370
(6 rows)
ちなみに実行計画は以下のようになります。Seq Scanでテーブルをスキャンし、color列でソート、そして、その結果に対してWindowAggでWindow関数を実行します。なぜSortが必要かは、この後を読んでいけばきっと理解できます。
QUERY PLAN
-------------------------------------------------------------
WindowAgg (cost=1.14..1.24 rows=6 width=13)
-> Sort (cost=1.14..1.15 rows=6 width=9)
Sort Key: color
-> Seq Scan on w (cost=0.00..1.06 rows=6 width=9)
(4 rows)
Window関数の考え方
Window関数ではWindow Frame（以下、フレームと呼びます）と呼ばれる区間を定義し、そのフレーム内の行に対して関数が実行されます。フレームは、以下の3つを指定することで決定されます。
- 分割する区間（パーティション）
PARTITION BY句で指定
- フレーム境界
RANGE句、ROW句、GROUP句でモードを指定して、- frame_start and frame_end で具体的な境界を指定する
以下、順番に説明します。
パーティション分割をする（PARTITION BY句）
PARTITION BY句でテーブルを論理的に分割します。例えば、Window関数でOVER (PARTITION BY color)を指定すると以下のように分割されます。
※見やすくするために各行の間に追加で境界線を入れています
color | sum
--------+----- -
blue | 100 | (1) 'blue'のパーティション
--------+----- - -
red | 490 |
--------+----- |
red | 490 | (2) 'red'のパーティション
--------+----- |
red | 490 |
--------+----- - -
yellow | 370 |
--------+----- | (3) 'yellow'のパーティション
yellow | 370 |
--------+----- -
(6 rows)
この時、上記の結果がcolor列でソートされていることに注意してください。PARTITION BY句で指定すると、指定した列でソートが行われます。このソートをすることによって、パーティションの境界が明確になります。ちなみに、PARTITION BY句を指定しないでWindow関数を実行すると、テーブルの先頭から末尾までが一つのパーティションとなります。
フレーム境界を指定する
そして、PARTITION BY句の次に具体的なフレームを指定します。
SELECT *, sum(value) OVER (
PARTITION BY color
ORDER BY id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) ...
と指定すると、フレームは以下のように指定されます。
- ROWSモードで、
- フレームの始まりはパーティションの先頭(UNBOUND PRECEIDING)で、
- フレームの終わりは現在の行(CURRENT ROW)
RANGEモードとROWSモードの違いは、同一行をフレームの境界にするかどうかです。ROWSモードでは境界にして、RANGEモードでは境界にしません。 ‘red’のパーティションに注目してフレームの動きを見てみると以下のようになります。
※わかりやすくするためにcolor列の’red’を’red(1)’, ‘red(2)’, ‘red(3)’に分けています。
color | value | sum
--------+-------+-----
blue | 100 | 100
--------+-------+----- = = = <---- フレームの始まり(UNBOUNDED PRECEDING)
red(1) | 120 | 120 | (1) | |
--------+-------+----- = | (2) | <---- 現在行 = 'red(1)'の時のフレームの終わり(CURRENT ROW)
red(2) | 120 | 240 | | (3)
--------+-------+----- = | <---- 現在行 = 'red(2)'の時のフレームの終わり(CURRENT ROW)
red(3) | 250 | 490 |
--------+-------+----- = <---- 現在行 = 'red(3)'の時のフレームの終わり(CURRENT ROW)
yellow | 160 | 160
--------+-------+-----
yellow | 210 | 370
(6 rows)
通常のスキャンと同じように一行ずつ処理をしていきます。現在の行がred(1)の時、(1)の箇所がフレームになります。そして、次の行(red(2))の時のフレームは(2)です。フレームの指定がRANGE UNBOUNDED PRECEDING AND **CURRENT ROW**なので、このように現在の行が進むにつれて、フレームも広がっていきます。そしてその結果、処理対象のデータも変わるので、sum()を使うと、120 -> 240 -> 490と増えていきます。
例えば、フレーム境界を常にパーティションの先頭・末尾に指定する(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)と、各行を処理しているときのフレームは以下のようになります。
color | value | sum
--------+-------+-----
blue | 100 | 100
--------+-------+----- = = = <----- フレームの始まり(UNBOUNDED PRECEDING)
red(1) | 120 | 490 | | |
--------+-------+----- | | |
red(2) | 120 | 490 | (1) | (2) | (3)
--------+-------+----- | | |
red(3) | 250 | 490 | | |
--------+-------+----- = = = <----- フレームの終わり(UNBOUNDED FOLLOWING)
yellow | 160 | 160
--------+----+-------
yellow | 210 | 370
(6 rows)
現在の行に関わらず、パーティションくの区間とフレームの区間が一致します。そのため、sum()関数の結果は同じ(=490)になります。
まとめ
まずはパーティションやフレームの概念について説明しました。 パーティションやフレームが理解できれば、Window関数を使いこなす準備はできていると思います。
lang: jp
これまでにまとめた記事もあわせてどうぞ。