BigQuery SQLサンプル - 1ヶ月分の日付レコード作成

2020年2月18日BigQueryBigQuery SQLサンプル

やりたいこと

2020年1月の日付レコードを動的に作成したい

月次帳票表示用のデータを作成する際などに、1ヶ月分の日付レコードが必要になることがよくありますよね。

例えば、こんなデータです。

日付
2020-01-01
2020-01-02
2020-01-03
  :
2020-01-30
2020-01-31

これを、わざわざプログラムからワークテーブルに登録して・・・などとしなくてもSQLで動的に作成する子ができます。

SQLサンプル

サンプルは2020年2月の日付レコードを作成するSQLです。

SELECT
  sample_date
, FORMAT_DATE('%Y%m%d', sample_date) AS sample_ymd
FROM 
  UNNEST(
    GENERATE_DATE_ARRAY(
      '2020-02-01'
    , DATE_ADD(DATE_ADD(DATE('2020-02-01'), INTERVAL 1 MONTH), INTERVAL -1 DAY)
    )
  ) AS sample_date

サンプルクエリについて説明します。

6行目の GENERATE_DATE_ARRAY 関数はBigQueryの関数で開始日と終了日を指定すると配列形式で日付データを作成する関数です。

7行目の '2020-02-01’で開始日(月初)を指定します。

8行目には終了日つまり月末の日付を指定すればよいことがわかります。

ややこしく感じるかもしれませんが、次のことをやっています。

  1. '2020-02-01’の1ヶ月後の日付を算出 → ’2020-03-01’
  2. '2020-03-01’の1日前の日付を算出 → ’2020-02-29’

5行目の UNNEST 関数で GENERATE_DATE_ARRAYで作成した配列形式の日付データを行データに展開します。

10行目でUNNEST関数で作成した行データに名前をつけてFROM句の完成です。

2行目は、DATE型のまま表示しています。

3行目は、書式指定し文字型で表示しています。

実行結果

sample_datesample_ymd
12020-02-0120200201
22020-02-0220200202
32020-02-0320200203
42020-02-0420200204
52020-02-0520200205
62020-02-0620200206
72020-02-0720200207
82020-02-0820200208
92020-02-0920200209
102020-02-1020200210
112020-02-1120200211
122020-02-1220200212
132020-02-1320200213
142020-02-1420200214
152020-02-1520200215
162020-02-1620200216
172020-02-1720200217
182020-02-1820200218
192020-02-1920200219
202020-02-2020200220
212020-02-2120200221
222020-02-2220200222
232020-02-2320200223
242020-02-2420200224
252020-02-2520200225
262020-02-2620200226
272020-02-2720200227
282020-02-2820200228
292020-02-2920200229