时间戳

首页 > 解决方案 > 根据参数 -Oracle 重新启动 sum(over) 函数

oracle - 根据参数 -Oracle 重新启动 sum(over) 函数

问题描述

我试图做的是使用 sum over 函数，使其重置为 0 并在遇到参数值时重新开始。我附上的是一些带有开始和结束日期的基本会员数据。现在，如果会员期限之间的间隔超过 5 天，则它会再次重新启动计数器。我尝试使用延迟函数，但 oracle 不允许窗口函数。

数据：

ID  BEG_DATE    END_DATE         MONTHS DAYS_IN_COVERAGE_BREAK  tot_membership_months   
123 01-JAN-15   31-DEC-15        30.3                          30.3 first term
123 01-JAN-16   28-FEB-17        35.3   1                      65.6 --Adds first & 2nd term
123 01-JUN-17   31-DEC-17        17.8   93                     17.8 **--restarts- lapse in coverage**
123 01-JAN-18   30-MAR-19        37.8   1                      55.6 --Adds from restart time

询问：

With TL as (select '123' as id, to_Date('01JAN2015') as Beg_date,to_date('31DEC2015') as end_Date from dual union
            select '123' as id, to_Date('01JAN2016') as Beg_date,to_date('28FEB2017') as end_Date from dual union
            select '123' as id, to_Date('01JUN2017') as Beg_date,to_date('31DEC2017') as end_Date from dual union
            select '123' as id, to_Date('01JAN2018') as Beg_date,to_date('30MAR2019') as end_Date from dual)
            
select TL.*, round((END_DATE-BEG_DATE) /12,1) as MONTHS,
            BEG_DATE-LAG(END_DATE) OVER (PARTITION BY ID ORDER BY BEG_DATE) as DAYS_IN_COVERAGE_BREAK
            --case when BEG_DATE-LAG(END_DATE) OVER (PARTITION BY ID ORDER BY BEG_DATE)<5
           -- then sum(round((END_DATE-BEG_DATE) /12,1) over (partition by mrn order by beg_date)) else round((END_DATE-BEG_DATE) /12,1) end as member_cum_months
from TL;

任何见解表示赞赏。谢谢

标签： oracledatetimesumgaps-and-islandsdate-arithmetic

解决方案

这是一个间隙和岛屿问题，每行开始一个新岛屿，与前一行有 5 天以上的差距。

这是一个使用窗口函数的选项：lag()给我们前一个end_date，然后我们使用窗口总和，对于超过 5 天的每个间隔递增来定义组。

select id, beg_date, end_date, 
    end_date - min(beg_date) over(partition by id, grp order by beg_date) total_membership_days
from (
    select t.*,
        sum(case when beg_date > lag_end_date + interval '5' day then 1 else 0 end) over(partition by id order by beg_date) grp
    from (
        select t.*, 
            lag(end_date) over(partition by id order by beg_date) lag_end_date
        from tl t
    ) t
) t

如果您希望结果为几个月而不是几天，那么：

select id, beg_date, end_date, 
    months_between(end_date, min(beg_date) over(partition by id, grp order by beg_date)) total_membership_months
from (
    select t.*,
        sum(case when beg_date > lag_end_date + interval '5' day then 1 else 0 end) over(partition by id order by beg_date) grp
    from (
        select t.*, 
            lag(end_date) over(partition by id order by beg_date) lag_end_date
        from tl t
    ) t
) t

推荐阅读