连续日期计算SQL

如何用一条SQL实现：取到每段连续日期的起始终止日期、持续天数以及起始日期距上一期终止日期的天数。

一、构造SQL（其他数据库语法微调）

WITH tmp_table AS

(SELECT '800XXX' as user_id,to_date('20180101','yyyymmdd') as rq FROM DUAL union SELECT '800XXX' as user_id,to_date('20180102' ,'yyyymmdd') as rq FROM DUAL union

SELECT '800XXX' as user_id,to_date('20180103' ,'yyyymmdd') as rq FROM DUAL union SELECT '800XXX' as user_id,to_date('20180108' ,'yyyymmdd') as rq FROM DUAL union

SELECT '800XXX' as user_id,to_date('20180109' ,'yyyymmdd') as rq FROM DUAL union SELECT '800XXX' as user_id,to_date('20180110' ,'yyyymmdd') as rq FROM DUAL union

SELECT '800XXX' as user_id,to_date('20180111' ,'yyyymmdd') as rq FROM DUAL union SELECT '800XXX' as user_id,to_date('20180112' ,'yyyymmdd') as rq FROM DUAL union

SELECT '800XXX' as user_id,to_date('20180120' ,'yyyymmdd') as rq FROM DUAL union SELECT '800XXX' as user_id,to_date('20180121' ,'yyyymmdd') as rq FROM DUAL)

select b.user_id,min(b.rq) as 本期起始日期, max(b.rq) as 本期终止日期,max(b.range_days)-min(b.range_days)+1 as 持续天数,

(case when b.range_days-b.continue_days=-1 then 0 else max(b.rq-b.last_day) end) as 距上一期天数

from (select a.user_id,a.rq,(a.rq-min(a.rq)over(partition by a.user_id)) as range_days,

(select count(1) from tmp_table where user_id=a.user_id and rq <= a.rq) as continue_days,

(select max(rq) from tmp_table where user_id=a.user_id and rq < a.rq) as last_day

from tmp_table a) b

group by b.user_id,(b.range_days-b.continue_days)

order by b.user_id,min(b.rq);

二、SQL运行结果

user_id | 本期起始日期 | 本期终止日期 | 持续天数 | 距上一期天数

---------+--------------+--------------+----------+--------------

800XXX | 2018-01-01 | 2018-01-03 | 3 | 0

800XXX | 2018-01-08 | 2018-01-12 | 5 | 5

800XXX | 2018-01-20 | 2018-01-21 | 2 | 8

本文来源：https://www.wddqw.com/doc/81f4e03ecf2f0066f5335a8102d276a20129608c.html