https://swfz.hatenablog.com/entry/2021/03/08/194805

f:id:swfz:20210308195004p:plain

スクリプティングを使ってみたくて題材探してみようと思ったがこのケースならSQL組み合わせるだけで良かった

WITH
  # 歯抜けサンプルデータの用意
  sample_data AS(
    SELECT
      *
    FROM
      UNNEST(ARRAY<STRUCT<d DATE, item STRING, sales INT64>> [
         ("2020-10-04", "hoge", 100),
         ("2020-10-19", "fuga", 50),
         ("2020-10-25", "hoge", 80)
      ])
  ),
  # 連続した日付データの用意
  dates AS (
    SELECT
      d
    FROM
      UNNEST(GENERATE_DATE_ARRAY('2020-10-01','2020-10-31', INTERVAL 1 DAY)) AS d)
SELECT
  dates.d,
  IFNULL(sample_data.sales, 0) AS sales
FROM
  dates
LEFT JOIN sample_data
ON
  (dates.d = sample_data.d)

結果

d	min
2020-10-01	0
2020-10-02	0
2020-10-03	0
2020-10-04	100
2020-10-05	0
2020-10-06	0
2020-10-07	0
2020-10-08	0
2020-10-09	0
2020-10-10	0
2020-10-11	0
2020-10-12	0
2020-10-13	0
2020-10-14	0
2020-10-15	0
2020-10-16	0
2020-10-17	0
2020-10-18	0
2020-10-19	50
2020-10-20	0
2020-10-21	0
2020-10-22	0
2020-10-23	0
2020-10-24	0
2020-10-25	80
2020-10-26	0
2020-10-27	0
2020-10-28	0
2020-10-29	0
2020-10-30	0
2020-10-31	0

流れ

WITH句でsample_dataとdatesを生成
- sample_data
  - ARRAY+STRUCTでサンプルデータを記入
  - UNNESTで1行1データに変換
- dates
  - GENERATE_DATE_ARRAYで連続した日付の配列を生成
  - UNNESTで1行1日に変換
生成した2つのデータをLEFT JOINで結合

感想

MySQLでやる場合かなり面倒だなーと思っていた記憶があったのでこんなに簡単にできるのか!とびっくりした

特にUNNEST + ARRAYは記事書くときやサンプル実装など使える場面多そうなので覚えておきたい

BigQueryで連続した日付のデータを生成して集計データと突き合わせる

流れ

感想

参考