【BigQuery】WindowFrame句省略時の挙動まとめ

はじめに

ウィンドウ関数における 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のマニュアル が分かり辛すぎることですけどね。