BigQueryで日付の欠損を補完したレポートを作成する
BigQueryで日付の欠損を補完したレポートを作成するときの方法です。過去何度かやったのだけどその度に調べたり思い出して対応してたのでここに残しておきます。
たとえば
テーブル1
| 日付 | 属性1 | 金額A |
|---|---|---|
| 2020-07-01 | A | 500 |
| 2020-07-03 | B | 500 |
テーブル2
| 日付 | 属性1 | 金額B |
|---|---|---|
| 2020-07-01 | B | 100 |
| 2020-07-02 | B | 500 |
という２つのテーブルから下記のような結果を出したいとします。（例としてはめちゃくちゃなんですが、やりたいことは察してください）
| 日付 | 属性1 | 金額A | 金額B |
|---|---|---|---|
| 2020-07-01 | A | 500 | 100 |
| 2020-07-01 | B | ||
| 2020-07-02 | A | ||
| 2020-07-02 | B | 500 | |
| 2020-07-03 | A | ||
| 2020-07-03 | B | 500 |
日付を作成する
GENERATE_DATE_ARRAY関数で日付の配列を作成することができます。UNNEST関数で展開することでテーブルとして利用することができます。
SELECT base_date FROM UNNEST(GENERATE_DATE_ARRAY( DATE("2020-07-01"), DATE("2020-07-03"))) AS base_date
属性のパターンを作成し、クロスジョイン
属性の取り得るパターンを事前に作成し、日付とクロスジョインします。（属性のパターンは、SELECTで作成するのではなく、マスターテーブルから取得するのが正しいです）
WITH date_series AS ( SELECT base_date FROM UNNEST(GENERATE_DATE_ARRAY( DATE("2020-07-01"), DATE("2020-07-03"))) AS base_date ), dimensions AS ( SELECT "A" as attr1 UNION ALL SELECT "B" ),base AS ( SELECT base_date, attr1 FROM date_series, dimensions ) SELECT * FROM base
このSQLで下記のようなテーブルを作成することができます
| date | attr1 |
|---|---|
| 2020-07-01 | A |
| 2020-07-01 | B |
| 2020-07-02 | A |
| 2020-07-02 | B |
| 2020-07-03 | A |
| 2020-07-03 | B |
データテーブルとjoinする
あとは、データが入っているテーブルとJOINすれば完了です。WITH句は先ほどと同じです。
WITH date_series AS ( SELECT base_date FROM UNNEST(GENERATE_DATE_ARRAY( DATE("2020-07-01"), DATE("2020-07-03"))) AS base_date ), dimensions AS ( SELECT "A" as attr1 UNION ALL SELECT "B" ),base AS ( SELECT base_date, attr1 FROM date_series, dimensions ) SELECT base.base_date, base.attr1, テーブル1.金額A テーブル2.金額B FROM base LEFT OUTER JOIN テーブル1 ON base.base_date = テーブル1.base_date AND base.attr1 = テーブル1.attr1 LEFT OUTER JOIN テーブル2 ON base.base_date = テーブル2.base_date AND base.attr1 = テーブル2.attr1
