选择最大差距oracle之后的最小日期

Question

我有一个成员的数据，如下

EFF_DT-Term_dt
1/1/13-7/31/14
1/1/15-3/31/15
5/1/15-5/31/15
6/1/15-12/31/15
1/1/16-12/31/16

这里有两个间隔-在7/31/14和3/31/15之后。我想选择行5/1/15-5/31/15，因为它是最大间隔之后的最小日期。我试着用

select ( FIRST_VALUE(EFF_DT) OVER (PARTITION BY MemberID ORDER BY FLAG DESC) AS CUR_EFF_DT)
from
(
select   EFF_DT,     
CASE WHEN LAG(TERM_DT, 1) OVER (PARTITION BY MemberID ORDER BY TERM_DT) = EFF_DT - 1 THEN 0
           ELSE sequence.nextval
END AS FLAG
from effective_dates_table). 

这给出了正确的结果，但我不想使用序列，有没有其他最简单的方法呢？

Answer 1

这里有一个方法..。使用解析LAG()函数计算差异，然后使用group by member_id并使用聚合LAST()函数。

注意:在term_dt和下面的eff_dt之间可能有多对行具有相同的最大间距。如果发生这种情况，您必须弄清楚应该选择哪一行。下面的解决方案选择最早出现的(如果发生这种情况)。如果想要最新的匹配项，请将MIN更改为MAX。如果你想要别的东西，只要说出需求就行了。

with
     inputs ( member_id, eff_dt, term_dt ) as (
       select 101, to_date('1/1/13', 'mm/dd/yy'), to_date('7/31/14' , 'mm/dd/yy') from dual union all
       select 101, to_date('1/1/15', 'mm/dd/yy'), to_date('3/31/15' , 'mm/dd/yy') from dual union all
       select 101, to_date('5/1/15', 'mm/dd/yy'), to_date('5/31/15' , 'mm/dd/yy') from dual union all
       select 101, to_date('6/1/15', 'mm/dd/yy'), to_date('12/31/15', 'mm/dd/yy') from dual union all
       select 101, to_date('1/1/16', 'mm/dd/yy'), to_date('12/31/16', 'mm/dd/yy') from dual
     )
-- End of simulated inputs (for testing only, not part of the solution).
-- Use your actual table and column names in the SQL query below.
select member_id,
       min(eff_dt)  keep (dense_rank last order by diff nulls first) as eff_dt,
       min(term_dt) keep (dense_rank last order by diff nulls first) as term_dt
from   (
         select member_id, eff_dt, term_dt,
                eff_dt - lag(term_dt) over (partition by member_id order by eff_dt) as diff
         from   inputs
       )
group by member_id
;

MEMBER_ID  EFF_DT               TERM_DT           
---------  -------------------  -------------------
      101  2015-01-01 00:00:00  2015-03-31 00:00:00