はじめに

業務でクエリを作成する際、ちょっと時間が空いてると「あれ？どうやるんだっけ？」となりがちなので、ほぼ自分用にまとめておくことにする。
ちょっと特殊な状況もあるため、汎用的なものではないけど、一部のニッチなところには参考になるんじゃないかと思う。

あと、日を空けて書いたので句読点・ですますがコロコロ変わっています。

フィルタ

結果値に対してフィルタ適用する

QUALIFY を使うことでクエリで何やかんやした後の結果値をフィルタリングできる
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
例えば ROW_NUMBER でフィルタリングする場合など、サブクエリを作成する必要があるが、QUALIFY を使うことで簡単に書けるようになる

SELECT
    *
FROM tableName
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatedAt) = 1

注意！！
Looker Studioで上記のようなクエリを書くとエラーになる（エラー内容も不明瞭で謎）
WHERE 句が無いのが問題なので無理やり書くと解消する

SELECT
    *
FROM tableName
WHERE true
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatedAt desc) = 1

型変換

数字の文字列を数値にして取得する

SELECT 
  SACE_CAST(number as int64) number,

CASTでも良いですが、型不正のエラーでクエリが止まると嫌なのでSAFE_CAST使ってます。

日付

文字列の日付 `yyyy-mm-ddThh:mm:ss.000z` を範囲指定する

WHERE TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) >= TIMESTAMP(createDateTime)

こういう日付の足し引きも可能

current_date() - 30

文字列の日付 `yyyy-mm-ddThh:mm:ss.000z`（UTC）をJSTの日付型で取得する

9時間足して取得する方法

select
    TIMESTAMP_ADD(TIMESTAMP(dateString), INTERVAL 9 HOUR) createdAt,

JSTとして取得する方法
※タイムスタンプをデフォルトでUTCにしており、TIMESTAMP型で取得するとUTCになってしまうため、STRING型で取得する場合

select
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP(createdAt), 'Asia/Tokyo') AS createdAt,

JSTにして年月 `yyyy-mm` の文字列だけ取得する

select
    FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP(createdAt, 'Asia/Tokyo')) createMonth,

JSTにして時間帯 `HH:00` の文字列だけ取得する

select
    FORMAT_TIMESTAMP('%H:00', TIMESTAMP(createdAt, 'Asia/Tokyo')) AS timeRange,

JSON

JSONのデータを展開する

SELECT
  JSON_VALUE(user, '$._id') _id,
  JSON_VALUE(user, '$.name') name,
  JSON_VALUE(user, '$.age') age,

この例では、 user は以下のようなJSON形式のデータが入っている想定です。

{
  "_id": "xxxxxx",
  "name": "xxxxxx",
  "age": "20"
}

JSONの配列データをindex指定して取得する

SELECT
  JSON_VALUE(user, '$[0]._id') _id,
  JSON_VALUE(user, '$[0].name') name,
  JSON_VALUE(user, '$[0].age') age,

この例では、 user は以下のようなJSON形式のデータが入っている想定です。

{
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ...
}

JSONの配列データを展開してレコードとして扱う

WITH extract_json AS (
  SELECT
    createdAt,
    -- userカラムの中のjson配列をネストして展開
    JSON_EXTRACT_ARRAY(user, '$') AS json_data
  FROM
    `tableName`
)

, temp_table as (
  SELECT
    createdAt,
    -- レコードごとに name を抽出
    JSON_EXTRACT_SCALAR(JSON_EXTRACT(json_record, '$.name'), '$') name,
  FROM
    -- ネストされたjsonレコードを個別のレコードに展開
    extract_json, UNNEST(json_data) AS json_record
)

select * from temp_table

この例では、 user は以下のようなJSON形式のデータが入っている想定です。

{
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ["_id": "xxxxxx", "name": "xxxxxx", "age": "20"],
  ...
}

GA関連

GAのevent_timestampを日付型で取得する

select
    TIMESTAMP_MICROS(event_timestamp)

流入元の取得

select
    event_date,
    user_pseudo_id,
    (select value.string_value from unnest(event_params) where key = 'source') source,
from tableName
where event_name = 'first_visit'

その他

日付の疑似テーブルを生成する GENERATE_TIMESTAMP_ARRAY

「複数のデータを日付ごとにサマリしてプロットしたいけど、実際のデータレコードには存在しない日があるし、データが無いところは 0 をいれておきたいよー！」
って思ったことないですか？
一時的なデータレコードをマスタ化することで、それをベースにJoinしてサマリできます。
※解りやすくするために分けて書いてますが、実際に使うときはまとめてもOK

with date_table as (
  select
    generate_timestamp_array(
      timestamp('2024-01-01'), -- 生成する開始の日付
      timestamp('2024-04-01'), -- 生成する終了の日付
      interval 1 day           -- 生成する間隔(second / minutes / hour / day / month / year)
    ) date_array,
)

, convert_record as (
  select
    _date,
  from date_table, unnest(date_array) _date -- 配列データをunnestでレコードに展開
)

select
  format_timestamp('%Y-%m-%d', _date) date, -- 好きなフォーマットに変換して使う
from convert_record

実行すると以下のようになります。

date
2024-01-01
2024-01-02
2024-01-03
2024-01-04
2024-01-05
2024-01-06
・・・（以下略）・・・

日付以外で同じことをしたい GENERATE_ARRAY

簡単に書くとこのようになる。
※GENERATE_ARRAYの引数は開始, 終了, 間隔

select
  num,
from unnest(generate_array(1, 10, 1)) num

応用として以下のような使い方も可能。

with base as (
  select
    num,
  from unnest(generate_array(1, 10, 1)) num
)

select
  -- LPADで0埋めしてIDの形式に変換する
  concat('XXX-', lpad(cast(num as string), 2, '0')) id
from base

実行すると以下のようになる。

id
XXX-01
XXX-02
XXX-03
XXX-04
・・・（以下略）・・・