(PostgreSQL 8.4) Kontynuując mojepoprzedni przykładChciałbym lepiej zrozumieć przetwarzanie luk i wysp za pomocą funkcji okna. Rozważ poniższą tabelę i dane:

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

Jak wyjaśniono wcześniej, urządzenia włączają się i wyłączają, a tym razem chcę wyodrębnić określoną sekwencję:

pokaż wszystko noweON rekordy stanu, które nie są duplikatami (to samo urządzenie dwa razy z rzędu)pokaż odpowiedniOFF status z obecnieON urządzenie

Najbliższe, jakie mógłbym uzyskać, to:

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

To odfiltrowuje więcej fałszywych danych, których nie zawiera próbka, ale zasadniczo chodzi o pobieranie kolejnych duplikatów (niezależnie od statusu) i góryOFF rekordy, które nie pasują. Dokumentacja3, 4, 5 i6 są zwracane, ale nie chcę piątej, to jestOFF który przyszedł po nowymON. Muszę więc przeskoczyć tę lukę i poszukać następnego właściwegoOFF dla aktualnie aktywnego urządzenia.

10 wyłącza - fałszywe w tym kontekście, ale opóźnia opóźnienie ()11 wyłącza - fałszywe w tym kontekście, ale opóźnia opóźnienie ()11 włącza się - OK, nowa sekwencja, włącz w SELECT10 włączeń - OK, nowa sekwencja, włącz w WYBIERZ11 wyłącza się - wiadomość dotarła późno, należy zignorować lukę10 wyłącza się - OK, poprawne wyłączenie do wiersza 4, należy uwzględnić w WYBIERZ

Po prawidłowym przefiltrowaniu chciałbym użyćlead() do tego wszystkiego, aby uzyskać identyfikator następnego wiersza (wyobraź sobie znacznik czasu), a także odfiltrować wszystkie rekordy, które nie sąON statusy. Wyobrażam sobie, że będzie to wymagało trzech wbudowanych instrukcji SELECT. Pozwoli mi to zrozumieć, jak długo urządzenie było aktywne, aż do stanu innegoON lub właściwą kolejkęOFF.