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

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

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

BQ can handle date data.

Then we would like to handle "from-to" record like below.

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

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

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

Author

Mid-carieer engineer (AI, system). Good at Python and SQL.

How to decompose "from-to term" to 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.

Let's try.

Generate each date rocords and filter it by "from-to"

First, generate each date rocords.

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.

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

Then use UNNEST to decompose array to each record.

So filter it withusing "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

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

Conclusion

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

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

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

Then filter it by between.

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

Yes. It is easy trick.

There are some other articles about BigQuey.

If you interested in them, please read them.