SQL - 基于两个不同的时间线合并两个表

Question

我正在努力想出一种方法来加入/合并基于日期范围的两个表，以创建一个带有一个时间线的单个表。

带有一些示例数据的表格的精简版本:

Table 1
---------------------------------
ID    Start      End       State
1     10:00      10:05     A
2     10:23      10:24     B
3     10:32      10:40     A
4     14:00      14:15     C


Table 2
---------------------------------
ID    Start      End       State
1     10:22      10:27     X
2     11:00      11:20     Y
3     12:05      14:30     Z

两个表由两个不同的进程维护，虽然在每个单独的表中事件永远不会重叠，但两个表之间可能会有重叠。

第一个表中的事件应优先于第二个表中的事件。也就是说，基本上从第一个中选择所有内容并用第二个填充空白。

基于上面的示例，预期的查询输出将是:

Start      End       State
10:00      10:05     A
10:22      10:23     X
10:23      10:24     B
10:24      10:27     X
10:32      10:40     A
11:00      11:20     Y
12:05      14:00     Z
14:00      14:15     C
14:15      14:30     Z

注意表 2 中的状态 Z 是如何被表 1 中的状态 C 分割的。

我看到了一些示例，其中表是基于单个时间戳连接的，或者可能是一个具有日期范围的表和另一个具有单个时间戳的表。我还没有看到这种性质的东西，而且由于我的经验水平相当基本，我的想法一直在循环。

感谢您的任何建议，如果我提出任何突破，我一定会更新。

更新

感谢戈登，这是我正在使用的解决方案(对他所拥有的版本进行了非常轻微的修改):

---------------------------------------------------------------------------------------------
-- Setup Table 1                                                                           --
---------------------------------------------------------------------------------------------
DECLARE @Table1 TABLE(ID INT, Start DATETIMEOFFSET(7), [End] DATETIMEOFFSET(7), [State] CHAR)

INSERT  @Table1
VALUES  (1, '2013-12-21 10:00:00 +00:00', '2013-12-21 10:05:00 +00:00', 'A'),
        (2, '2013-12-21 10:23:00 +00:00', '2013-12-21 10:24:00 +00:00', 'B'),
        (3, '2013-12-21 10:32:00 +00:00', '2013-12-21 10:40:00 +00:00', 'A'),
        (4, '2013-12-21 14:00:00 +00:00', '2013-12-21 14:15:00 +00:00', 'C')

SELECT * FROM @Table1

---------------------------------------------------------------------------------------------
-- Setup Table 2                                                                           --
---------------------------------------------------------------------------------------------
DECLARE @Table2 TABLE (ID INT, Start DATETIMEOFFSET(7), [End] DATETIMEOFFSET(7), [State] CHAR)

INSERT  @Table2
VALUES  (1, '2013-12-21 10:22:00 +00:00', '2013-12-21 10:27:00 +00:00', 'X'),
        (2, '2013-12-21 11:00:00 +00:00', '2013-12-21 11:20:00 +00:00', 'Y'),
        (3, '2013-12-21 12:05:00 +00:00', '2013-12-21 14:30:00 +00:00', 'Z')

SELECT * FROM @Table2

---------------------------------------------------------------------------------------------
-- Merge Tables                                                                            --
---------------------------------------------------------------------------------------------
;WITH StateChangeTimes AS (
    SELECT  DISTINCT TheTime
    FROM    (SELECT T1.Start AS TheTime, T1.[State]
             FROM   @Table1 T1
             UNION  ALL
             SELECT T1.[End], NULL
             FROM   @Table1 T1
             UNION  ALL
             SELECT T2.Start, T2.[State]
             FROM   @Table2 T2
             UNION  ALL
             SELECT T2.[End], NULL
             FROM   @Table2 T2) T ),
TimePairs AS (
    SELECT   TheTime AS Start,
            (SELECT MIN(SCT2.TheTime)
             FROM   StateChangeTimes SCT2
             WHERE  SCT2.thetime > SCT.TheTime) AS [End]
    FROM     StateChangeTimes SCT)
SELECT  Start,
        [End],
        COALESCE(T1State, T2State) AS [State]
FROM    (SELECT Start,
                [End],
                (SELECT TOP 1
                        T1.[State]
                 FROM   @Table1 T1
                 WHERE  TP.Start >= T1.Start AND TP.[End] <= T1.[End]
                 ORDER BY T1.Start DESC) T1State,
                (SELECT TOP 1
                        T2.[State]
                 FROM   @Table2 T2
                 WHERE  TP.Start >= T2.Start AND TP.[End] <= T2.[End]
                 ORDER BY T2.Start DESC) T2State
         FROM TimePairs TP) TP2
ORDER BY Start;

我所做的主要改变:

在获取合并状态的 select 语句的 where 子句中，我使 End 时间戳包含在内。

如第一个要点中提到的，在第一个状态选择语句中添加了 TOP 1 限定符。

Answer 1

我认为这是一个很难的问题。

这是我思考的方式。获取所有开始和结束时间的列表。这提供了不同时间段的“边界”。接下来，查找每个时间段内的状态。

给定时间段内的状态规则是:

如果时间段 start 是从 Table1 开始，则使用该状态。

如果时间段 start 大于 Table1 的 start 且小于 end，则使用 Table1 中的 state。这是优先规则的一部分，当 table2 状态与 table1 状态重叠时会发生。

否则使用表 2 中的状态。

(规则 2 实际上包含规则 1。)

下一个问题是在 SQL 中实现它。方法是获取各个时间段，将它们组合成对，然后进行查找。因为SQL Server 2008缺少像 lag()这样的功能和累积总和(在 SQL Server 2012 中)，代码改为使用相关子查询。

with StateChangeTimes as (
      select distinct thetime
      from (select start as thetime, state
            from Table1 t1
            union all
            select end, NULL
            from Table1 t1
            union all
            select start, state
            from Table2 t2
            union all
            select end, NULL
            from Table2 t2
           ) t
    ),
     timepairs as (
     select thetime as start,
            (select min(thetime)
             from StateChangeTimes sct2
             where sct2.thetime > sct.thetime
            ) as end
     from StateChangeTimes sct 
    )
select start, end, coalesce(t1State, t2State) as state
from (select start, end,
             (select t1.state
              from Table1 t1
              where tp.start >= t1.start and tp.end < t1.end
             ) t1State,
             (select t2.state
              from Table2 t2
              where t2.start <= tp.start
              order by t2.start desc
             ) t2State
      from timepairs tp
     ) tp
order by start;

我不确定当两个表中出现相同的时间时，此代码是否有效。此外，如果 Table1 时间重叠，则会产生错误。这相对容易修复，但似乎不是必需的。