CODEWITHSUNDEEP
sqlamazon-athenapresto
andrew
andrew

Reputation: 4089

UNNEST an interval of all dates without grouping by column

I want to UNNEST an interval of all dates within the min and max of a date column without grouping by any other column.

Building off the answer in this post, I can get close, but instead of getting the date range grouped by each job_name, I want to generate the total date range across all job_name values. So each job_name should be exploded to have 3-rows for 2021-08-20 through 2021-08-22.

WITH 
    dataset AS (
      SELECT * 
      FROM 
        ( VALUES      
            ('A', DATE '2021-08-21'), ('A', DATE '2021-08-22'),
            ('B', DATE '2021-08-20'), ('B', DATE '2021-08-21')
        ) AS d (job_name, run_date)),
    
    nested_dates AS (
        select job_name, sequence(min(run_date), max(run_date), interval '1' day) seq
        from dataset
        group by job_name)

SELECT job_name, dates
FROM nested_dates
CROSS JOIN UNNEST(seq) AS t(dates)

Current output:

#   job_name    dates
1   A   2021-08-21 00:00:00.000
2   A   2021-08-22 00:00:00.000
3   B   2021-08-20 00:00:00.000
4   B   2021-08-21 00:00:00.000

Desired output:

#   job_name    dates
1   A   2021-08-20 00:00:00.000
2   A   2021-08-21 00:00:00.000
3   A   2021-08-22 00:00:00.000
3   B   2021-08-20 00:00:00.000
4   B   2021-08-21 00:00:00.000
5   B   2021-08-22 00:00:00.000

Upvotes: 0

Views: 306

Answers (1)

Guru Stron
Guru Stron

Reputation: 143243

One approach can be using windows functions and distinct select:

-- sample data
WITH dataset(job_name, run_date) AS (
VALUES ('A', DATE '2021-08-21'), 
    ('A', DATE '2021-08-22'), 
    ('B', DATE '2021-08-20'), 
    ('B', DATE '2021-08-21')), 

nested_dates AS (
    select distinct job_name, max (run_date) over() max_run_date, min (run_date) over() min_run_date
    from dataset)

-- query 
select job_name, dates
from nested_dates,
unnest (sequence(min_run_date, max_run_date, interval '1' day)) AS t(dates)
order by job_name, dates;

Output:

job_name dates
A 2021-08-20
A 2021-08-21
A 2021-08-22
B 2021-08-20
B 2021-08-21
B 2021-08-22

Upvotes: 1

Related Questions