SQL: Überlappende Zeiträume zusammenfassen

Timobeil

Neuer Benutzer
Beiträge
4
Hallo Zusammen,

ich habe eine Tabelle: Ausfallzeit, in der Ausfallzeiten für bestimmte Fehler an einer Produktionslinie erfasst werden.


ID - Fehler - Start - Ende
1 - 1 - 1443791600 - 1443791721
2 - 1 - 1443791660 - 1443791785
3 - 2 - 1443791710 - 1443791850

4 - 1 - 1443791790 - 1443791911
5 - 2 - 1443791800 - 1443792121


Der Start und Endzeitpunkt wird als Unix Timestamp gespeichert.

Wenn ich aktuell eine Abfrage für die gesamte Ausfallzeit eines Fehlers mache sieht diese wie folgt aus:
Code:
SELECT  fehler, sum(ende-start)ausfall

FROM ausfallzeit 

WHERE fehler = 1
AND start BETWEEN 1443791590 AND 1443791950
AND ende > 1

GROUP BY fehler

Ergebnis: Richtiges Ergebnis:
Fehler - Ausfall Fehler - Ausfall
1 - 367 1 - 306

Leider habe ich nicht bedacht, das sich manche Fehler auch überschneiden werden und meine Auswertung somit fehlerhaft ist :(
Es kann nämlich passieren, dass zum Beispiel der Fehler 1, vorne an der Linie von 15:10 bis 15:12 gemeldet wurde. Und der selbe Fehler um 15:11 bis 15:13 am Ende der Linie.
Meine Abfrage würde hier 4 Minuten als Ergebnis liefern, obwohl 3 Minuten als Ausfallzeit für die Linie richtig wären.

Ich habe auch schon durch googeln und ausprobieren versucht meine einfache Abfrage anzupassen, doch leider ohne Erfolg.

Darum frage ich nun einmal euch um Hilfe, habt Ihr Ideen, Vorschläge oder sogar eine Lösung für mein Problem ? :)






 
Werbung:
Ich habe es mal mit PostgreSQL gelöst, vielleicht hilft Dir es ja.

Die Tabelle:

Code:
test=*# select * from timobell ;
 id | fehler |  zeitraum
----+--------+-------------------------
  1 |  1 | [1443791600,1443791721)
  2 |  1 | [1443791660,1443791785)
  3 |  2 | [1443791710,1443791850)
  4 |  1 | [1443791790,1443791911)
  5 |  2 | [1443791800,1443792121)
(5 rows)

Ich habe hier int4range für den Zeittraum verwendet, ist einfacher als 2 separate Spalten.

Nun schaue ich mit lag(), ob sich das mit dem vorherigen Zeitraum überschneidet:

Code:
test=*# select *, upper(zeitraum) - lower(zeitraum) - coalesce((upper(zeitraum * lag(zeitraum) over (order by id)) - lower(zeitraum * lag(zeitraum) over (order by id))),0) as dauer from timobell where fehler = 1;
 id | fehler |  zeitraum  | dauer
----+--------+-------------------------+-------
  1 |  1 | [1443791600,1443791721) |  121
  2 |  1 | [1443791660,1443791785) |  64
  4 |  1 | [1443791790,1443791911) |  121
(3 rows)

Ist der Zeitraum der aktuellen Zeile überlager mit der der vorhergehenden, so zuehe ich die Dauer dieser Überlagerung ab. Nun noch die Summe:

Code:
test=*# select sum(dauer) from (select *, upper(zeitraum) - lower(zeitraum) - coalesce((upper(zeitraum * lag(zeitraum) over (order by id)) - lower(zeitraum * lag(zeitraum) over (order by id))),0) as dauer from timobell where fehler = 1) foo;
 sum
-----
 306
(1 row)

Du hattest Recht ;-)

Im Prinzip sollte das mit M$SQL lösbar sein, es kann ja lag(). Wird nur etwas fummeliger mit den Zeiten.
 
Danke für deine Antwort akretschmer! Das sieht ja sehr gut aus :)

Ich werde gleich mal schauen, ob ich das so auch in MySQL hin bekomme ;)
 
Hallo,

bitte entschuldigt das ich mich jetzt erst wieder melde, ich wurde quasi mit anderen Dingen zugeschüttet ^^

Sry, Ich meine MySQL, da habe ich wohl zu schnell geklickt und nicht richtig gelesen wohin ich das Thema packe.

@akretschmer : Das hört sich ja fast so an als wäre es in MySQL nicht bzw. kaum umsetzbar, oder gibt es nicht etwas ähnliches in MySQL ?
 
Eine "live" Berechnung scheint mir gar nicht lösbar, zuminest keine, die auch performant arbeitet.
Jenachdem wie man "Rekursion in MySQL" bezogen auf Performance einordnet...

Mit Rekursion lösbar... Die Methoden die MySQL da zur Verfügung stellt kenne ich allerdings nicht...
Ich nehme an da arbeitet man mit Variablen?
 
Guten Morgen zusammen,

hier mal eine kurze Rückinfo:

Ich habe mich nun dazu entschieden alles über eine PostgreSQL Datenbank laufen zu lassen.

Nachdem ich mich nun etwas eingearbeitet habe, funktioniert alles so wie es soll - einfach prima!:D

Vielen Dank nochmals an eure Antworten, speziell an @akretschmer, Dankeschön! :)
 
Werbung:
Zurück
Oben