Crezitの@yabebe_tです。
最近ひたすらBQ書いてるのでわからなかったことのメモを残していくことにしました。
課題
貸付データをサーバーから日次で集計しつつ、銀行口座データとのダブルチェックができるようにしたい。日付の欠損があった場合に、集計がずれてしまわないように、欠損日には0をいれてクエリーする。
解決方法
シンプルに2ステップで解決しました。
色々調べているとSQLで@numなど変数を使ってやっている方もいました
- GENERATE_DATE_ARRAYで日付データを生成
- 集計したテーブルをLEFT JOIN。
1. GENERATE_DATE_ARRAYで日付データを生成
2020年3月1日から今日+1日までの日付データを生成。
WITH date_series as (
select publish_date
from unnest(GENERATE_DATE_ARRAY( DATE("2020-03-01"), DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY))) as publish_date
)
GENERATE_DATE_ARRAY
GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
+--------------------------------------------------+
| example |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+
Date_ADD
DATE_ADD(date_expression, INTERVAL int64_expression date_part)
SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;
+--------------------+
| five_days_later |
+--------------------+
| 2008-12-30 |
+--------------------+