sql - Sql 查询以获取重叠日期间隔的外部

Question

我一直在尝试弄清楚如何为以下情况正确编写查询时遇到一些问题。我有一个表（TableA），其中 StartDate 和 EndDarte 作为 Date 列，以及一个 Type 列，它只能具有值 A 或 B。现在，当 B 型区间与 A 型区间重叠时，B 型将优先，类型 A 间隔将成为类型 B 间隔之外的任何内容。

例如：假设我们在表中有以下两行：

ID  Type   StartDate     EndDate
1.   A.    21-JAN-2021.  21-SEP-2021
2.   B.    23-APR-2021.  27-AUG-2021

预期的结果应该是：

ID  Type   StartDate     EndDate
1.   A.    21-JAN-2021.  22-APR-2021
3.   A.    28-AUG-2021.  21-SEP-2021
2.   B.    23-APR-2021.  27-AUG-2021

这是一个简单的例子，似乎可以很好地使用联合，但是当我在一个更大的 TypeA 间隔中有多个 B 型间隔时，我不知道如何将它们分开。

在这张图片中，我们有 A 类第一个区间：AB和两个 B 类区间重叠GH和IJ 所以结果应该是：

|ID.| Type|    StartDate.|   EndDate|
|---|-----|--------------|----------|
|1. |   A.|      A.      |     G.   | 
|2. |   B.|      G.      |     H    |
|3. |   A.|      H+1     |    I-1.  |
|4. |   B.|     I        |     J.   | 

有谁知道如何解决这个问题？谢谢

标签： sqlsnowflake-cloud-data-platform

Answer 1

查找“A”区间与“B”区间之间的间隙的交集

select t.*
from (
   select *, lead(StartDate) over(order by StartDate) nxt
   from tbl
   where type='B') tb
outer apply (
 select tb.ID, tb.Type, tb.StartDate, tb.EndDate
 union all
 select ta.ID, ta.Type, 
     case when ta.StartDate > tb.EndDate then ta.StartDate else tb.EndDate + 1 end,
     case when ta.EndDate > tb.nxt then tb.nxt - 1 else ta.EndDate end 
 from tbl ta
 where ta.type='A' and ta.StartDate < coalesce (tb.nxt, ta.StartDate + 1) and tb.EndDate < ta.EndDate
) t;

或者，您可以使用 JOIN 找到上述交叉点，并使用 UNION 添加 B 间隔。还添加了第一个 A 在第一个 B 之前开始的情况。

select *
from tbl
where type='B'

UNION ALL

select ta.ID, ta.Type, 
     case when ta.StartDate > coalesce(tb.EndDate, ta.StartDate -1) then ta.StartDate else tb.EndDate + 1 end StartDate,
     case when ta.EndDate > tb.nxt then tb.nxt - 1 else ta.EndDate end EndDate
from (
   -- before first B
   select 0 id, null Enddate, min(startdate) nxt
   from tbl
   where type='B'
   
   union all
   
   select id, Enddate, lead(StartDate) over(order by StartDate) nxt
   from tbl
   where type='B') tb
join tbl ta
  on ta.type='A' and ta.StartDate < coalesce (tb.nxt, ta.StartDate + 1) and coalesce(tb.EndDate, ta.EndDate - 1) < ta.EndDate
order by StartDate