BQ ナビゲーション関数のwindow_frame_clauseについて

# 結論

省略せずにきちんと書こう

# 目的

- ナビゲーション関数と window_frame_clauseの挙動について理解する

# 仕様

- デフォルトは`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
- `SUM`関数は`ORDER BY`句を付けると累積和、付けないと総和になる
- `ROWS` は行数、`RANGE` は`ORDER BY`句で指定した列の値が参照される

# サンプルクエリで出力確認

```sql
WITH
view_logs AS (
    SELECT
        created_at,
        "hoge" AS platform_uid
    FROM
        UNNEST(GENERATE_TIMESTAMP_ARRAY(
            TIMESTAMP("2024-01-01 00:00:00"), 
            TIMESTAMP("2024-01-09 00:00:00"), 
            INTERVAL 1 DAY
        )) AS created_at
),
play_logs AS (
    SELECT
        TIMESTAMP("2024-01-02 00:00:00") AS begin_at,
        "hoge" AS platform_uid,
        "begin" AS action

    UNION ALL
    
    SELECT
        TIMESTAMP("2024-01-03 00:00:00") AS begin_at,
        "hoge" AS platform_uid,
        "begin" AS action

    UNION ALL

    SELECT
        TIMESTAMP("2024-01-07 00:00:00") AS begin_at,
        "hoge" AS platform_uid,
        "begin" AS action
),
view_session_logs AS (
    SELECT
        view_logs.*,
        play_logs.action
    FROM
        view_logs
    LEFT OUTER JOIN
        play_logs
    ON
        view_logs.platform_uid = play_logs.platform_uid
        AND view_logs.created_at = play_logs.begin_at
),
view_session_logs_attr AS (
    SELECT
        *,
        -- デフォルト
        FIRST_VALUE(IF(action = "begin", created_at, NULL) IGNORE NULLS) OVER(PARTITION BY platform_uid ORDER BY created_at ASC) AS first_value_default,
        FIRST_VALUE(IF(action = "begin", created_at, NULL) IGNORE NULLS) OVER(PARTITION BY platform_uid ORDER BY created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_value_unbounded_current,
        FIRST_VALUE(IF(action = "begin", created_at, NULL) IGNORE NULLS) OVER(PARTITION BY platform_uid ORDER BY created_at ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS first_value_current_following,

        -- デフォルト
        LAST_VALUE(IF(action = "begin", created_at, NULL) IGNORE NULLS) OVER(PARTITION BY platform_uid ORDER BY created_at ASC) AS last_value_default,
        LAST_VALUE(IF(action = "begin", created_at, NULL) IGNORE NULLS) OVER(PARTITION BY platform_uid ORDER BY created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_value_unbounded_current,
        LAST_VALUE(IF(action = "begin", created_at, NULL) IGNORE NULLS) OVER(PARTITION BY platform_uid ORDER BY created_at ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_value_current_following,

        -- デフォルト
        SUM(IF(action = "begin", 1, 0)) OVER(PARTITION BY platform_uid ORDER BY created_at ASC) AS sum_default,
        SUM(IF(action = "begin", 1, 0)) OVER(PARTITION BY platform_uid ORDER BY created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_unbounded_current,
        SUM(IF(action = "begin", 1, 0)) OVER(PARTITION BY platform_uid ORDER BY created_at ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_current_following,
    FROM
        view_session_logs
),
`--` AS (SELECT 1)

SELECT
    *
FROM
    view_session_logs_attr
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411380/13b689ec-e494-42b6-f5f0-6713bce6a9f7.png)

`LAST_VALUE`は指定行以降のログを見るときに使うことが多いけど、デフォルトは指定行以前のログを見ているので気を付けないとですね。

# 参考
↓公式doc
[Defining the window frame clause](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls#def_window_frame)