计算两个日期之间缺失的日期范围和重叠的日期范围。

Question

我有一个包含以下事件日期（dd/MM/yyyy）的数据库：

eventId     startDate     endDate
1           02/05/2009    10/05/2009
2           08/05/2009    12/05/2009
3           10/05/2009    12/05/2009
4           21/05/2009    21/05/2009
5           25/05/2009    NULL
6           01/06/2009    03/06/2009

这些事件都有开始和结束日期（时间不重要），NULL的endDate表示事件仍在进行中。

我想要确定的是，两个任意日期之间存在a)没有事件以及b)事件重叠的日期范围。

因此，对于输入日期范围01/04/2009 - 30/06/2009，我希望得到以下结果：

没有事件: 01/04/2009 - 01/05/2009
重叠: 08/05/2009 - 10/05/2009
重叠: 10/05/2009 - 12/05/2009
没有事件: 13/05/2009 - 20/05/2009
没有事件: 22/05/2009 - 24/05/2009
重叠: 01/06/2009 - 03/06/2009

请注意，两个相邻的重叠范围会被视为一个结果。

有谁能帮我提供一个SQL算法来生成这个结果集吗？

编辑：目标平台数据库是SQL Server 2005。日期被记录为10/05/2009 00:00:00，这意味着事件在10/5/2009 00:00:00和10/5/2009 23:59:59之间结束。对于开始日期也是如此。因此，输入日期范围也可以解释为01/04/2009 00:00:00 - 30/06/2009 23:59:59。

- Liam

---

您将在哪些数据库平台上运行此查询？ - Ed Harper

Answer 1

这是将交叉时间跨度平铺在SQL Server中的函数的小变化:

• 平铺时间跨度: SQL Server

这是SQL Server中基于光标的方法比基于集合的方法更快的罕见情况之一:

CREATE FUNCTION mytable(@p_from DATETIME, @p_till DATETIME)
RETURNS @t TABLE
        (
        q_type VARCHAR(20) NOT NULL,
        q_start DATETIME NOT NULL,
        q_end DATETIME NOT NULL
        )
AS
BEGIN
        DECLARE @qs DATETIME
        DECLARE @qe DATETIME
        DECLARE @ms DATETIME
        DECLARE @me DATETIME
        DECLARE cr_span CURSOR FAST_FORWARD
        FOR
        SELECT  startDate, endDate
        FROM    mytable
        WHERE   startDate BETWEEN @p_from AND @p_till
        ORDER BY
                startDate 
        OPEN    cr_span
        FETCH   NEXT
        FROM    cr_span
        INTO    @qs, @qe
        SET @ms = @qs
        SET @me = @qe
        WHILE @@FETCH_STATUS = 0
        BEGIN
                FETCH   NEXT
                FROM    cr_span
                INTO    @qs, @qe
                IF @qs > @me
                BEGIN
                        INSERT
                        INTO    @t
                        VALUES ('overlap', @ms, @me)
                        INSERT
                        INTO    @t
                        VALUES ('gap', @me, @qs)
                        SET @ms = @qs
                END
                SET @me = CASE WHEN @qe > @me THEN @qe ELSE @me END
        END
        IF @ms IS NOT NULL
        BEGIN
                INSERT
                INTO    @t
                VALUES  (@ms, @me)
        END
        CLOSE   cr_span
        RETURN
END
GO

该函数将每个相邻的交叉范围压缩成一个范围，并返回范围和下一个间隙。

- Quassnoi

---

Quassnoi的回复是否是解决问题的方法？如果是，函数中数据表的名称是什么？ - user169448

Answer 2

如果你不是真正理解你要解决的问题，这里是我脑海中对于某些问题的解决方案：

1. 创建表函数（UDF）“all-dates”，返回一年中的所有日期。
2. 通过将事件与所有日期进行内部连接，将您的事件转换为单独的日期（一个事件行将变成与其天数相同的行）其中日期在事件的开始和结束日期之间... 保留原始eventId。
3. 再次使用外部连接将事件日期与所有日期进行连接，以查找间隙或遗漏。
4. 在日期相同但eventId不同的情况下将事件日期与自身连接以查找重叠。

- Andriy Volkov

Answer 3

我的朋友，使用PostgreSQL，你可以简单地这样做：

(start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2)

当两个时间段（由它们的端点定义）重叠时，此表达式返回true；否则返回false。端点可以指定为日期、时间或时间戳对；或者是日期、时间或时间戳后跟一个间隔。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); 结果：true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); 结果：false

但在SQL Server下，我不知道... 抱歉

- Michael REMY