create table if not exists test_serialdate (
rq string comment '日期'
) stored as rcfile
;
insert into table test_serialdate
select '2019-01-01' as rq union all
select '2019-01-02' as rq union all
select '2019-01-05' as rq union all
select '2019-01-06' as rq union all
select '2019-01-08' as rq union all
select '2019-01-09' as rq union all
select '2019-01-10' as rq union all
select '2019-01-11' as rq union all
select '2019-01-17' as rq union all
select '2019-01-18' as rq ;
select b.gp,b.startdate,b.enddate,b.days,(case when b.gp = 0 then 0 else b.missingdays end)
from
(
select a.gp,min(a.rq) as startdate,max(a.rq) as enddate,
(max(a.id1)-min(a.id1)+1) as days,
max(datediff(a.rq,a.rq2)) as missingdays
from
(
select ta.rq,
datediff(ta.rq,'2019-01-01') as id1, --距离初始日期的天数
nvl(tb.id2,0) as id2, --比本日期小的天数
tc.rq2, --比本日期小的最大日期
nvl((datediff(ta.rq,'2019-01-01')-tb.id2),0) as gp --比本日期小的缺失天数
from test_serialdate ta
left join
( --记录中比本日期小的数据量
select t11.rq,count(1) as id2
from test_serialdate t11
inner join test_serialdate t12
where t11.rq > t12.rq
group by t11.rq
) tb
on ta.rq = tb.rq
left join
( --记录中比本日起小的最大日期
select t21.rq,max(t22.rq) as rq2
from test_serialdate t21
inner join test_serialdate t22
where t21.rq > t22.rq
group by t21.rq
) tc
on ta.rq = tc.rq
) a
group by a.gp
) b
;
