😇

BigQueryでGROUP BYを使って集計すると該当しないレコードが歯抜けする場合の対処

2021/07/19に公開

BigQueryで日毎に集計したい時は以下のようにすれば、取得できます。

SELECT DATE(TIMESTAMP(created_at)) AS time, COUNT(name) as COUNT
FROM sample.users
GROUP BY created_at
ORDER BY COUNT DESC;

ただこれだとユーザーが作成されていない日の場合は歯抜けしてしまいます。

2021-07-05 3
2021-07-04 2
2021-07-01 1

GROUP BYの条件に該当しない場合は初期値を設定するようにしたいです。

これを実現する方法としては以下の2つがあります。

  1. フィールドの生成とCROSS JOINを利用する
  2. プログラム側で日付とマージする

バッチなどでBigQueryを叩く場合は2の方が手軽かもしれません。

ただ1の書き方も一度覚えてしまえば便利なので、
こちらも今後は使っていこうと思います。

参考にした記事

Bigqueryの日時に関係する関数全部試してみた ①Date編 - Qiita https://qiita.com/hogeta_/items/1416135863a023a09127

BigQueryによるデータ分析のための前処理Tips - ZOZO Technologies TECH BLOG https://techblog.zozo.com/entry/bigquery_data_reduction#クロス集計の欠落を埋める

Discussion