CODEWITHSUNDEEP
sqlarraysgoogle-bigqueryunnest
jbev
jbev

Reputation: 83

How to split the GENERATE_DATE_ARRAY in a date for a single row in Google Bigquery to create a date view?

I'm attempting to create a date view using the MIN and MAX of dates from another table in Google Bigquery. I've come across GENERATE_DATE_ARRAY and have been able to create the dates I'm after in a single array. However, splitting them out to a single date per row has been the hard part.

I've attempted to use UNNEST to do this but when I run the code it only gives me the first result so I'm missing something here.

This is the code I've used to generate the array, but I'm stuck on getting it to split out:

SELECT GENERATE_DATE_ARRAY( 
              MIN(CAST(journaldate AS DATE)), 
              MAX(CAST(journaldate AS DATE))
            )
          AS Period
FROM
dataset.table

Upvotes: 4

Views: 11777

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Another version is: 

#standardSQL
SELECT period FROM UNNEST(
  GENERATE_DATE_ARRAY(
    (SELECT MIN(CAST(journaldate AS DATE)) FROM `project.dataset.table`),
    (SELECT MAX(CAST(journaldate AS DATE)) FROM `project.dataset.table`)
  )
) period

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I think you want:

SELECT Period
FROM (SELECT MIN(CAST(journaldate AS DATE)) as min_date,
             MAX(CAST(journaldate AS DATE)) as max_date
      FROM dataset.table
     ) t JOIN
     UNNEST(GENERATE_DATE_ARRAY(t.min_date, t.max_date)) period

Upvotes: 9

Related Questions