SELECT
DISTINCT -- DISTINCT指定することで配列データからレコードを増幅する
id,
start_date,
end_date,
target_month
FROM
dataset.table
CROSS JOIN UNNEST(
GENERATE_DATE_ARRAY(
start_date, -- この日から
end_date, -- この日まで
INTERVAL 1 MONTH -- １ヶ月毎に区切る
)
) target_month -- 上記で作成した項目に名称をつける
上記ではstart_dateからend_dateの期間を１ヶ月毎に区切って、レコードを増幅します。
| id | start_date | end_date |
|---|---|---|
| 123 | 2020-01-01 | 2020-06-01 |
の場合は、
target_monthには、以下のような配列項目が設定されます。
| id | start_date | end_date | target_month |
|---|---|---|---|
| 123 | 2020-01-01 | 2020-06-01 | '2020-01-01' '2020-02-01' '2020-03-01' '2020-04-01' '2020-05-01' '2020-06-01' |
これをSELECT DISTINCTすると、同じ年月日ごとに１レコードずつが作成されるため、以下のようにレコードが作成されます。
| id | start_date | end_date | target_month |
|---|---|---|---|
| 123 | 2020-01-01 | 2020-06-01 | 2020-01-01 |
| 123 | 2020-01-01 | 2020-06-01 | 2020-02-01 |
| 123 | 2020-01-01 | 2020-06-01 | 2020-03-01 |
| 123 | 2020-01-01 | 2020-06-01 | 2020-04-01 |
| 123 | 2020-01-01 | 2020-06-01 | 2020-05-01 |
| 123 | 2020-01-01 | 2020-06-01 | 2020-06-01 |
用途としては、id単位で1レコードにまとめられているデータがあった場合に、それを各月毎に分解して、月額単位で値を集計したりする場合に活用可能です。
