Zeitabhängige Zustands-Verfolgung

[Tommi]

Hallo zusammen,

ich hab da mal ein kleines Problem, bei dem ich zu keiner einfachen Lösung komme.
Ich habe in meiner Auswertung Elemente, die zu bestimmten Zeiten einen bestimmten Zustand annehmen können. Der Zeit-Stempel, wann ein Zustand aufgetreten ist wie auch der Zustand selbst werden festgehalten.
Zum Ermittlungs-Zeitpunkt kann sich der Zustand des Elementes verändert haben, muss aber nicht zwingend so sein. (Die Zustands-Ermittlung wird durch externe Events ausgelöst, die nicht mit der Zustands-Änderung zusammenhängen.)

Das kann dann folgendermaßen aussehen:


Als Ergebnis möchte ich eine solche Tabelle bekommen:

Hat jemand von euch vielleicht eine Mengenbasierte Lösung, also ohne Cursor oder Schleife parat?

Viele Grüße,
Tommi

 

[akretschmer]

Mal so als Groblösung:

test=*# select * from tommi ;
 ts | zustand
----+---------
  1 | kalt
  2 | kalt
  3 | warm
  4 | warm
  5 | warm
  6 | heisz
  7 | heisz
  8 | warm
  9 | warm
 10 | kalt
(10 rows)

test=*# select array_agg(ts), x, zustand from (select *, sum(change) over (order by ts) as x from (select ts, zustand, case when lag(zustand) over (order by ts) != zustand then 1 else 0 end as change from tommi ) foo) bla group by x,zustand;
 array_agg | x | zustand
-----------+---+---------
 {3,4,5}  | 1 | warm
 {1,2}  | 0 | kalt
 {10}  | 4 | kalt
 {8,9}  | 3 | warm
 {6,7}  | 2 | heisz
(5 rows)

Statt Zeiten hier nur einfache INT-Werte. Im Resultat siehst dann die Bereiche mit dem Zustand. Aus den Arrays dann Min/Max zu extrahieren ist dann eher leicht. Geht bestimmt noch eleganter... ist aber wie üblich PostgreSQL.

 

[Tommi]

Hallo Andreas,

vielen Dank für diese Lösung. Ich bin immer wieder beeindruckt, was PostgreSQL kann .
Der Hinweis auf die LAG-Funktion bringt mich aber schon einmal einen Schritt weiter - vielen Dank.
(Gibt's im SQL Server erst seit Version 2012, deshalb hatte ich das nicht aufm Schirm)

Natürlich kann der SQL Server nicht so tolle Sachen wie "Array" und hat auch keiner erweiterten Funktionen der Aggregatfunktionen, aber irgendeine Lösung werde ich da noch finden.

Wenn du (oder auch jemand anderes) noch eine weitere Möglichkeit seht, bin ich für Tipps dankbar.

Viele Grüße,
Tommi

 

[Tommi]

ok, ich bin grade etwas überrascht worden. Eine Syntax wie "SUM(...) OVER (ORDER BY ...)" funktioniert tatsächlich auf einem SQL Server 2012!! (Diese Funktion ist nicht dokumentiert!)

@akretschmer: somit konnte ich deine Lösung nahezu 1:1 übernehmen! Sind zwar jetzt ein paar Schachtelungen mehr als ich wollte, aber die Laufzeit passt! Nochmals Danke!

Viele Grüße,
Tommi

 

[Distrilec]

Hätte mich auch stark gewundert, wenn MS SQL keine Window-Clause kennen würde... ?

Hier mal ganz ohne Lag / Window-Functions... Einfach nur als kleine Spielerei... Wahrscheinlich auch nicht so performant wie die Lösung von @akretschmer ... Ich habs aber nicht verglichen...

SQL> Select * From fps_max_test_tommi;
VALID_FROM                                                                       CONDITION
-------------------------------------------------------------------------------- ----------------
26.06.15 00:20:23,123                                                            kalt
26.06.15 00:20:23,432                                                            kalt
26.06.15 00:20:23,777                                                            warm
26.06.15 00:21:32,123                                                            warm
26.06.15 00:50:32,123                                                            warm
26.06.15 08:00:32,123                                                            warm
26.06.15 08:20:32,123                                                            kalt
26.06.15 08:43:32,123                                                            warm
26.06.15 08:59:32,123                                                            kalt
26.06.15 08:59:52,123                                                            warm
26.06.15 08:59:53,123                                                            warm
26.06.15 08:59:54,123                                                            warm
26.06.15 08:59:54,223                                                            warm
26.06.15 17:20:32,123                                                            warm
26.06.15 17:20:33,123                                                            warm
26.06.15 18:20:34,123                                                            kalt
26.06.15 19:20:35,123                                                            kalt
26.06.15 19:20:36,123                                                            kalt
26.06.15 20:20:37,123                                                            kalt
26.06.15 20:20:38,123                                                            kalt
VALID_FROM                                                                       CONDITION
-------------------------------------------------------------------------------- ----------------
26.06.15 20:20:39,123                                                            warm
21 rows selected

Select Min(valid_from) As valid_from
      ,valid_to
      ,Min(cond) As state
From   (Select t.valid_from
              ,Min(p.valid_from) As valid_to
              ,Min(t.condition) As cond
      
        From   fps_max_test_tommi t
      
        Left   Join fps_max_test_tommi p
        On     p.valid_from > t.valid_from
        And    p.condition <> t.condition
      
        Group  By t.valid_from
        Order  By valid_from)
Group  By valid_to
Order  By valid_from Asc;

    VALID_FROM   VALID_TO   STATE
1   26.06.15 00:20:23,123   26.06.15 00:20:23,777   kalt
2   26.06.15 00:20:23,777   26.06.15 08:20:32,123   warm
3   26.06.15 08:20:32,123   26.06.15 08:43:32,123   kalt
4   26.06.15 08:43:32,123   26.06.15 08:59:32,123   warm
5   26.06.15 08:59:32,123   26.06.15 08:59:52,123   kalt
6   26.06.15 08:59:52,123   26.06.15 18:20:34,123   warm
7   26.06.15 18:20:34,123   26.06.15 20:20:39,123   kalt
8   26.06.15 20:20:39,123     warm

 

[Tommi]

Hallo zusammen,

das mit den Fernsterfunktionen in Verbindung mit Aggregatfunktionen hat mir irgendwie keine Ruhe gelassen.
Diese Funktionalität gibt es tatsächlich erst seit dem SQL Server 2012, wird dort aber noch nicht sauber dokumentiert. Erst wenn man sich die Beispiele in der MSDN mal ansieht, wird das dort bereits genutzt.
Erst beim SQL Server 2014 werden die Aggregatfunktionen auch in Verbindung zu Fenster-Funktionen erklärt.

@Distrilec: Vielen Dank für deine Lösung. Die Lösung hat den Vorteil, dass eine Schachtelungs-Tiefe weniger benötigt wird. Nachteil für meine Umgebung ist tatsächlich die Laufzeit. Durch den Join auf die eigenen Werte wird natürlich die Gesamt-Datenmenge deutlich erhöht. Dennoch - sehr gute Idee und noch einmal vielen Dank.

Viele Grüße,
Tommi