有没有办法使用sql查询将一个时间序列数据表重叠到另一个时间序列数据表上？

Question

希望我能在这里找到我的难题的答案。

我在Postgres DB中有两个表，我想重叠到另一个表上。

表A有3列：“开始时间”、“结束时间”和“状态”。在每一行中，开始时间等于前一行的结束时间。

表B具有相同的列，但开头

我想将表B重叠到表A上以创建表C。

表C中每一行的开始时间也等于前一行的结束时间。

下面是一个例子来说明：

表A-受影响者

表B-重叠器

表C重叠的结果

如您所见，如果表B中的行的开始时间在表a中的行时间范围内，则表a中的行的结束时间将替换为表B中的行的开始时间。相反，如果表B中的行的结束时间在表a中的时间范围内，然后，表A中行的开始时间替换为表B中行的结束时间。

其次，如果表A和表B中的开始时间和结束时间完全相同，则B中的行将替换A中的行。

第三，如果表B中一行的时间范围属于表A中一行的时间范围，那么表A行被分成2，第一行的结束时间是表B行的开始时间然后第二行的开始时间将是表B行的结束时间。

最后，必须为时间范围正确保留状态。

是否可以通过postgres sql查询实现这一点？。我没有发现任何类似的问题或答案。

注意：在每个表中也有一个ID列，为了简单起见，我在这里省略了它，但是如果它有助于实现重叠，可以使用它。

Answer 1

一种方法是提取所有开始时间和结束时间，然后在该开始时间提取正确的状态，并使用lead（）获取结束时间：

with t as (
      select starttime as tm
      from a
      union  -- on purpose to remove duplicates
      select endtime
      from a
      union 
      select starttime as tm
      from b
      union  -- on purpose to remove duplicates
      select endtime
      from b
     )
select t.tm, lead(t.tm) over (order by t.tm) as endtime,
       (select ab.state
        from ((select a.*
               from a
              ) union all
              (select b.*
               from b
              )
             ) ab
        where ab.starttime <= t.tm and t.tm < ab.endtime
        order by ab.starttime desc
        limit 1
       ) as state
from t;