Rob Van Wijk has written also about the method recently.
Here is just another example of it.
Something happening on random times. Need to know sequences of dates. Need to get a grouping information on rows, if there exist rows on yesterday or tomorrow.
with dates as (
select TIMESTAMP'2014-04-15 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-15 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-16 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-17 03:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 01:00:00' dt from dual union all
select TIMESTAMP'2014-04-19 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 02:00:00' dt from dual union all
select TIMESTAMP'2014-04-21 03:00:00' dt from dual
)
select dt,TRUNC(dt)-DENSE_RANK()over(order by trunc(dt)) grp
from dates
;
2014-04-15 01:00:00 2014-04-14
2014-04-15 02:00:00 2014-04-14
2014-04-15 03:00:00 2014-04-14
2014-04-16 01:00:00 2014-04-14
2014-04-17 02:00:00 2014-04-14
2014-04-17 03:00:00 2014-04-14
2014-04-19 01:00:00 2014-04-15
2014-04-19 02:00:00 2014-04-15
2014-04-21 02:00:00 2014-04-16
2014-04-21 03:00:00 2014-04-16
