BigQuery の Window Frame 句について

以下についての噛み砕きです

https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts#def_window_frame

## Window Frame 句とは

集計関数で使うウィンドウにおいて、`ORDER BY` で指定した並び順に従った行や値の範囲についてフィルタを掛けるものです。

`ナビゲーション関数や番号付け関数（RANK() など）では、Window Frame 句を使用できません。` とありますが、ナビゲーション関数である `FIRST_VALUE`、 `LAST_VALUE` や `NTH_VALUE` などが実は使えます。

## 構文

EBNF通りの構文は以下のようになります。

![Screenshot_2021-05-30 Railroad Diagram Generator.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/57758/9fd92647-e2b7-09d8-bd75-fa33b61daed4.png)

## 意味の解説

RANGEとROWでフィルタ対象の指定の方法を選択します。実際の分析の場合 ROW で指定することが多いでしょう。

- `RANGE`: `ORDER BY` 句で指定した値の範囲を指定する。この時の指定される範囲は対象の行を基準に指定する(たとえば、`RANGE BETWEEN 10 PRECEDING AND 50 FOLLOWING` と指定されたときに現在の行の値が x だった場合、x - 10 から x + 50 の値がフィルタされる
    - なお、数値型しか対応してないのでタイムスタンプなどは適宜数値型にする必要がある。
- `ROWS`: `ORDER BY` 句でソートした行の範囲を指定する
    - `ROWS BETWEEN 10 PRECEDING AND 50 FOLLOWING` としたときは現在の行数の10個前から15個後までとなります。

(以下ROWの場合)
`expr PRECEDING` は「その行より expr 行前」、`expr FOLLOWING` は「その行より expr 行後」を意味します。`UNBOUNDED PRECEDING` は `∞ PRECEDING`、`UNBOUNDED FOLLOWING` は `∞ FOLLOWING` という意味で、ソートしたときの最初あるいは最後の行をフィルタ範囲にします。
行の範囲がテーブルの範囲を越えた場合では範囲におさまる分のみ計算されます。たとえば 2行目で `3 PRECEDING AND CURRENT ROW` という範囲では1行目と2行目が対象なります。なお、1行目で `BETWEEN 3 PRECEDING AND 2 PRECEDING` とした場合は NULL となります。

BETWEEN で挟まず直接範囲を指定することもでき、この場合意味は `BETWEEN ～ AND CURRENT ROW` と同じ意味になります。ただ、混同を避けるために末尾、指定した方がベターかと思われます。

## 例

フィールド x に 1 ～ 100 が入っているテーブルに `SELECT SUM(x) OVER (ORDER BY x ROWS ～)` をかけるとします。
このときの `x = 50` に着目したときに、どのフィールドが計算対象になるかを以下に示します。

- (1) `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
- (2) `ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING`
- (3) `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW`
- (4) `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`
- (5) `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`
- (6) `ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING`

|x | (1) | (2) | (3) | (4) | (5) | (6) |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | ✅ | ✅ | |
| 2 | ✅ | ✅ ||
| ... |... |... |... |... | ... |... |... |
| 48 | ✅ | ✅| ||
| 49 | ✅ | ✅| ✅| ✅|
| **50** | ✅ || ✅|  ✅|✅
| 51 | ✅||| ✅ |✅|✅|
| 52 | ✅|||| ✅|✅|
| ... |... |... |... |... |... |... |
| 99 | ✅|||| ✅|✅
| 100 | ✅|||| ✅|✅

ちなみにこちらの実行可能なクエリは以下のようになります。今回は行数と値が一致しているので、ROWSをRANGEにしても同じ結果になります。

```sql
SELECT
    x,
    SUM(x) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED  PRECEDING AND UNBOUNDED FOLLOWING) AS f1,
    SUM(x) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS f2,
    SUM(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS f3,
    SUM(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS f4,
    SUM(x) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS f5,
    SUM(x) OVER (ORDER BY x ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS f6,
FROM UNNEST(GENERATE_ARRAY(1, 100)) x
WHERE TRUE -- 注意: WHERE x = 50 だとすべてが 50 になる。
QUALIFY x = 50

/*
Result:
[
  {
    "x": "50",
    "f1": "5050",
    "f2": "1225",
    "f3": "99",
    "f4": "150",
    "f5": "3825",
    "f6": "3775"
  }
]
*/
```

## 応用例

https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts#analytic_function_examples を見てください。