DB

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.

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


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

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.


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.

If you felt this article is useful, please share.

にほんブログ村 IT技術ブログへ

-DB
-

Translate »

© 2022 ITips