テーブルから連続した日付を生成する方法について

Question

前提

team ごとに異なる開始日と終了日をもとに連続した日付を生成し、1つのテーブルとしたいです。

実現したいこと

インプットが以下のようなテーブルに対し

team | start_date | end_date
a | 2020-01-01 | 2020-01-03
b | 2020-01-05 | 2020-01-07
c | 2020-01-01 | 2020-01-05

以下のようなアウトプットを取得したいです。

team | date
a | 2020-01-01
a | 2020-01-02
a | 2020-01-03
b | 2020-01-05
b | 2020-01-06
b | 2020-01-07
c | 2020-01-01
c | 2020-01-02
c | 2020-01-03
c | 2020-01-04
c | 2020-01-05

試したこと

GENERATE_DATE_ARRAY を用いて、start_date と end_date に対して日付生成することができることは確認しました。
この内容を縦持ち的に生成して保持する場合の考え方に少し悩んでおります。
お知恵を拝借できますと幸いです。

Answer 1

以下のようなクエリで実現できるかと思います。

WITH RECURSIVE cte AS (
    SELECT team, start_date, end_date FROM X
  UNION ALL
    SELECT team, DATE_ADD(start_date, INTERVAL 1 DAY) , end_date
    FROM cte
    WHERE start_date < end_date
)
SELECT team, start_date FROM cte

Comments

[mi2]

ありがとうございます。
> A recursive CTE has reached the maximum number of iterations: 100.
適用してみたところ上記が出るのですが、recursive CTE を使用しない形もしくは上限は解除できるものでしょうか。

[neko_the_shadow]

2022-08-28 22:03追記

>適用してみたところ上記が出るのですが、recursive CTE を使用しない形もしくは上限は解除できるものでしょうか。

BigQueryは再帰SQLの上限を変更できなかった気がします…。

未検証ですが、以下のようなクエリではどうでしょうか?

WITH cte AS (
SELECT team, GENERATE_DATE_ARRAY(start_date, end_date) AS dates
FROM X
)
SELECT team, date
FROM cte, cte.dates AS date

[mi2]

ありがとうございます。想定通りの処理になります。
大変勉強になりました。

Answer 2

GENERATE_DATE_ARRAYで良いです。

Comments

[mi2]

コメントありがとうございます。
Inputの複数 team ごとに異なる期間を生成し、outputの形式に持って行くロジックに悩んでおりまして、もしご存知でしたらご教示いただけますと幸いです。