![見出し画像](https://assets.st-note.com/production/uploads/images/32227114/rectangle_large_type_2_95c22aaf9882f0af4a58bf7fe4dfc21b.png?width=800)
BigQueryチートシート
※2022/9/24 UPDATE
データ分析・集計でよく使うクエリを調べる時間を省くためにまとめています。
URLを階層で区切る
ページごとのPVやCTRを集計するときに、階層ごとに切り出して集計しやすくする時に使ったりします。
クエリ
WITH data AS (
SELECT
'https://www.example.com/video/detail?id=100' url
)
SELECT
url,
-- //からパラメータの間をマッチさせ/で分割
split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(2)] path1,
split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(3)] path2
FROM
data
■SAFE_ORDINAL…インデックスが範囲外の場合に NULLを返す
結果
![画像1](https://assets.st-note.com/production/uploads/images/31900681/picture_pc_be5d6241d9b89be1adcbea28f0c9e4fc.png)
現在日付・タイムスタンプを取得する
売上のトレンドを逃さないようにリアルタイムで追うため、現在日付の条件で絞ったりするときに使ってます。
クエリ
SELECT
CURRENT_DATE date_utc,
CURRENT_DATE('Asia/Tokyo') date_jst,
DATETIME(CURRENT_TIMESTAMP) datetime_utc,
DATETIME(CURRENT_TIMESTAMP, 'Asia/Tokyo') datetime_jst,
CURRENT_TIMESTAMP timestamp
結果
![スクリーンショット 2021-11-08 13.18.02](https://assets.st-note.com/production/uploads/images/65198595/picture_pc_9c1554be393cd7d750e804516de65ef5.png?width=800)
年月日・時分秒を分けて取り出す
例えば、給料日の25日前後で売上に傾向がみられるか月跨ぎで分析するときなどに使ったりします。
クエリ
SELECT
stamp,
EXTRACT(YEAR from stamp) year, -- 年
EXTRACT(MONTH from stamp) month, -- 月
EXTRACT(DAY from stamp) day, -- 日
EXTRACT(DAYOFWEEK from stamp) dayofweek, -- 曜日 1(日曜日) ~ 7(土曜日)
EXTRACT(HOUR from stamp) hour, -- 時
EXTRACT(MINUTE from stamp) minute, -- 分
EXTRACT(SECOND from stamp) second -- 秒
FROM
(
SELECT
CAST(
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP, 'Asia/Tokyo') as timestamp
) as stamp
)
■EXTRACT…年月日などの特定のフィールドを取り出す
結果
![スクリーンショット 2020-12-29 17.50.18](https://assets.st-note.com/production/uploads/images/41891557/picture_pc_bb373e5b11b561c7678ef226ba992e1d.png?width=800)
unixtime(秒)をtimestampに変換
クエリ
WITH raw_data AS (
SELECT 1619531893 AS unixtime
)
SELECT
unixtime,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(unixtime), 'Asia/Tokyo') AS to_datetime
FROM
raw_data
結果
![スクリーンショット 2021-04-27 23.03.00](https://assets.st-note.com/production/uploads/images/50990466/picture_pc_a933b146e7eefa886178ca9b26dc44d0.png?width=800)
unixtime(ミリ秒)をtimestampに変換
クエリ
WITH raw_data AS (
SELECT 1619531893111 AS unixtime
)
SELECT
unixtime,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MILLIS(unixtime), 'Asia/Tokyo') AS to_datetime
FROM
raw_data
結果
![スクリーンショット 2021-04-27 23.03.00](https://assets.st-note.com/production/uploads/images/50991133/picture_pc_c63503dd7464e9538e1cc88c8fb5fabf.png?width=800)
unixtime(マイクロ秒)をtimestampに変換
クエリ
WITH raw_data AS (
SELECT 1619531893111000 AS unixtime
)
SELECT
unixtime,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(unixtime), 'Asia/Tokyo') AS to_datetime
FROM
raw_data
結果
![スクリーンショット 2021-04-27 23.12.36](https://assets.st-note.com/production/uploads/images/50991243/picture_pc_21757f2f3d34a3d6cf67e634d22a791a.png)
経過日数を出す
会員登録、最終購入からの離脱期間や、生年月日から年齢を出すときに利用してます。
データ
![画像4](https://assets.st-note.com/production/uploads/images/31901761/picture_pc_5470380c3cc560e35f58948aaf351e81.png)
クエリ
SELECT
register_stamp,
date(register_stamp, 'Asia/Tokyo') register_date,
DATE_DIFF(CURRENT_DATE('Asia/Tokyo'), date(register_stamp, 'Asia/Tokyo'), DAY) past_days_from_registered,
birth_date,
DATE_DIFF(CURRENT_DATE('Asia/Tokyo'), birth_date, YEAR) age
FROM
`bigdata.mst_users_with_dates`
結果
![画像5](https://assets.st-note.com/production/uploads/images/31901794/picture_pc_3bfbc58b14a255a4e8aac77ee37fa06e.png)
直近N日間 / N週間 / Nヶ月 / Nクオーター / N年 を求める
前日、直近1週間、直近1ヶ月、直近1クオーター、直近1年の売上集計や分析に利用します。
クエリ
SELECT
-- 現在日付
CAST('2020-10-14' as DATE) as now,
-- 1日
DATE_SUB(CAST('2020-10-14' as DATE), INTERVAL 1 DAY) as previous_day,
-- 1週間
DATE_SUB(CAST('2020-10-14' as DATE), INTERVAL 1 WEEK) as previous_week,
-- 1ヶ月
DATE_SUB(CAST('2020-10-14' as DATE), INTERVAL 1 MONTH) as previous_month,
-- 1クオーター前(3ヶ月前に同じ)
DATE_SUB(CAST('2020-10-14' as DATE), INTERVAL 1 QUARTER) as previous_quarter,
-- 1年前
DATE_SUB(CAST('2020-10-14' as DATE), INTERVAL 1 YEAR) as previous_year
結果
DATE_SUB関数を利用して、例えば2020-10-14を基準に日付を算出しています。集計クエリの条件式などに利用してください。
![スクリーンショット 2020-10-06 23.50.36](https://assets.st-note.com/production/uploads/images/36181110/picture_pc_056581edf8d8c97a6feacb3fa685fce2.png?width=800)
■DATE_SUB...DATE から指定した期間を減算する
文字列をDATE型/TIMESTAMP型に変換
広告やCMを打っていた期間に絞ってその効果をみたい時などに使っています。
クエリ
SELECT
CAST ('2020-08-03' as DATE) as _date,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp('2020-08-03 23:30:17', 'Asia/Tokyo'), 'Asia/Tokyo') as _timestamp_jst
結果
![画像6](https://assets.st-note.com/production/uploads/images/31902264/picture_pc_8c466efb22316b5cd3d82953620d8101.png)
年度ごとの四半期売上平均を集計する
四半期決算の売上サマリーを出したいときに利用します。
データ
![画像7](https://assets.st-note.com/production/uploads/images/32047340/picture_pc_c3392766b39c1065147cb537a613cd8b.png)
クエリ
SELECT
year,
-- 年度が締まっていない年は、それまでの期の平均で集計
(COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0))
/ (SIGN(COALESCE(q1, 0)) + SIGN(COALESCE(q2, 0)) + SIGN(COALESCE(q3, 0)) + SIGN(COALESCE(q4, 0))) as avg
FROM
`bigdata.quartery_sales`
結果
![画像8](https://assets.st-note.com/production/uploads/images/32047462/picture_pc_c11b0f6feaf4ef33b5d6480073b9c4f2.png)
CTR / CVR
会員登録導線、購入導線のファネル分析するときに使ったりします。
データ
![画像9](https://assets.st-note.com/production/uploads/images/32047519/picture_pc_f713c620c7354861dba46fb33ac4fa2b.png)
クエリ
SELECT
dt,
ad_id,
100.0 * IFNULL(SAFE_DIVIDE(clicks, impressions), 0) as ctr
FROM
`bigdata.advertising_stats`
■SAFE_DIVIDE...計算できない際に NULL
結果
![画像10](https://assets.st-note.com/production/uploads/images/32047628/picture_pc_477c58d6fbb9c66256ce3b7a3d656e02.png)
ランキング - スコア順にする
スコアリングした商品をおすすめ順に並び替えるときに利用してます。
データ
![画像11](https://assets.st-note.com/production/uploads/images/32047680/picture_pc_f6d70e6e337e4c0395f15983d7c59c45.png)
クエリ
SELECT
product_id,
score,
-- スコア順に一意なランキングを付与
ROW_NUMBER() OVER(ORDER BY score DESC) AS ROW,
-- 同順位を許容するランキング(1, 2, 2, 4)
RANK() OVER(ORDER BY score DESC) AS rank,
-- 同順位を許容し、同順位の次の順位を飛ばさないランキング(1, 2, 2, 3)
DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank,
-- 現在行より前の行の値を取得
LAG(product_id) OVER (ORDER BY score DESC) AS lag1,
LAG(product_id, 2) OVER (ORDER BY score DESC) AS lag2,
-- 現在行より後の行の値を取得
LEAD(product_id) OVER (ORDER BY score DESC) AS lead1,
LEAD(product_id, 2) OVER (ORDER BY score DESC) AS lead2
FROM
`bigdata.popular_products`
ORDER BY
ROW
結果
![画像12](https://assets.st-note.com/production/uploads/images/32047964/picture_pc_4a5d463d8208377d596136f139d8179b.png)
ORDER BYと集約関数と組み合わせる
ランキング最上位、最下位、TOP10の商品をピックアップするのに使ったりしてます。
クエリ
SELECT
product_id,
score,
ROW_NUMBER() OVER(ORDER BY score DESC) AS ROW,
-- ランキング上位からの累計スコア合計を計算する
SUM(score) OVER(
ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cum_score,
-- 今の行と前後一行ずつの、合計三行の平均スコアを出す
AVG(score) OVER(
ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS local_avg,
-- ランキング最上位の商品IDを取得する
FIRST_VALUE(product_id) OVER(
ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_value,
-- ランキング最下位の商品IDを取得する
LAST_VALUE(product_id) OVER(
ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_value
FROM
`bigdata.popular_products`
ORDER BY
ROW
■ウィンドウのフレーム指定
フレーム指定とは、現在のレコード位置から相対的なウィンドウを定義するための構文。
■基本形
ROWS BETWEEN [start] AND [end]
start/end
CURRENT ROW(現在行)
n PRECEDING(n行前)
n FOLLOWING(n行後)
UNBOUNDED PRECEDING(前の行全て)
UNBOUNDED FOLLOWING(後の行全て)
結果
![画像13](https://assets.st-note.com/production/uploads/images/32048283/picture_pc_29e3c0e11e0841bb8a41baf24423fc58.png)
カテゴリーごとに売上ランキング TOP N 件取得
カテゴリーごとに売上ランキングTOP10を調べるときに利用してます。
クエリ
WITH ranking_by_category AS (
SELECT
category,
product_id,
score,
ROW_NUMBER() OVER(PARTITION BY category ORDER BY score DESC) AS ROW
FROM
`bigdata.popular_products`
)
SELECT
*
FROM
ranking_by_category
WHERE
-- ここの数字でTOPNのNを指定できる
ROW <= 2
■WINDOW関数はWHERE句に指定できないので、サブクエリかWITH句で囲む必要がある。
結果
![画像14](https://assets.st-note.com/production/uploads/images/32048861/picture_pc_54430251cec71ca9f3b9354d3a6744c8.png)
カテゴリーごとの最上位を取得する
カテゴリーごとに売上No.1を調べるときに利用してます。
クエリ
SELECT
distinct category,
-- 逆に最下位とる場合は、FIRST_VALUE を LAST_VALUE にする
FIRST_VALUE(product_id) OVER (partition by category order by score desc)
FROM
`bigdata.popular_products`
■逆に最下位とる場合は、FIRST_VALUE を LAST_VALUE にすればよい。
結果
![画像15](https://assets.st-note.com/production/uploads/images/32048925/picture_pc_75e47a5086c5d8dd02c316aa66c95d2c.png)
UU数の推移
UU数の増加傾向を時系列で可視化するときに利用します。
データ
一般的なユーザーテーブルにおいて、レコードの作成日を会員登録日として利用します。以下のイメージでいう「created_at」カラムを会員登録日とします。
![スクリーンショット 2020-11-28 11.24.56](https://assets.st-note.com/production/uploads/images/39713283/picture_pc_101ee538dbdded98dab9e9f95e9becfc.png?width=800)
クエリ
WITH raw_data AS
(
-- ユーザーテーブルに置き換えてください
SELECT
created_at,
EXTRACT(YEAR from created_at) year,
EXTRACT(MONTH from created_at) month,
CONCAT(
EXTRACT(YEAR from created_at),
'/',
EXTRACT(MONTH from created_at)
) as year_month
FROM
bigdata.users
),
uu_by_month AS (
-- 年月ごとのUU数を集計
SELECT
year_month,
count(*) AS register_uu
FROM
raw_data
GROUP BY
year_month
)
SELECT
year_month,
register_uu,
-- UU数を積み上げ
SUM(register_uu) OVER (ORDER BY year_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as uu
FROM
uu_by_month
ORDER BY
year_month
■ その月から過去分までのUU数を積み上げるために、現在行から前の行全ての合計を計算する
[構文]
ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING
CURRENT ROW:現在行
UNBOUNDED PRECEDING:前の行全て
結果
![スクリーンショット 2020-11-28 12.07.40](https://assets.st-note.com/production/uploads/images/39715692/picture_pc_58b763199d5ddc9718f6e96d79eb9086.png?width=800)
グラフにすると…
![UU数推移](https://assets.st-note.com/production/uploads/images/39715710/picture_pc_6790f5986c1e5880f35fa3642c0ecd9a.png?width=800)
CSVデータを1行づつ展開
データレイクのrawデータを整形してデータウェアハウスに入れるときに使っています。
データ
![スクリーンショット 2020-08-07 23.36.06](https://assets.st-note.com/production/uploads/images/32049241/picture_pc_fedf1426e47802c8ddf7e1ee318dde55.png)
クエリ
WITH user_favorite_fruits AS (
SELECT
'jhon' AS user_name,
'apple,banana,strawberry' AS fruits
UNION ALL
SELECT
'luck' AS user_name,
'pine' AS fruits
)
SELECT
user_name,
fruit
FROM
user_favorite_fruits,
-- SPLITした結果をUNNEST
UNNEST(SPLIT(fruits, ",")) AS fruit
■SPLIT...区切り文字を引数に、STRING 型の ARRAY または BYTES 型の ARRAYを返します。
■UNNEST...UNNEST 演算子は ARRAY を受け取り、ARRAY 内の各要素を 1 行にしてテーブルを返します。
結果
![スクリーンショット 2020-08-07 23.35.14](https://assets.st-note.com/production/uploads/images/32049156/picture_pc_7ad760bdf00fa11811c9f9a2f77cdc0a.png)
JSON形式データをカラムごとに展開
データレイクのrawデータを整形してデータウェアハウスに入れるときに使っています。
データ
![画像18](https://assets.st-note.com/production/uploads/images/32049350/picture_pc_386fc2ad69d49343882b395816851c09.png?width=800)
クエリ
WITH data AS (
SELECT
12345 AS user_id,
'purchase' AS action,
'{"item_id": 1, "item_name": "ゲームセンターCX DVD/BD", "price": 5000, "quantity": 1}' AS detail
)
SELECT
user_id,
action,
JSON_EXTRACT(detail, '$.item_id') AS item_id,
JSON_EXTRACT(detail, '$.item_name') AS item_name,
JSON_EXTRACT(detail, '$.price') AS price,
JSON_EXTRACT(detail, '$.quantity') AS quantity
FROM
data
■JSON_EXTRACT...JSON値をSTRINGで返す。
結果
![画像19](https://assets.st-note.com/production/uploads/images/32049462/picture_pc_6033fccbcf1259c834fd6229130122b8.png?width=800)
無からカレンダー生成
日ごとに集計すると日付が歯抜けになるケースがあるので、日付マスタ的なテーブルを生成してジョインするときに利用するとよし。
クエリ
WITH _date AS (
SELECT *
FROM
UNNEST(
GENERATE_DATE_ARRAY(
CAST('2020-07-01' as DATE),
DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL 9 DAY)
)
) AS local_date
ORDER BY 1
)
,cal AS (
SELECT
*,
EXTRACT(DAYOFWEEK FROM local_date) as day_of_week_raw
FROM
_date
)
SELECT
*,
CASE
WHEN day_of_week_raw = 1 THEN 'Sun'
WHEN day_of_week_raw = 2 THEN 'Mon'
WHEN day_of_week_raw = 3 THEN 'Tue'
WHEN day_of_week_raw = 4 THEN 'Wed'
WHEN day_of_week_raw = 5 THEN 'Thu'
WHEN day_of_week_raw = 6 THEN 'Fri'
WHEN day_of_week_raw = 7 THEN 'Sat'
END
FROM
cal
結果
![画像20](https://assets.st-note.com/production/uploads/images/32049751/picture_pc_11bdd704dedcf9df530b0ee143a4719b.png?width=800)
タイムトラベルで過去データの復元
タイムトラベルでその時点のデータを参照可能です。タイムトラベル可能期間は7日間です(記事時点)。
クエリ
SELECT
*
FROM
`dataset.table`
-- 参照したい時点の日時を指定する
FOR SYSTEM_TIME AS OF TIMESTAMP('2022-09-23 14:00:00', 'Asia/Tokyo');
ただし、削除されたテーブルはbqコマンドでテーブルを復元する利用する必要があります。クエリの実行はできないようです(記事時点)。
$ bq cp ${dataset}.${table}@${UNIXタイム(ミリ秒)} ${dataset}.${restored_table}
# 例:bq cp mydataset.table1@1624046611000 mydataset.table1_restored
UNIXタイムスタンプ(ミリ秒)は以下で取得できます。
SELECT
UNIX_MILLIS(TIMESTAMP('2022-09-24 14:00:00', 'Asia/Tokyo'))
公式Doc: タイムトラベルを使用した履歴データへのアクセス
--- ✐ --- ✐ --- ✐ ---
参考資料
■ BigQuery ドキュメント
基本的にはBigQueryのドキュメントをみてます。
■ ビッグデータ分析・活用のためのSQLレシピ
黒魔術として名高い「ビッグデータ分析・活用のためのSQLレシピ」。行動ログ分析、集計などで参考にさせていただいてます。これ以上おすすめする本はないと言えるくらい有用な本でした。
■ Qiita
無からカレンダー生成はQiitaの @shiozaki さんの記事を参考にさせていただきました。
よろしければサポートお願いします!クリエイター費として利用させていただきます!