Determining continuity length of a sequence of dates in postgres

Question

Is there a straightforward way to determine the length of continuity in a sequence of dates in Postgres?

For simplicity, lets say I have a table of dates, like so:

2017-01-01
2017-01-02
2017-01-03
2017-01-04
2017-01-06
2017-01-08
2017-01-09

For each date, I want to know its count in a continuous sequence of dates. So I would want:

2017-01-01 1
2017-01-02 2
2017-01-03 3
2017-01-04 4
2017-01-06 1
2017-01-08 1
2017-01-09 2

I think I'll need to use a procedure and loop through a cursor to keep count, but was wondering if there was perhaps a more clever approach? I spent some time looking at windowing functions, but didn't see anything to quite fit the bill.

Answer 1

You can identify the sequences by subtracting a sequential number. Constant values then represent a sequence. After this, your enumeration is an application of row_number():

select t.date,
       row_number() over (partition by date - seqnum * interval '1 day' order by date) as col2
from (select t.*,
             row_number() over (order by date) as seqnum
      from t
     ) t;