Ich habe eine Tabelle in Oracle mit zwei Spalten ('datum' [DATE] und 'aktiv'[BOOLEAN]).
Darin wird im zeitlichen Ablauf gespeichert, ob ein System aktiv oder inaktiv ist. Im Normalfall sollten sich die Werte in der aktiv-Spalte bei zeitlich sortiertem Datum immer abwechseln, also aktiv folgt nur auf inaktiv und umgekehrt.
Das würde dann so aussehen:
datum aktiv
------------------------------
30.09.2011 21:00 1
30.09.2011 22:00 0
30.09.2011 23:00 1
01.10.2011 00:00 0
01.10.2011 01:00 1
01.10.2011 02:00 0
In seltenen Fällen kann es aber passieren, dass zwei- oder mehrmals aktiv bzw. zwei- oder mehrmals inaktiv aufeinander folgt.
datum aktiv
------------------------------
30.09.2011 21:00 1
30.09.2011 22:00 0
30.09.2011 23:00 0
01.10.2011 00:00 0
01.10.2011 01:00 1
01.10.2011 02:00 1
Im gerade gezeigten Beispiel wären demnach nur der 1., 2. und 5. Eintrag gültig.
Ich brauche nun eine SQL-Abfrage, die mir zu einem von mir festgelegten Datum den Zeitpunkt der letzten GÜLTIGEN Statusmeldung liefert.
Sieht die Tabelle also so aus...
datum aktiv
------------------------------
30.09.2011 21:00 1
30.09.2011 22:00 0
30.09.2011 23:00 1
01.10.2011 00:00 0
01.10.2011 01:00 1
01.10.2011 02:00 1
... und mein definiertes Datum ist der 02.10.2011 05:00, dann sollte als Zeitpunkt der letzten gültigen Statusmeldung 01.10.2011 01:00 geliefert werden (da 01.10.2011 02:00 ungültig war -> aktiv folgt auf aktiv).
Wie würde so eine Abfrage aussehen? Ich probiere jetzt schon ewig mit Unterabfragen etc. herum aber ich komme einfach nicht drauf. Ist das überhaupt in einer einzelnen Abfrage möglich?
Danke und LG
Peter
Was hier vielleicht klappen kann ist ein Join der Tabelle mit sich selbst nach folgendem Muster:
SELECT T1.Datum, T1.aktiv, T2.aktiv
FROM Table T1
JOIN Table T2
ON Datum = Datum + 1h
WHERE T1.aktiv + T2.aktiv != 1
Der Vorteil der Klugheit liegt darin, dass man sich dumm stellen kann - umgekehrt ist das schon schwieriger (K. Tucholsky)
Das Problem mit Internet-Zitaten ist, dass sie oftmals zu unrecht als authentisch angenommen werden. (K. Adenauer)
Ich hab es mittlerweile selbst geschafft und zwar sowohl mit Unterabfrage als auch ohne. Meine Frage wäre jetzt nur noch, ob man das zweite Query (ohne Unterabfrage) noch optimieren kann. Insbesondere hätte ich gerne noch das ORDER BY eliminiert. Hat jemand Vorschläge?
Hier nun die Queries (allerdings in Postgres und nicht in Oracle, sollte aber vom Prinzip her gleich funktionieren):
mit Subquery:
select MIN(t1.datum) from activetable t1 where
t1.datum < current_timestamp and
t1.datum >= (select MAX(t2.datum) from activetable t2 where t2.datum < current_timestamp and t2.aktiv = false) and
t1.aktiv = true
ohne Subquery:
select MIN(t1.datum) from activetable t1, activetable t2 where
t1.datum < current_timestamp and
t2.datum < current_timestamp and
t1.aktiv = true and
t2.aktiv = false and
t1.datum >= t2.datum
group by t2.datum
having MIN(t1.datum) >= t2.datum
order by t2.datum desc limit 1