
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.
date_from | date_to |
---|---|
2021-11-01 | 2021-11-05 |
Once we get this record, we want to convert it to each date like below.
date |
---|
2021-11-01 |
2021-11-02 |
2021-11-03 |
2021-11-04 |
2021-11-05 |
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

Advantage to read
You can understand "How to decompose "from-to term" to each date record in BigQuery". Then you don't have to concern about decomposing "from-to" record.
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.
Point
Generate each date rocords and filter it by "from-to".
Let's try.
Generate each date rocords and filter it by "from-to"
First, generate each date rocords.
SQL is below.
SQL
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
Row | date_seq |
1 | 2021-01-01 |
2 | 2021-01-02 |
3 | 2021-01-03 |
4 | 2021-01-04 |
5 | 2021-01-05 |
6 | 2021-01-06 |
7 | 2021-01-07 |
8 | 2021-01-08 |
9 | 2021-01-09 |
10 | 2021-01-10 |
11 | 2021-01-11 |
12 | 2021-01-12 |
13 | 2021-01-13 |
14 | 2021-01-14 |
15 | 2021-01-15 |
16 | 2021-01-16 |
17 | 2021-01-17 |
18 | 2021-01-18 |
19 | 2021-01-19 |
20 | 2021-01-20 |
21 | 2021-01-21 |
22 | 2021-01-22 |
23 | 2021-01-23 |
24 | 2021-01-24 |
25 | 2021-01-25 |
26 | 2021-01-26 |
27 | 2021-01-27 |
28 | 2021-01-28 |
29 | 2021-01-29 |
30 | 2021-01-30 |
31 | 2021-01-31 |
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.
SQL
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
Row | date_seq | date_from | date_to |
1 | 2021-01-10 | 2021-01-10 | 2021-01-20 |
2 | 2021-01-11 | 2021-01-10 | 2021-01-20 |
3 | 2021-01-12 | 2021-01-10 | 2021-01-20 |
4 | 2021-01-13 | 2021-01-10 | 2021-01-20 |
5 | 2021-01-14 | 2021-01-10 | 2021-01-20 |
6 | 2021-01-15 | 2021-01-10 | 2021-01-20 |
7 | 2021-01-16 | 2021-01-10 | 2021-01-20 |
8 | 2021-01-17 | 2021-01-10 | 2021-01-20 |
9 | 2021-01-18 | 2021-01-10 | 2021-01-20 |
10 | 2021-01-19 | 2021-01-10 | 2021-01-20 |
11 | 2021-01-20 | 2021-01-10 | 2021-01-20 |
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.
Point
Generate each date rocords and filter it by "from-to".
With using GENERATE_DATE_ARRAY()
and UNNEST
, make date records.
Then filter it by between
.


There are some other articles about BigQuey.
If you interested in them, please read them.
Read more