CODEWITHSUNDEEP
sqlgoogle-bigquery
Evan Kaeding
Evan Kaeding

Reputation: 192

Translate date ranges to date sequences in BigQuery

I have table 1 that looks like the following:

+-----------+------------+------------+
| campaign  | start_date |  end_date  |
+-----------+------------+------------+
| campaign1 | 2020-01-01 | 2020-01-03 |
| campaign2 | 2020-01-04 | 2020-01-06 |
| ...       | ...        | ...        |
+-----------+------------+------------+

I'd like to create table 2 that looks like this:

+-----------+------------+
| campaign  |    date    |
+-----------+------------+
| campaign1 | 2020-01-01 |
| campaign1 | 2020-01-02 |
| campaign1 | 2020-01-03 |
| campaign2 | 2020-01-04 |
| campaign2 | 2020-01-05 |
| campaign2 | 2020-01-06 |
| ...       | ...        |
+-----------+------------+

Keep in mind that table 1 is going to have n number of rows and will be added to on a regular basis. I'd like to schedule the creation of table 2 using a scheduled query.

I've played around with GENERATE_DATE_ARRAY() in conjunction with CROSS JOIN UNNEST. I haven't been able to find a way to do this elegantly. Any suggestions?

Upvotes: 1

Views: 83

Answers (3)

rtenha
rtenha

Reputation: 3616

One other approach is to create a 'calendar' CTE (or a table in your database) and then cross join and filter:

with cal as ( select dte from unnest(generate_date_array('2000-01-01','2050-01-01', interval 1 day)) dte),
tbl as (
  select 'campaign1' as campaign, cast('2020-01-01' as date) as start_date, cast('2020-01-03' as date) as end_date union all
  select 'campaign2','2020-01-04','2020-01-06'
)
select campaign, dte from tbl
cross join cal
where dte between start_date and end_date
order by campaign, dte

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

[How to] Translate date ranges to date sequences ...

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'campaign1' campaign, DATE '2020-01-01' start_date, DATE '2020-01-03' end_date UNION ALL
  SELECT 'campaign2', '2020-01-04', '2020-01-06' 
)
SELECT campaign, day
FROM `project.dataset.table`,
UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) day
-- ORDER BY campaign, day

with result 

Row campaign    day  
1   campaign1   2020-01-01   
2   campaign1   2020-01-02   
3   campaign1   2020-01-03   
4   campaign2   2020-01-04   
5   campaign2   2020-01-05   
6   campaign2   2020-01-06

Update - use below in your real use case (above was just example with dummy data from your question for you to test) 

#standardSQL
SELECT campaign, day
FROM `project.dataset.table`,
UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) day

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use generate_date_array() and unnest():

select campaign, dte
from (select 'campaign1' as campaign,  date('2020-01-01') as start_date, date('2020-01-03') as end_date
     ) t cross join
     unnest(generate_date_array(t.start_date, t.end_date, interval 1 day)) dte;

Upvotes: 0

Related Questions