mysql - 简化 SQL 中的时间线 (Netezza)

Question

我想总结/简化(我不知道该怎么称呼)我的时间线。

所以我拥有的是带有时间轴的 ID。我试图摆脱同一 ID 内的重叠时间线。

这里是数据的例子。我有什么:

ID   START_TIME   END_TIME
 1        a          b
 1        c          d
 1        e          f
 1        g          h

从图中可以看出，[a,b],[c,d],[e,f]相互重叠，[g,h]不相交所以我只需要 [a,f] 和 [g,h]。我想要什么:

ID   START_TIME   END_TIME
 1        a          f
 1        g          h

Answer 1

我认为@shA.T 很接近。问题是在多个重叠中这会崩溃。您可能必须将其变成一个多步骤过程

第 1 步(制作示例表):

 create temp table stack (
 id integer
 ,start_time timestamp
 ,end_time timestamp
 )

 insert into stack  values(1, date('2020-01-01'),date('2020-01-01') + interval '3 hours');
 insert into stack  values(1,date('2020-01-01') + interval '2 hours',date('2020-01-01') + interval '4 hours');
 insert into stack  values(1,date('2020-01-01') + interval '3.5 hours',date('2020-01-01') + interval '5 hours');
 insert into stack  values(1,date('2020-01-01') + interval '5.5 hours',date('2020-01-01') + interval '6.5 hours');
 insert into stack  values(1,date('2020-01-01') + interval '7.5 hours',date('2020-01-01') + interval '9.5 hours');
 insert into stack  values(1,date('2020-01-01') + interval '8.5 hours',date('2020-01-01') + interval '10.5 hours');

第 2 步(查找单个重叠):

create temp table stack2 as
 SELECT ID, ps2 as start_time, max(e) AS End_Time
    FROM (
        SELECT t1.ID, t1.START_TIME AS s, MAX(t1.END_TIME) AS e,
               max(t2.START_TIME) As ps, MAX(t2.END_TIME) AS pe
               ,CASE WHEN pe between s and e THEN ps ELSE s END ps2
        FROM stack AS t1
        JOIN stack AS t2 ON t1.START_TIME > t2.START_TIME
        GROUP BY t1.ID, t1.START_TIME) AS DT
    GROUP BY
        ID, ps2
    ORDER BY ps2

第 3 步(合并双重重叠):

 SELECT ID, ps2 as start_time, max(e) AS End_Time
    FROM (
        SELECT t1.ID, t1.START_TIME AS s, MAX(t1.END_TIME) AS e,
               max(t2.START_TIME) As ps, MAX(t2.END_TIME) AS pe
               ,CASE WHEN pe between s and e THEN ps ELSE s END ps2
        FROM stack2 AS t1
        JOIN stack2 AS t2 ON t1.START_TIME > t2.START_TIME
        GROUP BY t1.ID, t1.START_TIME) AS DT
    GROUP BY
        ID, ps2
    ORDER BY ps2