notebook

都内でWEB系エンジニアやってます。

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

f:id:swfz:20210308195004p:plain

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

  • 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_datadatesを生成

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

感想

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

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

参考