oraclesql需要根据模式在数据方面提供帮助

Question

我在oracle数据库中有如下示例数据：

set_no  set_eff_dt    set_term_dt
1000    1/1/2015    12/31/2016
1000    1/1/2017    10/31/2017
1000    11/1/2017   12/31/2018
1000    2/1/2019    10/31/2019
1000    11/1/2019   10/31/2020

我想要像下面一样的输出

1000    1/1/2015    12/31/2018
1000    2/1/2019    10/31/2020

让我解释一下模式和输出将如何实现

在第二行中，set_off_dt是set_term_dt +1的结果

第三行set_off_dt是第二行的set_term_dt +1的结果

在第四行中，set_eff_dt不是来自第三行的set_term_dt+1的结果，因此这里是分组中断可能是

在第5行中，set_eff_dt再次是来自第4行的set_term_dt+1的结果因此它将被折叠为第4行，如输出所示

在同样的模式中，我们有数千条记录，我们希望按照描述的逻辑进行折叠

what i have tried
SELECT SET_NO,SET_EFF_DT,
case when LEAD (SET_EFF_DT,1) OVER (ORDER BY SET_EFF_DT)-1 = set_trm_dt then 1 else 0 end flg
FROM xx_fl_test

如果新行中的SET_EFF_DT=set_trm_dt，我只能识别标志。。。但我仍然不明白如何处理这些数据。

Answer 1

这是一个缺口和孤岛问题。我将通过以下步骤计算分组变量来解决问题：

1. 确定组的起始位置。为此，我在之前的set_trm_dt和case逻辑上做了一个滞后，看看是否没有"连接">
2. 对标志进行累积求和，为每一行分配一个grp
3. 按grp聚合

代码如下：

select set_no, min(set_eff_dt), max(set_trm_dt)
from (select t.*,
sum(case when set_eff_dt > prev_set_trm_dt + 1 then 1 else 0 end) over (partition by set_no order by set_eff_dt) as grp
from (select t.*,
lag(set_trm_dt) over (partition by set_no order by set_eff_dt) as prev_set_trm_dt
from xx_fl_test t
) t
) t
group by set_no, grp;

Answer 2

考虑对生成的列进行累积求和，以生成需要两个CTE的分组变量：一个用于flg计算，另一个用于带有窗口函数的flg的累积求和。最后，通过cum_flg进行聚合(但有条件地为从1开始的第一个分组值加1(。

WITH sub AS
(SELECT SET_NO, SET_EFF_DT, SET_TRM_DT,
CASE WHEN LEAD (SET_EFF_DT,1) OVER (ORDER BY SET_EFF_DT)-1 = SET_TRM_DT
THEN 1 
ELSE 0 
END AS flg
FROM xx_fl_test),
calc AS
(SELECT SET_NO, SET_EFF_DT, SET_TRM_DT,
SUM (flg) OVER (PARTITION BY SET_NO ORDER BY SET_EFF_DT)  AS cum_flg
FROM sub)
SELECT SET_NO, 
MIN(SET_EFF_DT) AS MIN_SET_EFF_DT,
MAX(SET_TRM_DT) AS MAX_SET_TRM_DT
FROM calc
GROUP BY SET_NO,
CASE cum_flg
WHEN 1
THEN cum_flg + 1
END

Rextester演示