Перескочить пробел в SQL через конкретное условие и правильное использование lead ()
(PostgreSQL 8.4) Продолжая с моимпредыдущий примерЯ хотел бы углубить мое понимание обработки пробелов и островков с помощью оконных функций. Рассмотрим следующую таблицу и данные:
CREATE TABLE T1
(
id SERIAL PRIMARY KEY,
val INT, -- some device
status INT -- 0=OFF, 1=ON
);
INSERT INTO T1 (val, status) VALUES (10, 0);
INSERT INTO T1 (val, status) VALUES (11, 0);
INSERT INTO T1 (val, status) VALUES (11, 1);
INSERT INTO T1 (val, status) VALUES (10, 1);
INSERT INTO T1 (val, status) VALUES (11, 0);
INSERT INTO T1 (val, status) VALUES (10, 0);
Как было объяснено ранее, устройства включаются и выключаются, и на этот раз я хочу извлечь конкретную последовательность:
показать все новоеON статусные записи, которые недубликат (одно устройство дважды)показать соответствующиеOFF статус от в настоящее времяON устройствоСамое близкое, что я мог получить, это:
SELECT * FROM (
SELECT *
,lag(val, 1, 0) OVER (PARTITION BY status ORDER BY id) last_val
,lag(status, 1, -1) OVER (PARTITION BY val ORDER BY id) last_status
FROM t1
) x
WHERE (last_val val OR last_status status)
AND (status = 1 OR last_status -1)
ORDER BY id
Это отфильтровывает больше фиктивных данных, что образец не 'не включать, но по сути этоо удалении последующих дубликатов (независимо от статуса) и топOFF записи, которые нет совпадают. Записи,345 а также6 вернулись, но я нене хочу пятого, этосOFF который пришел после новогоON, Так что мне нужно преодолеть этот пробел и искать следующий правильныйOFF для текущего активного устройства.
После фильтрации правильно, я хотел бы использоватьlead() вдобавок ко всему, чтобы получить следующий рядs id (представьте метку времени), а также отфильтруйте все записи, которые не являютсяON статусы. Я предполагаю, что для этого потребуется три встроенных оператора SELECT. Это дало бы мне четкое представление о том, как долго устройство было активно, до состояния другогоON или правильный поворот.OFF