BigQueryのクエリで連続した日付の配列を作成する

下記のトランザクションテーブルではconutry毎にdateの種類がバラバラです。
2023-02-10〜2023-02-14の4日間の内、dateがないレコードにdateとuu(0)を設定して、レコードを作成したいと思います

dataset.table

countrydateuu
JP2023-02-1010
JP2023-02-115
JP2023-02-123
JP2023-02-136
JP2023-02-143
US2023-02-105
US2023-02-131
US2023-02-142
CN2023-02-105
CN2023-02-132

GENERATE_DATE_ARRAYで日付の配列を作成して、配列を使用して0で穴埋めをします

 
SELECT
 IFNULL(b.country, a.country) AS country,
 IFNULL(b.date, a.date) AS date,
 IFNULL(b.uu, 0) AS uu
FROM (
  SELECT
    country,
    date
  FROM
    `dataset.table`
 ),
 UNNEST(GENERATE_DATE_ARRAY('2023-02-10','2023-02-14', INTERVAL 1 DAY)) AS date
) a
LEFT JOIN
 `dataset.table` b
ON
 a.country = b.country
 AND a.date = b.date
ORDER BY
  country,
  date

クエリ結果

countrydateuu
JP2023-02-1010
JP2023-02-115
JP2023-02-123
JP2023-02-136
JP2023-02-143
US2023-02-105
US2023-02-110
US2023-02-120
US2023-02-131
US2023-02-142
CN2023-02-105
CN2023-02-110
CN2023-02-120
CN2023-02-132
CN2023-02-140

コメント

このブログの人気の投稿

BigQuery テーブルの有効期限を設定する

Cloud Schedulerは必ず実行されるわけではない

ChromebookでAndroidアプリをインストールする方法