実現したいこと

叶えたかったことは以下の通り。

特定のユーザーIDに絞り、セッション数が見たい
日付は常に「◯日前」と指定したい
- 日次でクエリを更新してスプレッドシートに反映させる、という流れを作りたかったため
出力する日付は指定したフォーマットにしたい
- 諸事情により。

作成したクエリ

SELECT
    user_id,
    FORMAT_DATE("%Y/%m/%d", CAST(CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2), '-', SUBSTR(event_date, 7, 2) ) as DATE)) as event_date,
    count(distinct (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS ga_session_count
FROM
    `テーブル名`
WHERE
    _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 DAY))
    and user_id in ("10001, 10002, 10003......")
GROUP BY user_id, event_date
ORDER BY user_id;

自分用 - 何がおこってるかメモ

SELECT
    -- ▼ まずはユーザーIDがほしい
    user_id,
    -- ▼ 日付の型を変換しないとFORMAT_DATEが使えないっぽい。「参照」に記載しているリンクから拝借。
    FORMAT_DATE("%Y/%m/%d", CAST(CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2), '-', SUBSTR(event_date, 7, 2) ) as DATE)) as event_date,
    -- ▼ 「その日のセッションが何回あったか」をカウントするには ga_session_number を数えればよさそう
    count(distinct (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS ga_session_count
FROM
    `テーブル名`
WHERE
    -- ▼ 欲しい日付は「◯日前」指定なので、CURRENT_DATE に対して「◯日前」という指定をしてみた。以下は「2日前」を指定。
    _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 DAY))
    -- ▼ 欲しいユーザーの対象をここで絞る
    and user_id in ("10001, 10002, 10003......")
-- ▼ 同じユーザーIDと日付が出るので、重複する行はまとめる
GROUP BY user_id, event_date
ORDER BY user_id;

参照

BigQueryでstring型の文字列からdate型の日付に変換する方法

BigQueryビギナーなので、間違ってたりもっと良いやり方があったりしたら教えてください🙌