HiveSQL找出连续日期及连续的天数

参考：https://www.cnblogs.com/Joetao/p/3842242.html

参考关键代码：

select 本期起始日期=min(rq),本期终止日期=max(rq),
       持续天数=max(id1)-min(id1)+1,
       距上一期天数=case a.id1-a.id2 when -1 then 0 else max(datediff(d,rq2,rq)) end 
from 
(
 select id1 = datediff(d,'2010-01-01',rq),id2=(select count(1) from tmptable where rq <= a.rq),
        rq2=(select max(rq) from tmptable where rq < a.rq),* 
 from tmptable a
) a
group by a.id1-a.id2

测试数据：

use xxx;
drop table test_serialdate;
create table if not exists test_serialdate (
  rq  string comment '日期'
) stored as rcfile
;

insert into table test_serialdate 
select '2019-01-01' as rq from dual union all 
select '2019-01-02' as rq from dual union all 
select '2019-01-05' as rq from dual union all 
select '2019-01-06' as rq from dual union all 
select '2019-01-08' as rq from dual union all 
select '2019-01-09' as rq from dual union all 
select '2019-01-10' as rq from dual union all 
select '2019-01-11' as rq from dual union all 
select '2019-01-17' as rq from dual union all 
select '2019-01-18' as rq from dual ;

 

代码

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
;

后记：大牛解决这个问题的核心在于缺失天数，大写的服。

本文地址：https://blog.csdn.net/sdsky1987/article/details/85915920