結論

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

目的

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

仕様

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

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

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

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

参考

↓公式doc
Defining the window frame clause