欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-13 08:04:47
参考: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......

参考: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