持续次数计算逻辑

 

select
     t3.day_time
    ,t3.cnt
    ,t3.rn1
    ,t3.rn2
    ,t3.diff
    ,row_number() over(partition by t3.diff order by t3.day_time) as rn
from (
    select
         t2.day_time
        ,t2.cnt
        ,t2.rn1
        ,t2.rn2
        ,rn2 - rn1 as diff
    from (
        select
             t1.day_time
            ,t1.cnt
            ,row_number() over(partition by t1.cnt order by t1.day_time) as rn1
            ,row_number() over(order by t1.day_time)                     as rn2
        from (
            select '2019-03-18 19:01:17' as day_time, 100 as cnt union all
            select '2019-03-18 19:01:19' as day_time, 100 as cnt union all
            select '2019-03-18 19:01:21' as day_time, 100 as cnt union all
            select '2019-03-18 19:01:22' as day_time, 5   as cnt union all
            select '2019-03-18 19:01:22' as day_time, 1   as cnt union all
            select '2019-03-18 19:01:24' as day_time, 100 as cnt union all
            select '2019-03-18 19:01:23' as day_time, 100 as cnt
        ) t1
        order by t1.day_time
    ) t2
) t3
;