使用递归查询聚合期间

我需要将每个组（ID）的顺序事件（具有标识符 NUM）的重叠周期（由 FROM 和 TO 变量定义）与“先行缓冲区”合并，这意味着如果下一个周期在缓冲区内开始，它们应该是合并了。

例如;在以下示例中，第二个事件 (NUM = 2) 在时间 13 开始，该时间位于缓冲区 (10 + 5 = 15) 内。

与我发现的其他类似问题相比，这里棘手的部分是，尽管每个事件的缓冲期都有固定值，但如果它与具有较长缓冲期的事件（仅向后）合并，则可能会发生变化。

例如;事件三也合并到与事件1和事件2相同的周期，因为这些事件的缓冲周期更长。接下来的缓冲区应该是 (25 + 5 = 30)，而不是 (25 + 3 = 28)，这意味着接下来的事件 4 也应该包含在这些时间段中。

事件 4 的缓冲期也再次更改为 5。但是，因为 40 > 31+5，所以最后一个事件是单独的观察。

CREATE TABLE MY_TABLE(ID INTEGER, NUM INTEGER, FROM INTEGER, TO INTEGER, LOOKAHEAD INTEGER);
INSERT INTO MY_TABLE VALUES (1, 1, 1,  10, 5);
INSERT INTO MY_TABLE VALUES (1, 2, 13, 20, 5);
INSERT INTO MY_TABLE VALUES (1, 3, 21, 25, 3);
INSERT INTO MY_TABLE VALUES (1, 4, 29, 31, 3);
INSERT INTO MY_TABLE VALUES (1, 5, 40, 50, 3);

最终，我需要的结果是具有两个“不连续”周期的两个观察结果；

(ID = 1, FROM = 1,  TO = 31)
(ID = 5, FROM = 40, TO = 50)

当然，我最初认为我可以通过创建一个新变量 LOOKAHEAD2 来创建这个“LOOKHEAD”变量，该变量是 LOOKAHEAD2 的先前值和 LOOKAHEAD 的当前值的最大值，条件为 FROM(此记录)

相反，我尝试使用递归查询，从第一个事件开始(NUM = 1)，然后递归地将表与下一个事件连接起来(root.NUM+1 = next.NUM)有条件地(root.TO + root.LOOKAHEAD > next.FROM)，并相应地更新 LOOKAHEAD 变量。

但我以前从未使用过递归查询，并且无法让它加入 LOOKAHEAD 值的更新值。

有谁知道如何通过递归查询或其他方式解决这个问题？

您应该使用RESET WHEN分析函数中的窗口修饰符（LAG在 Teradata 16 中，或MAX在早期）；不要使用递归查询。

Update:

DROP TABLE MY_TABLE;
CREATE VOLATILE TABLE MY_TABLE 
( id          INTEGER
, num         INTEGER
, from_value  INTEGER
, to_value    INTEGER
, lookahead   INTEGER
) ON COMMIT PRESERVE ROWS;

INSERT INTO MY_TABLE VALUES (1, 1, 1,  10, 5);
INSERT INTO MY_TABLE VALUES (1, 2, 13, 20, 5);
INSERT INTO MY_TABLE VALUES (1, 3, 21, 25, 3);
INSERT INTO MY_TABLE VALUES (1, 4, 29, 31, 3);
INSERT INTO MY_TABLE VALUES (1, 5, 40, 50, 3);

INSERT INTO MY_TABLE VALUES (2, 1, 1, 10, 5);
INSERT INTO MY_TABLE VALUES (2, 2, 20, 30, 15);
INSERT INTO MY_TABLE VALUES (2, 3, 40, 41, 5);
INSERT INTO MY_TABLE VALUES (2, 4, 100, 200, 5);
INSERT INTO MY_TABLE VALUES (2, 5, 300, 400, 3);


SELECT  id, first_from_value, to_value
FROM  ( SELECT  id
              , to_value
              , CASE WHEN overlaps_flag = 1
                  THEN  NULL
                  ELSE  COALESCE 
                        ( MIN (from_value) 
                            OVER (PARTITION BY id
                                  ORDER BY from_value
                                  RESET WHEN MAX (overlaps_flag) 
                                               OVER (PARTITION BY id 
                                                     ROWS BETWEEN 
                                                          1 PRECEDING 
                                                      AND 1 PRECEDING) = 0
                                  ROWS BETWEEN UNBOUNDED PRECEDING 
                                           AND 1 PRECEDING)
                        , from_value )
                END AS first_from_value
        FROM  ( SELECT  id, from_value, to_value
                      , MAX (from_value) 
                          OVER (PARTITION BY id 
                                ORDER BY from_value 
                                ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
                          AS next_from_value
                      , CASE WHEN to_value + lookahead + 1 >= next_from_value
                          THEN 1 ELSE 0 
                        END AS overlaps_flag
                FROM  my_table
              ) AS a
      ) AS a
WHERE first_from_value IS NOT NULL
ORDER BY 1, 2

id  first_from_value    to_value
1   1                   31
1   40                  50
2   1                   10
2   20                  41
2   100                 200
2   300                 400