CODEWITHSUNDEEP
sqlgaps-and-islandsgreenplum
mtryingtocode
mtryingtocode

Reputation: 969

Tagging consecutive days

Supposedly I have data something like this:

ID,DATE

101,01jan2014

101,02jan2014

101,03jan2014

101,07jan2014

101,08jan2014

101,10jan2014

101,12jan2014

101,13jan2014

102,08jan2014

102,09jan2014

102,10jan2014

102,15jan2014

How could I efficiently code this in Greenplum SQL such that I can have a grouping of consecutive days similar to the one below:

ID,DATE,PERIOD

101,01jan2014,1

101,02jan2014,1

101,03jan2014,1

101,07jan2014,2

101,08jan2014,2

101,10jan2014,3

101,12jan2014,4

101,13jan2014,4

102,08jan2014,1

102,09jan2014,1

102,10jan2014,1

102,15jan2014,2

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this using row_number(). For a consecutive group, the difference between the date and the row_number() is a constant. Then, use dense_rank() to assign the period:

select id, date,
       dense_rank() over (partition by id order by grp) as period
from (select t.*,
             date - row_number() over (partition by id order by date) * 'interval 1 day'
      from table t
     ) t

Upvotes: 1

Related Questions