【SQL】範囲日付のカラムを対象年月に変換したい

Question

実現したいこと

該当テーブルのカラムとして、開始日付、終了日付を保持しており、
その2カラムを対象年月に変換したいと考えております。
※対象年月に集計後、同一テーブルの金額を計算する予定ですが、省略しております。

発生している問題

期間を含む対象年月ごとに集計したいのですが、横持ちしている日付を、
対象年月に変換する良い方法が見つからず困っております。

検討事項

• with句を使った再帰クエリと結合すれば良いのですが、

BiqQueryでは利用できないため、他の方法で検討しております。

データイメージ

対象テーブルの定義は以下のとおりです。

フィールド名	タイプ	モード
id	INTEGER	NULLABLE
開始日付	TIMESTAMP	NULLABLE
終了日付	TIMESTAMP	NULLABLE

参照データは以下のとおりです。

id	開始日付	終了日付
A	2021-01-01	2021-02-28
B	2020-12-01	2021-02-28

集計結果イメージは以下のとおりです。

id	対象年月
A	2021-01-01
A	2021-02-01
B	2020-12-01
B	2021-01-01
B	2020-02-01

補足情報（FW/ツールのバージョンなど）

• GCP BigQuery

Answer 1

連続した日付を生成したいのであればGENERATE_DATE_ARRAYやGENERATE_TIMESTAMP_ARRAYを利用することが多いと思います。

なお「対象テーブル」を「集計結果イメージ」に変換する方法ですが、生成した年月マスタ(以下ではSとしている)と「対象テーブル」をCROSS JOINしてしまうのが手っ取り早そうです。

DECLARE min_date, max_date DATE;

SET (min_date, max_date) = (
    SELECT AS STRUCT
        EXTRACT(DATE FROM TIMESTAMP_TRUNC(MIN(開始日付), MONTH)),
        EXTRACT(DATE FROM TIMESTAMP_TRUNC(MIN(終了日付), MONTH))
    FROM 対象テーブル
);

WITH S AS (
  SELECT TIMESTAMP(target_date) AS target_timestamp
  FROM UNNEST(GENERATE_DATE_ARRAY(min_date, max_date, INTERVAL 1 MONTH)) AS target_date
)
SELECT id, target_timestamp 
FROM 対象テーブル, S
WHERE target_timestamp BETWEEN 開始日付 AND 終了日付;

Comments

[engineer_takuto]

解決しました！ありがとうございます！

Answer 2

with句を使った再帰クエリと結合すれば良いのですが、BiqQueryでは利用できないため

GENERATE_ARRAY()とUNNEST()を組み合わせれば同じことが出来ます。
BigQueryで無から連番のレコードを生成する方法

Comments

[engineer_takuto]

ご回答ありがとうございます！解決しました！