CODEWITHSUNDEEP
postgresqlgaps-and-islands
Anindya Mukherjea
Anindya Mukherjea

Reputation: 13

Recursively dividing a list of dates into groups

I have a list of start dates as below -

start dates sorted in descending order

The start dates are always sorted in descending order. I am looking for a postgresql query that can give the following output - start dates with groups

Basically i am trying to create groups of dates from the given list such that each date in a group is within 61 days from the date at the top of the corresponding group. For example - in the output,

P.S. I am new to postgresql and stackoverflow. Any pointers will be helpfull

Upvotes: 1

Views: 80

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

Your sample data does not match your sample output.

Your calculations in your sample output are wrong since this counts backwards and March and October both have 31 days.

To recurse properly you need to assign row numbers using dense_rank():

with recursive num as (
  select row_number() over (order by start_date desc) as rn,
         start_date
    from dateslist
),

Then you create groups and find gaps by carrying anchor values forward as you recurse. Since you have the start_date information you can calculate the offset within groups at the same time:

find_gaps as (
  select rn as anchor, start_date as anchor_date, rn, start_date, 0 as group_offset
    from num 
   where rn = 1
  union all
  select case 
           when f.anchor_date - n.start_date > 61 then n.rn
           else f.anchor
         end,
         case 
           when f.anchor_date - n.start_date > 61 then n.start_date
           else f.anchor_date
         end,
         n.rn, n.start_date, 
         case 
           when f.anchor_date - n.start_date > 61 then n.start_date
           else f.anchor_date
         end - n.start_date
    from find_gaps f
    join num n on n.rn = f.rn + 1
)

The final query selects the columns you want for the output and applies a group number.

select start_date, 
       dense_rank() over (order by anchor) as group_number,
       group_offset
  from find_gaps
 order by start_date desc;

Working Fiddle Demo

Upvotes: 2

Related Questions