连续日期计算SQL

2022-10-19 16:33:27 文档大全网 [ 字体：小中大 ] [ 阅读： ]

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《连续日期计算SQL》，欢迎阅读！
连续,日期,计算,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