BigQueryで開始終了日付の範囲を1日毎のレコードに展開する方法

FROM-TOの日付を持つレコードをどうやって１日ずつのレコードに展開すればいいんだろう

データの分析や処理に便利なBigQuery(BQ)。

大規模データの処理に優れており、テーブルに保存した大きなデータでも短時間で集計することができる。

BQでは他のDBと同じように日付データを扱うことができる。

日付データを扱えると

このような開始日、終了日を指定されたデータを

１日ずつのデータに展開したくなることがある。

ではどうやって１日ごとのデータに展開すれば良いのか。

そこで今回はBigQueryで開始終了日付の範囲を1日毎のレコードに展開する方法について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

BigQueryで開始終了日付の範囲を1日毎のレコードに展開する方法

BigQueryで開始終了日付の範囲を1日毎のレコードに展開する方法は、少し考え方を変える必要がある。

範囲指定されたデータを１日ごとに展開するのではない。

ポイントはコレ。

では実際にやってみよう。

１日ごとの離散データを作成してから範囲指定で絞り込む

まず１日ごとの連続した離散データを作る。

SQLは以下の通り。

WITH
  t1 AS (
  SELECT
    date_seq
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2021-01-31')) AS date_seq
  )
SELECT
  *
FROM
  t1

すると↓のような結果が得られる。

連続した日付データを作るのに GENERATE_DATE_ARRAY() を使っており、得られた配列を UNNEST で１行ずつのデータにバラしている。

あとは連続した日付データを開始日終了日のデータで絞り込めば良い。

WITH
  t1 AS (
  SELECT
    date_seq
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2021-01-31')) AS date_seq
  ),
  t2 AS (
  SELECT
    DATE("2021-01-10") AS date_from,
    DATE("2021-01-20") AS date_to
  )
SELECT
  *
FROM
  t1,
  t2
WHERE
  t1.date_seq BETWEEN t2.date_from
  AND t2.date_to

処理の流れは異なるが、結果としては「開始終了日の範囲を１日ずつのレコードに展開した」ことになる。

まとめ

今回はBigQueryで開始終了日付の範囲を1日毎のレコードに展開する方法について紹介した。

開始終了日付の範囲を1日ずつのデータにするには、「展開する」という考えを逆転させて以下のように考える。

GENERATE_DATE_ARRAY() と UNNEST で１行ずつ日付データを広めの範囲で作ってから、開始日終了日の範囲だけ between で切り出せば良い。

展開するのではなく絞り込むのか。わかってみれば簡単だね。

単に手法を知っているかどうかですね。

他にもBigQueryのテクニックに関する記事もあるので、興味があれば見てみて欲しい。