(PostgreSQL 8.4) Weiter mit meinervorheriges BeispielIch möchte mein Verständnis der Gaps-and-Islands-Verarbeitung mit Window-Funktionen erweitern. Betrachten Sie die folgende Tabelle und Daten:

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

Wie bereits erläutert, schalten sich die Geräte ein und aus und dieses Mal möchte ich eine bestimmte Sequenz extrahieren:

zeige alles neuON Statusdatensätze, die nicht doppelt vorhanden sind (dasselbe Gerät zweimal hintereinander)zeigen angemessenOFF Status von derzeitON Gerät

Der nächste, den ich bekommen könnte, ist der folgende:

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

Dadurch werden mehr falsche Daten herausgefiltert, die in der Stichprobe nicht enthalten sind. Im Wesentlichen geht es jedoch darum, nachfolgende Duplikate (unabhängig vom Status) und die obersten zu entfernenOFF Datensätze, die nicht übereinstimmen. Aufzeichnungen3, 4, 5 und6 zurückgegeben werden, aber ich will nicht die fünfte, es ist eineOFF was nach einem neuen kamON. Also muss ich diese Lücke überspringen und nach dem nächsten richtigen Ausschau haltenOFF für das aktuell aktive Gerät.

10 ausschalten - in diesem Zusammenhang falsch, aber die Verzögerung durcheinander ()11 wird ausgeschaltet - in diesem Zusammenhang falsch, aber die Verzögerung durcheinander ()11 schaltet ein - OK, neue Sequenz, in SELECT aufnehmen10 schaltet ein - OK, neue Sequenz, in SELECT aufnehmen11 schaltet sich aus - Nachricht kam zu spät, müssen Lücke zu ignorieren10 schaltet AUS - OK, richtig AUS für Zeile 4, muss in SELECT enthalten sein

Einmal richtig gefiltert, möchte ich verwendenlead() Darüber hinaus erhalten Sie die ID der nächsten Zeile (stellen Sie sich einen Zeitstempel vor) und filtern alle Datensätze heraus, die nicht vorhanden sindON Status. Ich kann mir vorstellen, dass hierfür drei eingebettete SELECT-Anweisungen erforderlich sind. Dies würde mir ein klares Verständnis darüber verschaffen, wie lange ein Gerät aktiv war, bis der Zustand eines anderenON oder eine richtige WendeOFF.