Open2024/05/22にコメント追加2

BigQuery SQLメモ

ワイルドカードをテーブル名で使う

SELECT
    *
FROM `firebase.firebase.events_*`
WHERE _TABLE_SUFFIX = '20240101'

UNNESTをカラム上で定義する

SELECT
    (
        SELECT CAST(value.string_value AS INT64)
        FROM UNNEST(event_params) as event_params
        WHERE event_params.key = "user_id"
    ) AS user_id
FROM events_table

event_timestampの日時表示変換

DATETIME(FORMAT_TIMESTAMP(
    '%Y-%m-%d %H:%M:%S',
    TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), SECOND),
    "Asia/Tokyo"
))

fagai

イベントの前後のイベントタイムスタンプを手に入れる

この例だとfirebaseのuser_pseudo_idを基準に一個前と一個後のイベントのタイムスタンプを取得している

SELECT 
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
    LAG(TIMESTAMP_MICROS(event_timestamp)) OVER (PARTITION BY user_pseudo_id ORDER BY TIMESTAMP_MICROS(event_timestamp)) AS prev_event_timestamp,
    LEAD(TIMESTAMP_MICROS(event_timestamp)) OVER (PARTITION BY user_pseudo_id ORDER BY TIMESTAMP_MICROS(event_timestamp)) AS next_event_timestamp
FROM `firebase.firebase.events_*`