SQL: расширенный временной интервал в vertica

Question

Привет, ребята: у меня есть следующая таблица в базе данных Vertica:

+-----+------+----------+
| Tid | item | time_sec |
+-----+------+----------+
|   1 | A    |        1 |
|   1 | B    |        2 |
|   1 | C    |        4 |
|   1 | D    |        5 |
|   1 | E    |        6 |
|   2 | A    |        5 |
|   2 | E    |        5 |
+-----+------+----------+

Моя цель - создать новые группы предметов, которые находятся в пределах временного интервала deltaT. Это означает, что разница между отметкой времени первого и последнего элемента меньше или равна deltaT. Пример: если deltaT = 2 сек, мы получили бы новую таблицу:

+-----+------+
| Tid | item |
+-----+------+
|  11 | A    |
|  11 | B    |
|  12 | B    |
|  12 | C    |
|  13 | C    |
|  13 | D    |
|  13 | E    |
|  14 | D    |
|  14 | E    |
|  15 | E    |
|  21 | A    |
|  21 | E    |
+-----+------+

Вот обход таблицы: сначала мы проверяем все элементы с Tid 1 и создаем подгруппы с Tid 1n, где n - счетчик. Наша первая подгруппа с Tid 11 состоит из элемента A, B, поскольку deltaT между последним и первым элементом =<2. Следующая группа имеет Tid 12 с пунктом B,C. Группа после этого имеет Tid 13 и предметы C,D,E, так как все предметы находятся в промежутке времени в 2 секунды. Это продолжается до последнего пункта с Tid 1. Затем мы начнем с группы, в которой есть Tid 2.

Новая нумерация Tid для подгрупп может быть непрерывной (1...6), я просто выбираю этот вид нумерации, чтобы показать связь с исходной таблицей.

Я смотрю на функции vertica LAG и Time_slice, но не могу найти способ элегантного решения такой проблемы.

Answer 1

Вот как далеко я добрался - и это не отвечает на ваш вопрос, на самом деле. Но это может составить несколько указателей:

WITH
-- your input
input(Tid,item,time_sec) AS (
          SELECT 1,'A',1
UNION ALL SELECT 1,'B',2
UNION ALL SELECT 1,'C',4
UNION ALL SELECT 1,'D',5
UNION ALL SELECT 1,'E',6
UNION ALL SELECT 2,'A',5
UNION ALL SELECT 2,'E',5
)
-- end of your input, start your "real" WITH clause here
,
input_w_ts AS (
  SELECT
    *
  , TIMESTAMPADD('SECOND',time_sec-1,TIMESTAMP '2000-01-01 00:00:00') AS ts
  FROM input
)
SELECT
  TS_LAST_VALUE(Tid) AS Tid
, item
, TS_LAST_VALUE(time_sec) AS time_sec
, tsr
FROM input_w_ts
TIMESERIES tsr AS '2 SECONDS' OVER (PARTITION BY item ORDER BY ts)
ORDER BY 1,4
;
Output:
Tid|item|time_sec|tsr
  1|A   |       1|2000-01-01 00:00:00
  1|B   |       2|2000-01-01 00:00:00
  1|A   |       1|2000-01-01 00:00:02
  1|C   |       4|2000-01-01 00:00:02
  1|D   |       5|2000-01-01 00:00:04
  1|E   |       6|2000-01-01 00:00:04
  2|A   |       5|2000-01-01 00:00:04