はじめに
ウィンドウ関数における Window Frame 句は省略時の挙動が場面によって変わります。
場面に合わせて最も適していそうなものになっているようですが、ちゃんと理解せずにいたら少し混乱したのでまとめました。
BigQuery以外のDBMSでも同じかもしれませんが、検証していないのでBigQueryについてのみ書きます。
Window Frame が何かという部分については当記事では扱いません。
「ウィンドウ関数（分析関数）を何となく使っているけど、 ROWS BETWEEN〜 の部分の動作がイマイチわかってない」という方向けです。
定義
定義自体はシンプルで、以下の2パターンです。
ウィンドウ指定内に ORDER BY 句が存在しない場合
下記の指定を行ったのと同じ動作となります。
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
つまり
COUNT(*) OVER()
は、以下と同じです。
COUNT(*) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ウィンドウ指定内に ORDER BY 句が存在する場合
下記の指定を行ったのと同じ動作となります。
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
COUNT(*) OVER(ORDER BY foo)
は、以下と同じです。
COUNT(*) OVER(ORDER BY foo RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ソート順を指定すると集計範囲が現在行までになる
検証
ORDER BY 指定なし
下記SQLで動作検証を行います。
WITH values AS (
SELECT
val
FROM
UNNEST(GENERATE_ARRAY(1, 5, 1)) AS val
)
SELECT
*,
COUNT(*) OVER w AS cnt
FROM
values
WINDOW w AS ()
実行結果
| val | cnt |
|---|---|
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
全てのレコードが集計対象となるので、COUNT() の結果は全行 5 になります。
ORDER BY 指定あり
上記SQLに ORDER BY を追加しました。
WITH values AS (
SELECT
val
FROM
UNNEST(GENERATE_ARRAY(1, 5, 1)) AS val
)
SELECT
*,
COUNT(*) OVER w AS cnt
FROM
values
WINDOW w AS (ORDER BY val)
実行結果
| val | cnt |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
以下の動作となっています。
-
val = 1のレコードを処理時は、同レコードのみが集計対象となるので、COUNT()の結果は1になる -
val = 2のレコードを処理時は、val = 1とval = 2のレコードが集計対象となるので、COUNT()の結果は2になる
ソート順を追加したのみですが、集計対象まで変わってしまうので要注意です。
関数呼び出し箇所で ORDER BY 指定
名前付きウィンドウの方では ORDER BY を記載せず、関数呼び出しの方に記載すると、ソート順を指定した関数のみ集計対象が変わります。
WITH values AS (
SELECT
val
FROM
UNNEST(GENERATE_ARRAY(1, 5, 1)) AS val
)
SELECT
*,
COUNT(*) OVER (w) AS cnt1,
COUNT(*) OVER (w ORDER BY val) AS cnt2
FROM
values
WINDOW w AS ()
実行結果
| val | cnt1 | cnt2 |
|---|---|---|
| 1 | 5 | 1 |
| 2 | 5 | 2 |
| 3 | 5 | 3 |
| 4 | 5 | 4 |
| 5 | 5 | 5 |
混乱を招く要因
最初に書いた通り、定義自体は2パターンで特にややこしいということはないです。
それなのに、初学者が集計関数、ナビゲーション関数について都度調べていると混乱することも多そうです。
その理由は、「主なナビゲーション関数は ORDER BY 指定が必須である」ということにあると思います。
例えば、 LAST_VALUE() について解説した記事には下記のように書いてあったりします。
LAST_VALUE()の定義は下記です。LAST_VALUE(foo) OVER (ORDER BY bar)集計範囲を省略すると
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWとなり、現在行までが対象となります。
全ての行を対象にする場合はROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGと書く必要があります。
大元の定義を知らずにこれを読むと、「集計範囲を省略すると現在行までになるのか」と誤解する可能性があります。
LAST_VALUE() は必ずソート順を指定するので、 結果的に 現在行までが初期値にはなるのですが。
一番の要因は BigQueryのマニュアル が分かり辛すぎることですけどね。