BigQuery SQLサンプル － １ヶ月分の日付レコード作成
やりたいこと
2020年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行目には終了日つまり月末の日付を指定すればよいことがわかります。
ややこしく感じるかもしれませんが、次のことをやっています。
- '2020-02-01’の1ヶ月後の日付を算出 → ’2020-03-01’
- '2020-03-01’の1日前の日付を算出 → ’2020-02-29’
5行目の UNNEST 関数で GENERATE_DATE_ARRAYで作成した配列形式の日付データを行データに展開します。
10行目でUNNEST関数で作成した行データに名前をつけてFROM句の完成です。
2行目は、DATE型のまま表示しています。
3行目は、書式指定し文字型で表示しています。
実行結果
| 行 | sample_date | sample_ymd |
|---|---|---|
| 1 | 2020-02-01 | 20200201 |
| 2 | 2020-02-02 | 20200202 |
| 3 | 2020-02-03 | 20200203 |
| 4 | 2020-02-04 | 20200204 |
| 5 | 2020-02-05 | 20200205 |
| 6 | 2020-02-06 | 20200206 |
| 7 | 2020-02-07 | 20200207 |
| 8 | 2020-02-08 | 20200208 |
| 9 | 2020-02-09 | 20200209 |
| 10 | 2020-02-10 | 20200210 |
| 11 | 2020-02-11 | 20200211 |
| 12 | 2020-02-12 | 20200212 |
| 13 | 2020-02-13 | 20200213 |
| 14 | 2020-02-14 | 20200214 |
| 15 | 2020-02-15 | 20200215 |
| 16 | 2020-02-16 | 20200216 |
| 17 | 2020-02-17 | 20200217 |
| 18 | 2020-02-18 | 20200218 |
| 19 | 2020-02-19 | 20200219 |
| 20 | 2020-02-20 | 20200220 |
| 21 | 2020-02-21 | 20200221 |
| 22 | 2020-02-22 | 20200222 |
| 23 | 2020-02-23 | 20200223 |
| 24 | 2020-02-24 | 20200224 |
| 25 | 2020-02-25 | 20200225 |
| 26 | 2020-02-26 | 20200226 |
| 27 | 2020-02-27 | 20200227 |
| 28 | 2020-02-28 | 20200228 |
| 29 | 2020-02-29 | 20200229 |