はじめに
業務でクエリを作成する際、ちょっと時間が空いてると「あれ？どうやるんだっけ？」となりがちなので、ほぼ自分用にまとめておくことにする。
ちょっと特殊な状況もあるため、汎用的なものではないけど、一部のニッチなところには参考になるんじゃないかと思う。
あと、日を空けて書いたので句読点・ですますがコロコロ変わっています。
フィルタ
結果値に対してフィルタ適用する
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
・・・（以下略）・・・