sql - 考虑行之间的 "difference"对行进行分组

Question

我有一个包含开始时间(在示例中使用数字以保持简单)和事件持续时间的表格。

我想确定“块”及其开始和结束时间。
每当前一行的结束时间(开始时间+持续时间)(按开始时间排序)与当前行的开始时间之差为>=5 ，一个新的“块”应该开始。

这是我的测试数据，包括在评论中尝试图形解释:

WITH test_data AS (
  SELECT  0 s, 2 dur FROM dual UNION ALL   --# ■■
  SELECT  2  , 2     FROM dual UNION ALL   --#   ■■
  SELECT 10  , 1     FROM dual UNION ALL   --#           ■
  SELECT 13  , 4     FROM dual UNION ALL   --#              ■■■■
  SELECT 15  , 4     FROM dual             --#                ■■■■
)
--# Should return
--#   0 ..  4                              --# ■■■■
--#  10 .. 19                              --#           ■■■■■■■■■

第一个区块开始于 0并在 4 结束.由于与下一行的差异是 >=5 , 在 10 开始另一个块结束于 19 .

我可以使用 LAG 识别块的第一行，但我还没有找到如何进行。

我可以在 PL/SQL 循环中解决这个问题，但出于性能原因，我试图避免这种情况。

关于如何编写此查询的任何建议？

提前致谢，彼得

Answer 1

我使用带有分析的子查询来识别和分组连续范围:

SQL> WITH test_data AS (
  2    SELECT  0 s, 2 dur FROM dual UNION ALL   --# ■■
  3    SELECT  2  , 2     FROM dual UNION ALL   --#   ■■
  4    SELECT 10  , 1     FROM dual UNION ALL   --#           ■
  5    SELECT 13  , 4     FROM dual UNION ALL   --#              ■■■■
  6    SELECT 15  , 4     FROM dual             --#                ■■■■
  7  )
  8  SELECT MIN(s) "begin", MAX(s + dur) "end"
  9    FROM (SELECT s, dur, SUM(gap) over(ORDER BY s) my_group
 10             FROM (SELECT s, dur,
 11                           CASE
 12                              WHEN lag(s + dur) over(ORDER BY s) >= s - 5 THEN
 13                               0
 14                              ELSE
 15                               1
 16                           END gap
 17                      FROM test_data
 18                     ORDER BY s))
 19   GROUP BY my_group;

     begin        end
---------- ----------
         0          4
        10         19