How to decompose "from-to term" record by each date record in BigQuery

How can I decompose "from-to term" record by each date record ?

BigQuery(BQ) is very useful for data analysis or processing.
It is good to handle huge data.
It returns summary result in short time.

BQ can handle date data.
Then sometimes we handle "from-to" record like below.

Once we get this record, we would like to convert it to each date records like below.

So how can we decompose "from-to term" record by each date record ?

So today I introduce about "How to decompose "from-to term" record by each date record in BigQuery".

How to decompose "from-to term" record by each date record in BigQuery

In order to decompose "from-to term" to each date record in BigQuery, we should change our mind.

Instead of "decompose", we do this.

Generate date records and filter it by "from-to".

Let's try it.

Generate date records and filter it by "from-to"

First, generate each date records.

SQL is below.

WITH
  t1 AS (
  SELECT
    date_seq
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2021-01-31')) AS date_seq
  )
SELECT
  *
FROM
  t1

Then we get this result.

Result

In order to make continuous date records, we can use GENERATE_DATE_ARRAY() function.

Then use UNNEST to decompose array to each record.

Array functions  |  BigQuery  |  Google Cloud

Next, filter it with using "from-to" term.

WITH
  t1 AS (
  SELECT
    date_seq
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2021-01-31')) AS date_seq
  ),
  t2 AS (
  SELECT
    DATE("2021-01-10") AS date_from,
    DATE("2021-01-20") AS date_to
  )
SELECT
  *
FROM
  t1,
  t2
WHERE
  t1.date_seq BETWEEN t2.date_from
  AND t2.date_to

Result

Now we decomposed "from-to term" record by each date record.

Conclusion

Today I explained about "How to decompose "from-to term" record by each date record in BigQuery".

In order to each date record, we need to change "decompose" mind and think like below.

Generate date records and filter it by "from-to".

With using GENERATE_DATE_ARRAY() and UNNEST, make date records.

Then filter it by between clause.

Instead of decomposing, we filter it. It is simple.

Yes. It is easy trick.