如何在SQL中选择重叠的日期范围

Question

我有一个包含以下列的表格：

sID、start_date和end_date。

其中一些值如下：

1   1995-07-28  2003-07-20 
1   2003-07-21  2010-05-04 
1   2010-05-03  2010-05-03 
2   1960-01-01  2011-03-01 
2   2011-03-02  2012-03-13 
2   2012-03-12  2012-10-21 
2   2012-10-22  2012-11-08 
3   2003-07-23  2010-05-02

我只想要第二行和第三行，因为它们是重叠的日期范围。

我尝试了这个方法，但它无法去掉第一行。不确定我哪里出错了？

select a.sID from table a
inner join table b 
on a.sID = b.sID
and ((b.start_date between a.start_date and a.end_date)
and (b.end_date between a.start_date and b.end_date ))
order by end_date desc

我正在尝试在SQL Server中完成以下操作。

- koala

Answer 1

相对高效地完成这件事情的一种方式是

WITH T1
     AS (SELECT *,
                MAX(end_date) OVER (PARTITION BY sID ORDER BY start_date) AS max_end_date_so_far
         FROM   YourTable),
     T2
     AS (SELECT *,
                range_start = IIF(start_date <= LAG(max_end_date_so_far) OVER (PARTITION BY sID ORDER BY start_date), 0, 1),
                next_range_start = IIF(LEAD(start_date) OVER (PARTITION BY sID ORDER BY start_date) <= max_end_date_so_far, 0, 1)
         FROM   T1)
SELECT SId,
       start_date,
       end_date
FROM   T2
WHERE  0 IN ( range_start, next_range_start ); 

如果您在(sID, start_date) INCLUDE (end_date)上创建了索引，则可以通过单个有序扫描来完成工作。

- Martin Smith

---

哇，完美地运行了！谢谢！不过有没有不使用分区函数/更简单的查询方法呢？ - koala

@koala - 这是针对性能而非语法简洁进行优化的。 - Martin Smith

Answer 2

您的逻辑不完全正确，虽然它在您的示例数据上几乎可以工作。 它失败的具体原因是因为 between 包括端点，所以任何给定行都与自身匹配。 也就是说，该逻辑仍然不正确，因为它无法捕获以下情况：

 a-------------a
      b----b

这里是正确的逻辑：

select a.*
from table a
where exists (select 1
              from table b
              where a.sid = b.sid and
                    a.start_date < b.end_date and
                    a.end_date > b.start_date and
                    (a.start_date <> b.start_date or  -- filter out the record itself
                     a.end_date <> b.end_date
                    )
             )
order by a.end_date;

重叠时间段（或任何范围）的规则是，当期限1在期限2结束之前开始并且期限1在期限2开始之后结束时，期限1与期限2重叠。令人高兴的是，此目的不需要或不适用于使用between。（强烈反对在日期/时间操作数中使用between。）

我应该指出，这个版本在一个时间段结束和另一个时间段开始的同一天不被视为重叠。可以通过将<和>更改为<=和>=来轻松调整。 这里有一个SQL Fiddle。

- Gordon Linoff

---

我的表格有许多其他的sID。我尝试运行你的查询并在where sID=1处进行过滤，但仍然得到相同的结果。 - koala

@koala . . . 没错。你没有一个唯一标识每一行的ID，所以通常会有 and a.id <> b.id。我根据开始和结束日期添加了类似的逻辑。 - Gordon Linoff

现在当我不筛选sID = 1时，它只会给我这两行而不是任何其他 sID 的重叠日期范围 - koala

@koala...答案中没有过滤到sID = 1。你的查询语句可能还有其他问题。我提供了一个SQL Fiddle来展示它是可以工作的。 - Gordon Linoff

你说得对，现在可以了！对此我很抱歉，谢谢！请问您能否解释一下 "a.start_date <> b.start_date or a.end_date <> b.end_date" 的逻辑？ - koala