help!!!hive sql求学生连续报读次数，两次报读时间在半年内算一次

Question

例如：学生A有三条记录，三个时间分别为2017-1-1,2017-3-1,2017-10-1，
那么连续报读次数为1；
学生B有三条记录：三个时间分别为2017-1-1,2017-3-1,2017-8-30，
那么连续报读次数为2；
求help!!

Answer 1

with t1 as
(
  select 'A' c1, '2017-1-1' c2 from dual union all
  select 'A' c1, '2017-10-1' c2 from dual union all
  select 'A' c1, '2017-3-1' c2 from dual union all  select 'B' c1, '2017-1-1' c2 from dual union all
  select 'B' c1, '2017-3-1' c2 from dual union all
  select 'B' c1, '2017-8-30' c2 from dual
)
, t2 as
(
  select row_number() over(order by c1, to_date(c2, 'yyyy-mm-dd')) rn, c1, to_date(c2, 'yyyy-mm-dd') c2 from t1
)
select a.c1, sum(case when b.c1 is not null and months_between(b.c2, a.c2)<=6 then 1 else 0 end)
from t2 a 
left join t2 b on a.c1=b.c1 and a.rn=b.rn-1 
group by a.c1

Answer 2

with t1 as
(
  select 'A' id1, date'2017-1-1' start_date from dual union all
  select 'A' id1, date'2017-8-1' start_date from dual union all
  select 'A' id1, date'2017-10-1' start_date from dual union all
  select 'A' id1, date'2017-11-1' start_date from dual union all
  select 'A' id1, date'2017-12-1' start_date from dual union all
  select 'A' id1, date'2018-7-1' start_date from dual union all
  select 'A' id1, date'2018-10-1' start_date from dual union all
  select 'A' id1, date'2018-11-1' start_date from dual union all 
  select 'B' id1, date'2017-12-1' start_date from dual union all
  select 'B' id1, date'2018-7-1' start_date from dual union all
  select 'B' id1, date'2018-10-1' start_date from dual union all
  select 'B' id1, date'2018-11-1' start_date from dual UNION ALL
  select 'B' id1, date'2019-10-1' start_date from dual union all
  select 'B' id1, date'2020-11-1' start_date from dual
 )
, t2 as
(select id1
         ,start_date start_day
         ,nvl(lead(start_date) over(partition by id1 order by start_date),date'2999-12-31') end_day
         ,row_number() over(partition by id1 order by start_date) rn
    from t1)
select
       id1
       ,max(num1)+1 --最大连续次数 
  from (
        SELECT ID1,TYPE1,COUNT(1) num1
          FROM (select rn-rn1 TYPE1
                       ,a1.*
                  from (
                        select a.*
                               ,row_number() over(partition by id1 order by start_day) rn1
                          from (select t2.id1
                                       ,t2.rn
                                       ,t2.start_day
                                       ,t2.end_day
                                       ,case when add_months(t2.start_day,6) > t2.end_day then 1 else 0 end lx_rn
                                  from t2
                                 where case when add_months(t2.start_day,6) > t2.end_day then 1 else 0 end > 0
                                ) a
                        ) a1
                  ) A2
        GROUP BY ID1,TYPE1)
group by id1
;
看看这种

Answer 3

Quote: 引用 6 楼 hi537638 的回复:

with t1 as
(
  select 'A' id1, date'2017-1-1' start_date from dual union all
  select 'A' id1, date'2017-8-1' start_date from dual union all
  select 'A' id1, date'2017-10-1' start_date from dual union all
  select 'A' id1, date'2017-11-1' start_date from dual union all
  select 'A' id1, date'2017-12-1' start_date from dual union all
  select 'A' id1, date'2018-7-1' start_date from dual union all
  select 'A' id1, date'2018-10-1' start_date from dual union all
  select 'A' id1, date'2018-11-1' start_date from dual union all 
  select 'B' id1, date'2017-12-1' start_date from dual union all
  select 'B' id1, date'2018-7-1' start_date from dual union all
  select 'B' id1, date'2018-10-1' start_date from dual union all
  select 'B' id1, date'2018-11-1' start_date from dual UNION ALL
  select 'B' id1, date'2019-10-1' start_date from dual union all
  select 'B' id1, date'2020-11-1' start_date from dual
 )
, t2 as
(select id1
         ,start_date start_day
         ,nvl(lead(start_date) over(partition by id1 order by start_date),date'2999-12-31') end_day
         ,row_number() over(partition by id1 order by start_date) rn
    from t1)
select
       id1
       ,max(num1) --最大连续次数 
  from (
        SELECT ID1,TYPE1,COUNT(1) num1
          FROM (select rn-rn1 TYPE1
                       ,a1.*
                  from (
                        select a.*
                               ,row_number() over(partition by id1 order by start_day) rn1
                          from (select t2.id1
                                       ,t2.rn
                                       ,t2.start_day
                                       ,t2.end_day
                                       ,case when add_months(t2.start_day,6) > t2.end_day then 1 else 0 end lx_rn
                                  from t2
                                 where case when add_months(t2.start_day,6) > t2.end_day then 1 else 0 end > 0
                                ) a
                        ) a1
                  ) A2
        GROUP BY ID1,TYPE1)
group by id1
;
不需要加1了的，看成连续次数了