持续天数算法

 持续天数算法

 

select
    name
    ,day
    ,status
    ,case when status = 'n' then 0 else row_number() over(partition by name,date_add(day,rn) order by day) end as rn
from (
    select
        name
        ,day
        ,status
        ,row_number() over(partition by name,case when status = 'y' then 1 else 0 end order by day desc) as rn
    from (
        select 'a' as name, '2017-01-01' as day, 'y' as status union all
        select 'a' as name, '2017-01-02' as day, 'y' as status union all
        select 'a' as name, '2017-01-03' as day, 'y' as status union all
        select 'a' as name, '2017-01-04' as day, 'y' as status union all
        select 'a' as name, '2017-01-05' as day, 'y' as status union all
        select 'a' as name, '2017-01-06' as day, 'y' as status union all
        select 'a' as name, '2017-01-07' as day, 'n' as status union all
        select 'a' as name, '2017-01-08' as day, 'y' as status union all
        select 'a' as name, '2017-01-09' as day, 'y' as status union all
        select 'b' as name, '2017-01-10' as day, 'n' as status union all
        select 'b' as name, '2017-01-11' as day, 'n' as status union all
        select 'b' as name, '2017-01-12' as day, 'n' as status union all
        select 'b' as name, '2017-01-13' as day, 'y' as status union all
        select 'b' as name, '2017-01-14' as day, 'y' as status union all
        select 'b' as name, '2017-01-15' as day, 'y' as status union all
        select 'b' as name, '2017-01-16' as day, 'y' as status union all
        select 'b' as name, '2017-01-17' as day, 'n' as status union all
        select 'b' as name, '2017-01-18' as day, 'y' as status
    ) t1
) t2
order by name,day
;

 

new

select
     t2.uid
    ,datediff(max(t2.dt),min(t2.dt)) as diff
    ,collect_set(dt) as days
from (
    select
         t1.uid
        ,t1.dt
        ,date_add(t1.dt,row_number() over(partition by t1.uid order by t1.dt desc)) as day
    from (
        select '1043736' as uid, '2014-08-15' as dt union all
        select '1043736' as uid, '2014-08-14' as dt union all
        select '1043736' as uid, '2014-08-13' as dt union all
        select '1043736' as uid, '2014-08-12' as dt union all
        select '1043736' as uid, '2014-08-18' as dt union all
        select '1043736' as uid, '2014-08-21' as dt union all
        select '1043736' as uid, '2014-08-20' as dt union all
        select '1043844' as uid, '2014-08-14' as dt union all
        select '1044090' as uid, '2014-08-12' as dt union all
        select '1044090' as uid, '2014-08-11' as dt union all
        select '1044090' as uid, '2014-08-16' as dt union all
        select '1044090' as uid, '2014-08-15' as dt union all
        select '1044090' as uid, '2014-08-17' as dt union all
        select '1044090' as uid, '2014-08-21' as dt union all
        select '1044264' as uid, '2014-08-10' as dt union all
        select '1044264' as uid, '2014-08-15' as dt union all
        select '1044264' as uid, '2014-08-14' as dt union all
        select '1044264' as uid, '2014-08-13' as dt union all
        select '1044264' as uid, '2014-08-12' as dt union all
        select '1044264' as uid, '2014-08-21' as dt union all
        select '1044264' as uid, '2014-08-20' as dt union all
        select '1044264' as uid, '2014-08-22' as dt union all
        select '1044264' as uid, '2014-08-19' as dt union all
        select '1044264' as uid, '2014-08-17' as dt union all
        select '1044264' as uid, '2014-08-18' as dt
    ) t1
) t2
group by t2.uid,t2.day
;

 

-- 持续月份
select
    name
    ,day
    ,status
    ,case when status = 'n' then 0 else row_number() over(partition by name,add_months(day,rn) order by day) end as rn
from (
    select
        name
        ,day
        ,status
        ,row_number() over(partition by name,case when status = 'y' then 1 else 0 end order by day desc) as rn
    from (
        select 'a' as name, '2017-01-01' as day, 'y' as status union all
        select 'a' as name, '2017-02-01' as day, 'y' as status union all
        select 'a' as name, '2017-03-01' as day, 'y' as status union all
        select 'a' as name, '2017-04-01' as day, 'y' as status union all
        select 'a' as name, '2017-05-01' as day, 'y' as status union all
        select 'a' as name, '2017-06-01' as day, 'y' as status union all
        select 'a' as name, '2017-07-01' as day, 'n' as status union all
        select 'a' as name, '2017-08-01' as day, 'y' as status union all
        select 'a' as name, '2017-09-01' as day, 'y' as status union all
        select 'b' as name, '2017-10-01' as day, 'n' as status union all
        select 'b' as name, '2017-11-01' as day, 'n' as status union all
        select 'b' as name, '2017-12-01' as day, 'n' as status union all
        select 'b' as name, '2018-01-01' as day, 'y' as status union all
        select 'b' as name, '2018-02-01' as day, 'y' as status union all
        select 'b' as name, '2018-03-01' as day, 'y' as status union all
        select 'b' as name, '2018-04-01' as day, 'y' as status union all
        select 'b' as name, '2018-05-01' as day, 'n' as status union all
        select 'b' as name, '2018-06-01' as day, 'y' as status
    ) t1
) t2
order by name,day
;