DB

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

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

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

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


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

日付データを扱えると

開始日 終了日
2021-11-01 2021-11-05

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

日付
2021-11-01
2021-11-02
2021-11-03
2021-11-04
2021-11-05

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

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

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

この記事を書いている人


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

記事を読むメリット

BigQueryで開始終了日付の範囲を1日毎のレコードに展開する方法がわかり、データ展開で困ることがなくなる。


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

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

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

ポイントはコレ。

ココがポイント

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


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



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

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

SQLは以下の通り。

SQL

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


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

結果

Row date_seq
1 2021-01-01
2 2021-01-02
3 2021-01-03
4 2021-01-04
5 2021-01-05
6 2021-01-06
7 2021-01-07
8 2021-01-08
9 2021-01-09
10 2021-01-10
11 2021-01-11
12 2021-01-12
13 2021-01-13
14 2021-01-14
15 2021-01-15
16 2021-01-16
17 2021-01-17
18 2021-01-18
19 2021-01-19
20 2021-01-20
21 2021-01-21
22 2021-01-22
23 2021-01-23
24 2021-01-24
25 2021-01-25
26 2021-01-26
27 2021-01-27
28 2021-01-28
29 2021-01-29
30 2021-01-30
31 2021-01-31


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


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

SQL

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

結果

Row date_seq date_from date_to
1 2021-01-10 2021-01-10 2021-01-20
2 2021-01-11 2021-01-10 2021-01-20
3 2021-01-12 2021-01-10 2021-01-20
4 2021-01-13 2021-01-10 2021-01-20
5 2021-01-14 2021-01-10 2021-01-20
6 2021-01-15 2021-01-10 2021-01-20
7 2021-01-16 2021-01-10 2021-01-20
8 2021-01-17 2021-01-10 2021-01-20
9 2021-01-18 2021-01-10 2021-01-20
10 2021-01-19 2021-01-10 2021-01-20
11 2021-01-20 2021-01-10 2021-01-20


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



まとめ

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

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

ココがポイント

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


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


展開するのではなく絞り込むのか。わかってみれば簡単だね。
単に手法を知っているかどうかですね。


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

ITipsと同じようなブログを作る方法

ブログに興味がありますか?

もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

ランキング参加中

にほんブログ村 IT技術ブログへ

他にもブログやSNSで紹介してくれると励みになります。

はてブのコメントで酷評されると泣きます(´;ω;`)

-DB
-

Translate »

© 2022 ITips