(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 для текущего активного устройства.

10 выключается - фальшивка в этом контексте, но запутывает запаздывание ()11 выключается - фальшивка в этом контексте, но запутывает запаздывание ()11 включений - ОК, новая последовательность, включить в SELECT10 включений - ОК, новая последовательность, включить в SELECT11 выключается - сообщение пришло поздно, пропустить нужно10 выключается - ОК, правильное ВЫКЛ в строке 4, необходимо включить в SELECT

После фильтрации правильно, я хотел бы использоватьlead() вдобавок ко всему, чтобы получить следующий рядs id (представьте метку времени), а также отфильтруйте все записи, которые не являютсяON статусы. Я предполагаю, что для этого потребуется три встроенных оператора SELECT. Это дало бы мне четкое представление о том, как долго устройство было активно, до состояния другогоON или правильный поворот.OFF