(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);

Как было объяснено ранее, устройства включаются и выключаются, и на этот раз я хочу извлечь конкретную последовательность:

show all new ON status records that aren't duplicate (same device twice in a row) show appropriate OFF status from currently ON device

Самое близкое, что я мог получить, это:

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 записи, которые не соответствуют. документация3, 4, 5 а также6 возвращаются, но я не хочу пятого, этоOFF который пришел после новогоON, Так что мне нужно преодолеть этот пробел и искать следующий правильныйOFF для текущего активного устройства.

10 turns OFF -- bogus in this context, but messes up the lag() 11 turns OFF -- bogus in this context, but messes up the lag() 11 turns ON -- OK, new sequence, include in SELECT 10 turns ON -- OK, new sequence, include in SELECT 11 turns OFF -- message came in late, need to ignore gap 10 turns OFF -- OK, proper OFF to row 4, need to include in SELECT

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