或者使用匹配识别：

with data(Person, Elig_for_Disc_A, Elig_for_Disc_B, Version_Start, Version_End) as
(
    select 'Bob', 'Y', 'Y', to_date('2022-01-01', 'yyyy-mm-dd'), to_date('2022-01-04', 'yyyy-mm-dd') from dual union all
    select 'Bob', 'Y', 'N', to_date('2022-01-05', 'yyyy-mm-dd'), to_date('2022-01-13', 'yyyy-mm-dd') from dual union all
    select 'Bob', 'N', 'N', to_date('2022-01-14', 'yyyy-mm-dd'), to_date('2022-01-22', 'yyyy-mm-dd') from dual union all
    select 'Bob', 'Y', 'N', to_date('2022-01-23', 'yyyy-mm-dd'), to_date('3000-12-31', 'yyyy-mm-dd') from dual -- union all
)
select Person, Elig_for_Disc_A, Version_Start, Version_End 
from data
match_recognize (
    partition by person, Elig_for_Disc_A 
    order by Version_Start, Version_End
    measures first(Version_Start) as Version_Start, max(Version_End) as Version_End
    pattern( merged* strt )
    define
        merged as max(Version_End)+1 >= next(Version_Start)
)
order by person, Version_Start;

Bob Y   01/01/22    13/01/22
    Bob N   14/01/22    22/01/22
    Bob Y   23/01/22    31/12/00

以下是可以在Oracle中使用的查询示例：

SELECT person, Elig_for_Disc_A, Version_Start, Version_End
FROM (
    SELECT person, Elig_for_Disc_A, Version_Start, Version_End,
           LAG(Elig_for_Disc_A) OVER (PARTITION BY person ORDER BY Version_Start) as prev_Elig_Disc_A
    FROM your_table
)
WHERE Elig_for_Disc_A != prev_Elig_Disc_A OR prev_Elig_Disc_A IS NULL

使用LAG或LEAD查找更改很容易。真实的的技巧是重构您的开始/结束日期以涵盖中间时间段。请尝试以下操作（未尝试，可能需要调试）：

SELECT person,
         elig_for_disc_a,
         new_version_start,
         NVL(LEAD(new_version_start) OVER (PARTITION BY person ORDER BY version_start),old_version_end) new_version_end
    FROM (SELECT person,
                 new_version_start,
                 MAX(version_end) old_version_end,
                 MAX(elig_for_disc_a) elig_for_disc_a
            FROM (SELECT x.*,
                         MAX(start_of_elig_change) OVER (PARTITION BY person ORDER BY version_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) new_version_start
                    FROM (SELECT x.*,
                                 DECODE(last_elig_a,elig_for_disc_a,NULL,version_start) start_of_elig_change
                            FROM (SELECT x.*,
                                         LAG(elig_for_disc_a) OVER (PARTITION BY person ORDER BY version_start) last_elig_a
                                    FROM your_table x) x) x) x
         GROUP BY person,
                  new_version_start)

这是一个典型的间隙和孤岛问题，在这种情况下，您需要标识同一分区中的孤岛（应该放在一起的记录）。我们可以按照以下三个步骤来查找孤岛之间的间隙：

• 对于每个人，当先前的Version_End = Version_Start-1天并且先前的Elig_for_Disc_A具有与当前相同的值时，不标记分区的改变
• 计算所述标志的运行和，以创建所述新分区
• 在每个新分区上的MIN（Version_Start）、MAX（Version_End）上聚合。

WITH cte AS (
    SELECT tab.*, 
           CASE WHEN LAG(Version_End)     OVER w = Version_Start -1
                 AND LAG(Elig_for_Disc_A) OVER w = Elig_for_Disc_A 
                THEN 0 ELSE 1 
           END AS change_part
    FROM tab
    WINDOW w AS (PARTITION BY Person ORDER BY Version_Start)
), cte2 AS (
    SELECT cte.*, 
           SUM(change_part) OVER(PARTITION BY Person ORDER BY Version_Start) AS parts
    FROM cte
)
SELECT Person, 
       Elig_for_Disc_A, 
       MIN(Version_Start) AS Version_Start,
       MAX(Version_End) AS Version_End
FROM cte2
GROUP BY Person, 
         Elig_for_Disc_A,
         Parts

输出：
| 人员|盘A的ELIG|版本_开始|版本_结束|
| - ------|- ------|- ------|- ------|
| 鲍勃|是|2022年1月1日|2022年1月13日|
| 鲍勃|数量|二十二年一月十四日|2022年1月22日|
| 鲍勃|是|2022年1月23日|二○ ○ ○年十二月三十一日|
查看Oracle演示here。

• • 假设**：您使用的是DATE数据类型来存储日期。

您可以使用1个case语句来获得正确的version_end日期，其余的1个带有LAG窗口函数的子查询将起作用-

SELECT PERSON, ELIG_FOR_DISC_A, ELIG_FOR_DISC_B, VERSION_START,
       upd_version_end version_end
  FROM (SELECT d.*, LEAD(Elig_for_Disc_A) OVER(PARTITION BY Person ORDER BY Version_Start) prev_Elig_Disc_A,
                    CASE WHEN Elig_for_Disc_A = LEAD(Elig_for_Disc_A) OVER(PARTITION BY Person ORDER BY Version_Start)
                              THEN LEAD(version_end) OVER(PARTITION BY Person ORDER BY Version_Start)
                         ELSE version_end
                    END UPD_VERSION_END
         FROM DATA d
       )
WHERE Elig_for_Disc_A != prev_Elig_Disc_A OR prev_Elig_Disc_A IS NULL;

Demo.