BigQuery SQLでややこしい処理を書くための小ネタ集

Transcript

1. AI Community 2025.11.13 NISHIKAWA, Daisuke GO株式会社 BigQuery SQLでちょっとややこし い処理を書くための小ネタ集 AI技術共有会
2. 2 項目 01｜自己紹介／背景 02｜データ生成を使った小ネタ 03｜履歴データの小ネタ 04｜位置データの小ネタ
3. 3 自己紹介 ▪ 西川 大亮 ▪ 2019/4 DeNA入社、2020/4 GO転籍(当時Mobility Technologies) ▪ 前職: 中堅SIer ▪ 14年研究所、3年コンサル ▪ 顧客向け技術紹介と火消しが主な仕事 （Projが燃える匂いには敏感） ▪ 趣味 ▪ ゴルフ(年数回) ▪ 競馬予想(サボり気味) ▪ 設計欲を満たせるゲームにハマりやすい ▪ やっていること ▪ タクシーアプリ『GO』（主に乗務員端末）の挙動解析 ▪ 課題解決：原因調査→類型化→自動検知 ▪ 性能改善：現状把握→KPI定義→レポート＋指針策定 ▪ システムよりも人間系の解析が中心
4. 4 ごめんなさい。まとまってお話できるネタがありませんでした。 ですので小ネタ集となっております。 背景① 中央のイラストはSlideのGemini では作られなかった。残念
5. 5 ▪ 時々こんな感じで怒られながらクエリを書いています (BigQueryって意外とキャパ小さいですよね？) ▪ ややこしい処理をシンプルに書きたいなぁ、と日々思っています ▪ Geminiに聞いた範囲だと性能や設計方針の資料はあるけど、今回取り上げる ような細かい話はあまりなさそうなので、小ネタ集としてまとめました ▪ 普段SQLを読み書きしている人向け 背景②
6. 6 02 データ生成を使った小ネタ
7. 7 やりたいこと • １時間単位のイベント数の推移をグラフで見たい 処理方針 • truncで丸めて集計するとログがない時刻で欠損 ◦ グラフにすると山と山を繋いだ形になりNG ◦ テーブルでも欠損があるとかなり見づらい • 対策: 期間を全パターン生成して(unnest~)、集 計結果を流し込む ◦ ただし期間の定義が必要 • 複数のイベントログでfull outer joinするのはな んとなく全部埋まってそうに見えて欠損があるの で欠損を埋める目的ではアンチパターン 集計時に欠損時刻を埋める① 時刻 ユーザID 11/13 13:30 1 11/13 13:45 2 11/13 15:10 3 … … 時刻(1時間毎) 件数 11/13 13:00 2 11/13 14:00 0 11/13 15:00 1 … … イベントログ １時間毎のイベント件数 素直に集計すると この0は出ない select timestamp_trunc(ts, hour) as ymdh, count(1) as cnt, from event_log group by ymdh order by ymdh -- debug select ymdh, ifnull(cnt, 0) as cnt, from ( select timestamp_trunc(ts, hour) as ymdh, from unnest(generate_timestamp_array( '2025-10-01', '2025-11-01', interval 1 hour)) as ts ) left outer join ( select timestamp_trunc(ts, hour) as ymdh, count(1) as cnt, from event_log group by ymdh ) using(ymdh) order by ymdh -- debug 欠損時刻あり 欠損時刻なし
8. 8 集計時に欠損時刻を埋める② • 長期（大量）のレコードは生成できない ◦ ２ヶ月のマイクロ秒単位で生成失敗 ◦ 処理方法を変えないとダメ なぜこれをやったか思い出せない…
9. 9 SQLでパラメータの組み合わせを作る • 似たような小ネタとしてパラメータをSQLで作ってしまう方法 ◦ 書き捨てなのでテーブルにしたくない場合とか • パラメータを配列で用意してcross join で組み合わせる • 開始日を1週間1日ずつ取り、集計期間を5、7、14日に取った例 select start_date, date_add(start_date, interval days day) as end_date, days, -- debug from unnest(generate_date_array('2025-11-06', '2025-11-12')) as start_date cross join unnest([5, 7 ,14]) as days start_date end_date days 2025/11/06 2025/11/11 5 2025/11/06 2025/11/13 7 2025/11/06 2025/11/20 14 2025/11/07 2025/11/12 5 … … … これで7x3=21パターンのパラメータ ができる
10. 10 03 履歴データの小ネタ
11. 11 マスタの変更履歴から状態を復元する① やりたいこと • 端末ログに対応する車両を知りたい ◦ 端末は入れ替わりがある ◦ ある時点での端末IDに紐づく車両IDが必要になる 車両ID 更新時刻 端末ID 車両番号 事業所ID … 1 2025/11/12 101 001 1003 1 2025/06/01 109 001 1003 1 2023/01/01 109 025 1001 1 2022/10/01 96 025 1001 2 2025/11/05 113 01 2001 … … … … … 車両マスタ更新履歴 †マスタの変更履歴を全て残す方法をSlowly Changing Dimensions Type 4と呼ぶようです 端末ID 車両ID 開始 終了 101 1 2025/11/12 109 1 2025/06/01 2025/11/12 109 1 2023/01/01 2025/06/01 96 1 2022/10/01 2023/01/01 113 2 2025/11/05 … … … … 端末IDー車両IDペア（重複あり） 処理方針 • 現時点のマスタでは不十分。変更履歴†から復元する • 更新時刻のleadを取る ◦ ただし同じペアが複数行出てしまう（赤枠） select -- start_tsは閉区間, end_tsは開区間 car_id, device_id, updated_at as start_ts, lead(updated_at) over (partition by car_id order by updated_at) as end_ts, from car_history order by car_id, start_ts desc -- debug
12. 12 マスタの変更履歴から状態を復元する② 端末ID 車両ID 開始 終了 101 1 2025/11/12 109 1 2023/01/01 2025/11/12 96 1 2022/10/01 2023/01/01 113 3 2025/11/05 … … … … 端末IDー車両IDペア（重複なし） やりたいこと • 端末ログに対応する車両を知りたい • ただし、人が見るので変更がない期間は １行にまとめて欲しい 車両ID 更新時刻 端末ID 車両番号 事業所ID … 1 2025/11/12 101 001 1003 1 2025/06/01 109 001 1003 1 2023/01/01 109 025 1001 1 2022/10/01 96 025 1001 2 2025/11/05 113 01 2001 … … … … … 車両マスタ更新履歴 処理方針 • 連続する同一状態に同じ番号を振る ◦ １つ前の履歴と比較して変更があっ たらフラグを立てる ◦ フラグの累積和を取り番号とする 端末ID 車両ID 更新時刻 # 101 1 2025/11/12 2 109 1 2025/06/01 1 109 1 2023/01/01 1 96 1 2022/10/01 0 113 3 2025/11/05 0 … … … … ユーザごとのフラグの累積和
13. 13 マスタの変更履歴から状態を復元する③ with car_device_pair as ( select *, sum(is_changed) over (partition by car_id order by updated_at rows between unbounded preceding and current row) as serial_no, from ( select car_id, device_id, if (lag(device_id) over w != device_id, 1, 0) as is_changed, updated_at, ifnull(lead(updated_at) over w, '3000-01-01') as lead_updated_at, from car_history window w as (partition by car_id order by updated_at) ) ) select car_id, device_id, min(updated_at) as start_ts, max(lead_updated_at) as end_ts, from car_device_pair group by car_id, device_id, serial_no order by car_id, start_ts -- debug １つ前の履歴と比較†して変更があったらフラグを立てる フラグの累積和を取り番号を作る この番兵がないと直近の期間が作られないので注意 ※分析関数(ここだとlagとsum)は入れ子にできないので 1ステップずつ副問になってしまうのは残念な所 †複数項目あるならformat('%t %t %t', val1, val2, val3) のように１つの値にまとめると楽
14. 14 イベントログから期間を作る① やりたいこと • ログイン／ログアウトイベントからログ イン期間を作る ◦ エリア出入りやアイテム脱着も同様 • ただしログインセッションIDはない 時刻 ユーザID 11/13 13:30 1 11/13 13:45 2 11/13 15:10 3 … … ログインログ ログアウトログ 時刻 ユーザID 11/13 17:10 3 11/13 17:55 1 11/13 18:10 4 … … ログイン ログアウト ユーザID 11/13 13:30 11/13 17:55 1 11/13 13:45 2 11/13 15:10 11/13 17:10 3 … … ログイン期間 ユーザID 時刻 種別 # 1 11/12 18:55 ログアウト 0 1 11/13 13:30 ログイン 1 1 11/13 17:55 ログアウト 1 1 11/14 13:10 ログイン 2 1 11/14 13:11 ログイン 2 1 11/14 18:11 ログアウト 2 1 11/14 18:12 ログアウト 2 1 11/15 13:00 ログイン 3 処理方針 • 2つのイベントログをを1つにつなげてセッション番号を降る ◦ ログイン期間なら(１つ前が)ログアウト→ログイン時にフラグを立てる ◦ フラグの累積和を取りログインセッション番号を作る • 同種のログが連続した場合は最長になる ◦ 異常値の考慮は必須（ログにクリーンさを期待してはいけない） ユーザごとのフラグの累積和
15. 15 イベントログから期間を作る① with event_log as ( select user_id, ts, 'login' as tp, from login union all select user_id, ts, 'logout' as tp, from logout ) select user_id, min(if(tp='login', ts, NULL)) as login_ts, max(if(tp='logout', ts, NULL)) as logout_ts, from ( select *, sum(is_start) over ( partition by user_id order by ts rows between unbounded preceding and current row ) as session_no, from ( select *, if (lag(tp) over (partition by user_id order by ts) = 'logout' and tp = 'login', 1, 0) as is_start, from event_log ) ) group by user_id, session_no having login_ts is not NULL -- 最初のログがログアウトを除外 order by user_id, login_ts -- debug 2つのイベントログを1つにつなげる ログアウト→ログイン時にフラグ１を立てる フラグの累積和を取りログインセッション番号を作る 単にmin(ts)とmax(ts)だと集計期間の都合で ログインがないケースで問題となる
16. 16 種類の異なる状態を組み合わせる やりたいこと • ログイン中のエリア滞在時間が知りたい ◦ ログインとエリア出入りは独立事象なので境界値 はバラバラになる ◦ 結合後に境界値の調整が必要 ログイン エリア内 エリア内 エリア内 select --どちらも開始が閉区間、終了が開区間 l.car_id, greatest(l.login_ts, a.enter_ts) as start_ts, least(l.logout_ts, a.exit_ts) as end_ts, from login_session as l inner join in_area as a on l.car_id = a.car_id and l.login_ts < a.exit_ts and a.enter_ts < l.logout_ts ※ どちらの式にも開区間の変数があるので等号はつかない 処理方針 • 結合条件 ◦ 一方が他方に少しでもかかっていたら結合する • 境界値の調整 ◦ 両者が成立する範囲に切り落とす
17. 17 深夜にエリアにいたログイン時間のみ集計する やりたいこと • …はタイトルそのまま ◦ 労働基準法における深夜は22~5時 ログイン エリア内 エリア内 エリア内 深夜 処理方針 • 同様に深夜期間を作って先程の結果と組み合わせる ◦ 結合条件や境界値の調整も同じ • 固定値だからとhourを抜いてif で何とかしようとす るとハマる ◦ 開始時刻と終了時刻のそれぞれで22時前、0時前、 5時前、での場合分けが必要 ◦ 加えて２４時間超経過したときの対応も必要 with late_night as ( select timestamp_add(ts, interval 22 hour) as late_nighit_start_ts, timestamp_add(ts, interval 24+5 hour) as late_nighit_end_ts, from unnest(generate_timestamp_array( '2025-10-31', '2025-11-07', interval 1 day)) as ts ) select car_id, greatest(start_ts, late_nighit_start_ts) as start_ts, least(end_ts, late_nighit_end_ts) as end_ts, from ( select --どちらも開始が閉区間、終了が開区間 l.car_id, greatest(l.login_ts, a.enter_ts) as start_ts, least(l.logout_ts, a.exit_ts) as end_ts, from login_session as l inner join in_area as a on l.car_id = a.car_id and l.login_ts < a.exit_ts and a.enter_ts < l.logout_ts ) inner join late_night as n on n.late_nighit_start_ts < end_ts and start_ts < n.late_nighit_end_ts ※ ここまでtimestampで書いてしまいましたが、JSTで扱いたいのでdatetimeで書いた方が良いです。 元データはtimestampなので、どこでdatetimeに切り替えるかは結構悩ましいです。 1つ前のスライドの コード
18. 18 04 位置データの小ネタ
19. 19 場所をラフにグルーピングする やりたいこと • 車両がよく使う待機場所を知りたい 処理方針 • 緯度経度そのままだと集計が難しい ◦ 測定誤差 ◦ そもそも全く同じ所に停まらない • uber/h3-jsを使う ◦ GCSに置いて create function の (option library=~)で指定する（安全性担保） ◦ h3.geoToH3をラップした永続関数を作成 ◦ h3 indexが取れる with location as ( select * from unnest([ struct(35.680444 as lat, 139.768493 as lon, 1 as no), struct(35.681449, 139.765817, 2) ]) ) select *, func.geoToH3(lat, lon, 8) as h3_res_08, func.geoToH3(lat, lon, 9) as h3_res_09, func.geoToH3(lat, lon, 10) as h3_res_10, from location ※ 東京駅の丸の内口(黄)と八重洲口(紫)はresolusion=10(緑)だと離れた ブロック、9(青)だと隣接ブロック、8(黄)だと統合される
20. 20 一番近い設備を見つける やりたいこと • EVタクシーの充電場所を知りたい ◦ 充電器の位置情報はある ◦ 車両の充電記録にも位置情報がある ◦ ただし車両の充電記録には充電器のIDはない ◦ ２つの位置は完全一致しない • 紐づける処理での総当りは避けたい ◦ 距離が近い組み合わせだけで評価したい 処理方針 • uber/h3-jsのh3.kRingで隣接するh3 indexが取れる ◦ 充電器に近いエリアが定義できる • 車両の緯度経度をh3 indexに変換して結合すればOK ◦ ここから車両と充電器の距離を求めて確定させる ◦ フィルターをh3ブロックの直径以下にして偏りを防ぐ with location as ( select * from unnest([ struct(35.680444 as lat, 139.768493 as lon, 1 as charger_id), struct(35.681449, 139.765817, 2) ]) ) select * except(kring_array) from ( select *, func.kRing(func.geoToH3(lat, lon, 12), 1) as kring_array from location ) cross join unnest(kring_array) as kring_h3_index ※ 東京駅の丸の内口(黄)と八重洲口(紫)のresolusion=12での自身と隣接ブロック 一度この範囲でフィルター後に距離を求める
21. 21 ▪ 今回はBigQueryでややこしい処理を書くときの小ネタを紹介しました ▪ 未調査ですが他のDBでも使える所はありそうです、ご参考まで ▪ それでは皆様良いBigQueryライフを！ おわりに ※ unnest([...])でモンテカルロ法をやろうとして怒られた所