PostgreSQL группировка при изменении значения

Question

Есть пример данных:

+----------+----+
|time      |temp|
+----------+----+
|1606163169|10  |
|1606163165|0   |
|1606163163|5   |
|1606162384|0   |
|1606161384|0   |
|1606160384|0   |
|1606160380|24  |
|1606160360|10  |
+----------+----+

Нужно сделать разбитие на интервалы где значение меняется на 0 на промежуток времени к примеру > 300 секунд

+----------+----+-----+
|time      |temp|event|
+----------+----+-----+
|1606163169|10  |3    |
|1606163165|0   |3    |
|1606163163|5   |3    |
|1606162384|0   |2    |
|1606161384|0   |2    |
|1606160384|0   |2    |
|1606160380|24  |1    |
|1606160360|10  |1    |
+----------+----+-----+

Смысл в разбитии такой: первое сообщение в 1606160360, temp > 0, это первая группа. Следующее сообщение temp тоже больше нуля, поэтому это будет та же группа.

Затем значение temp = 0, и устанавливается оно на длительное время с 1606160384 по 1606162384. (1606162384 - 1606160384 > 300), значит нужно выделить новую группу (значение temp изменилось на 0 на длительное время).

Следующее сообщение в 1606163163 temp становится больше нуля, значит начинается новая группа, через 2 секунды значение temp становится 0, но только на 4 секунды < 300 по этому для трех сообщений это будет одна и та же группа.

Может есть какие-то мысли? Что-то я в тупике (

Answer 1

Проверяйте:

WITH 
cte1 AS (SELECT "time", 
                temp,
                MAX(temp) OVER (ORDER BY "time" 
                                RANGE BETWEEN CURRENT ROW 
                                  AND 300 FOLLOWING) > 0 "group"
         FROM test),
cte2 AS (SELECT "time", 
                temp,
                CASE WHEN "group" = LAG("group") OVER (ORDER BY "time") 
                     THEN 0
                     ELSE 1 END "group"
         FROM cte1)
SELECT "time",
       temp,
       SUM("group") OVER (ORDER BY "time") "event"
FROM cte2 
ORDER BY "time"

fiddle

БЛОГ НА HUSL

PostgreSQL группировка при изменении значения

Ответы (1 шт):