记一则Oracle业务需求---获取连续日期

需求背景

获取连续的日期，比如1月31、2月1、2月2 这样连续的，跨月跨年都算作连续，如下图所示，只取标红框部分。

数据搭建

with ltz as

 (select level as aid, trunc(sysdate - level) as bdate

    from dual

  connect by level <= 120),

hehe as

 (select aid, bdate

    from ltz

   where aid in (1,2,3,5,78,7,8,9,88,100,101,102,104,106,107,108,109))

select * from hehe

思路

思路就是先关联出一个完整的表，外关联，然后获取上一条和下一条数据，取这两条都不为空且外关联后date不为空的数据

SQL

with ltz as

 (select level as aid, trunc(sysdate - level) as bdate

    from dual

  connect by level <= 120),

hehe as

 (select aid, bdate

    from ltz

   where aid in (1,2,3,5,78,7,8,9,88,100,101,102,104,106,107,108,109))

select aid, hbdate

  from (select h.aid,

               lag(h.bdate, 1, date '1970-01-01') over(order by l.bdate) as l, --get last data

               h.bdate as hbdate,

               lead(h.bdate, 1, date '1970-01-01') over(order by l.bdate) as bl,--get next data

               l.bdate as lbdate

          from hehe h, ltz l

         where h.bdate(+) = l.bdate

         order by 3)

 where not (l is null and bl is null)

   and hbdate is not null

结果