Oracle中如何从指定时间段排除多个非重叠日期区间？

问题背景与需求 #

在Oracle数据库中，我们有如下样本数据：

With
RawTable as (
  Select 'A' ColA,'AA' ColB,
    To_Date('2023-02-10','yyyy-mm-dd') START_DATE,To_Date('2023-02-23','yyyy-mm-dd') END_DATE,
    To_Date('2023-02-11','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-13','yyyy-mm-dd') EXCLUDE_EDATE 
    from dual union all
  Select 'A' ColA,'AA' ColB,
    To_Date('2023-02-10','yyyy-mm-dd') START_DATE,To_Date('2023-02-23','yyyy-mm-dd') END_DATE,
    To_Date('2023-02-15','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-18','yyyy-mm-dd') EXCLUDE_EDATE
    from dual union all          
  Select 'A' ColA,'AA' ColB,
    To_Date('2023-02-10','yyyy-mm-dd') START_DATE,To_Date('2023-02-23','yyyy-mm-dd') END_DATE,
    To_Date('2023-02-20','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-22','yyyy-mm-dd') EXCLUDE_EDATE
    from dual union all          
  Select 'B' ColA,'BB' ColB,
    To_Date('2023-02-01','yyyy-mm-dd') START_DATE,To_Date('2023-02-20','yyyy-mm-dd') END_DATE,
    To_Date('2023-01-10','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-13','yyyy-mm-dd') EXCLUDE_EDATE
    from dual union all
  Select 'B' ColA,'BB' ColB,
    To_Date('2023-02-01','yyyy-mm-dd') START_DATE,To_Date('2023-02-20','yyyy-mm-dd') END_DATE,
    To_Date('2023-02-15','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-27','yyyy-mm-dd') ExcludeEDate
    from dual union all
  Select 'B' ColA,'BB' ColB,
    To_Date('2023-02-01','yyyy-mm-dd') START_DATE,To_Date('2023-02-20','yyyy-mm-dd') END_DATE,
    To_Date('2023-01-15','yyyy-mm-dd') Exclude_SDate,To_Date('2023-01-19','yyyy-mm-dd') EXCLUDE_EDATE
    from dual union all
  Select 'C' ColA,'CC' ColB,
    To_Date('2023-02-09','yyyy-mm-dd') START_DATE,To_Date('2023-02-12','yyyy-mm-dd') END_DATE,
    To_Date('2023-02-08','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-13','yyyy-mm-dd') EXCLUDE_EDATE
    from dual union all
  Select 'C' ColA,'CC' ColB,
    To_Date('2023-02-09','yyyy-mm-dd') START_DATE,To_Date('2023-02-12','yyyy-mm-dd') END_DATE,
    To_Date('2023-01-28','yyyy-mm-dd') Exclude_SDate,To_Date('2023-02-02','yyyy-mm-dd') EXCLUDE_EDATE
    from dual
          )
          
Select * from RawTable

核心需求：

• 按ColA和ColB分组
• 从每组统一的START_DATE至END_DATE时间段中，排除多个不重叠的Exclude_SDate至EXCLUDE_EDATE区间
• 若排除区间完全覆盖主时间段，则剔除该分组

问题解决过程 #

最初尝试用CASE WHEN语句处理，但按ColA和ColB分组生成正确日期区间时遇到阻碍。

参考思路后，发现可以将主时间段内的EXCLUDE_EDATE作为新的起始日期，下一条记录的Exclude_SDate作为新的结束日期，但这种方法会遗漏主时间段起始到第一条有效排除区间开始前的时段。

最终通过结合LAG函数判断分组首行，补充该缺失时段，得到最终SQL查询：

Select COLA, COLB,START_DATE_2 Start_Date,END_DATE_2 END_DATE
from (
      Select COLA, COLB, START_DATE, END_DATE, Exclude_SDate, EXCLUDE_EDATE,
                CASE WHEN EXCLUDE_EDATE BETWEEN START_DATE And END_DATE 
                     THEN EXCLUDE_EDATE END "START_DATE_2",
                CASE WHEN EXCLUDE_EDATE BETWEEN START_DATE And END_DATE THEN
                    CASE WHEN LEAD(COLA) OVER(Order By COLA, START_DATE) = COLA And 
                              LEAD(COLB) OVER(Order By COLA, COLB, START_DATE) = COLB
                         THEN LEAD(Exclude_SDate) OVER(Order By COLA, COLB, START_DATE)
                    ELSE END_DATE
                    END
                END "END_DATE_2"
      From rawtable 
      -- 处理主时间段到第一条有效排除区间开始前的缺失时段
      Union all  
      Select R.COLA, R.COLB, R.START_DATE, R.END_DATE, R.Exclude_SDate, R.EXCLUDE_EDATE,
            Case When Lag(R.ColA,1,null) over(partition by R.ColA Order by R.ColA,R.START_DATE) is null And
                      Lag(R.ColB,1,null) over(partition by R.ColA,R.ColB Order by R.ColA,R.ColB,R.START_DATE) is null And
                      R.Exclude_SDate between R.Start_Date and R.End_Date
                 then R.Start_Date 
                 Else Null 
            End "START_DATE_2", 
            Case When Lag(R.ColA,1,null) over(partition by R.ColA Order by R.ColA,R.START_DATE) is null And
                      Lag(R.ColB,1,null) over(partition by R.ColA,R.ColB Order by R.ColA,R.ColB,R.START_DATE) is null And
                      R.Exclude_SDate between R.Start_Date and R.End_Date
                  then R.Exclude_SDate 
                 Else Null
            End "END_DATE_2"
      From RawTable R
      ) 
Where Start_Date_2 is not null
Order by ColA,ColB,Start_Date_2

内容的提问来源于stack exchange，提问作者jou yungcheng