BigQuery のナビゲーション関数の ROWS句の検証

BigQuery のナビゲーション関数の ROWS句の使い方を理解するために検証してみました。

[こちらのドキュメント](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls#compute_a_moving_average)の Produceテーブルを使って、ROWS句の引数を変更するとどう変わるかを、下記のクエリで検証用 Produceテーブル作成して検証します。

##### 作成クエリ

```sql
WITH Produce AS
(
  SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'banana', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable'
)
SELECT
  *

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING
    ) AS avg_purchases_1

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS avg_purchases_2

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
    ) AS avg_purchases_3

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING
    ) AS avg_purchases_4

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS avg_purchases_5

  , AVG(purchases)
    OVER (
      ORDER BY purchases
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS avg_purchases_6

FROM Produce
ORDER BY purchases
```

##### Produce テーブル

| item | purchases | category | avg_purchases_1 |avg_purchases_2 |avg_purchases_3 |avg_purchases_4 |avg_purchases_5 |avg_purchases_6 |
|:-----------|------------:|:------------:|------------:|----:|----:|----:|----:|----:|
| banana     | 2           |fruit        | 2.0  |  2.0       |2.0 |9.0 |2.0 |9.0 |
| leek       | 2           |vegetable    | 5.0  |  4.0       |4.0 |10.4 |2.0 |9.0 |
| apple      | 8           |fruit        | 8.5  |  6.3333  |5.25 |12.5 |4.0 |9.0 |
| cabbage    | 9           |vegetable    | 9.5  |  9.0       |7.25 |14.0 |5.25 |9.0 |
| lettuce    | 10          |vegetable    | 16.5 | 14.0       |12.5 |16.5 |6.2 |9.0 |
| kale       | 23          |vegetable    | 23.0 | 16.5     |14.0 |23.0 |9.0 |9.0 |


avg_purchases_1 〜 6 はどう計算されているかというと、

- avg_purchases_1 （`ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING`）だと下記のイメージ
  - 現在の行と、現在の行から1個後の範囲（赤枠内）の purchaces の平均

<img width="300" alt="image.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/397664/61f7e2cc-0741-d319-95e8-bfe693777e10.png">

- avg_purchases_2 （`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`）だと下記のイメージ
  - 現在の行より１個前、1個後の範囲（赤枠内）の purchaces の平均

<img width="300" alt="image.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/397664/e80a5a06-8d80-8d68-9f95-1fadd9497544.png">

- avg_purchases_3 （`ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING`）だと下記のイメージ
  - 現在の行より2個前、1個後の範囲（赤枠内）の purchaces の平均

<img width="300" alt="image.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/397664/cbefa77b-17d6-0bfe-f315-3277e759395c.png">

- avg_purchases_4 （`ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING`）だと下記のイメージ
  - 現在の行と、最後の行の範囲（赤枠内）の purchaces の平均

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/397664/40100b9d-e42d-afd3-e1bc-e9da47087ecd.png)

- avg_purchases_5 （`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`）だと下記のイメージ
  - 最初の行と、現在の行の範囲（赤枠内）の purchaces の平均

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/397664/6d4a6d46-b93a-d4e2-38fe-df79c785b095.png)

- avg_purchases_6 （`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`）だと下記のイメージ
  - 最初の行と、最後の行の範囲（赤枠内）の purchaces の平均

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/397664/58d27bcd-2411-8a61-2445-efdb1500ed46.png)