SQL-Query Zeit zwischen Änderungen

[Roin]

Hallo Leute.
Ich habe gerade eine kleine Denkblockade und glaube, dass meine Lösung für ein Problem von mir sehr ineffektiv ist und vielleicht hat hier ja jemand einen besseren Vorschlag.

Zu der Tabelle:
[TABLE="class: grid, width: 500"]
[TR]
[TD]id[/TD]
[TD]timepoint[/TD]
[TD]mainvalue[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2017-07-18-15-00-00[/TD]
[TD]13.05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2017-07-18-15-00-01[/TD]
[TD]14.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]2017-07-18-15-00-02[/TD]
[TD]14.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2017-07-18-15-00-03[/TD]
[TD]14.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]2017-07-18-15-00-04[/TD]
[TD]15.0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]


Soweit zu der Ausgangssituation.
Nun möchte ich den Startzeitpunkt, Endzeitpunkt und die Zeitdifferenz wissen, in denen der Wert sich nicht geändert hat.

Meine (mögliche) Lösung wäre:
[src=mysql]SELECT t1.timepointAS startpoint, t3.timepoint AS endpoint
FROM tbl AS t1
INNER JOIN tbl AS t2 ON t1.id = t2.id-1
INNER JOIN tbl AS t4 ON t2.mainvalue = t4.mainvalue
INNER JOIN tbl AS t3 ON t3.id = t4.id+1
WHERE t1.mainvalue <> t2.mainvalue AND
t3.mainvalue <> t4.mainvalue AND
t2.mainvalue = 14.1[/src]

Derzeit habe ich leider kein System zur Hand, mit dem ich das testen könnte (SQLFiddler will irgendwie nicht...) aber so ähnlich würde ich das zumindest versuchen zu lösen.
Da gibt es aber bestimmt was besseres, oder nicht?

--- [2017-07-18 16:00 CEST] Automatisch zusammengeführter Beitrag ---

You do not have permission to view link please Anmelden or Registrieren

Über diese Seite habe ich festgestellt, dass meine obere Query noch nicht ganz ausgereift ist. Es können noch Werte "dazwischenspringen" und demnach das Ergebnis beträchtlich verfälschen.

Testquery auf der Seite war:
[src=mysql]SELECT t1.`CustomerID` as id1, t2.`CustomerID` as id2, t3.`CustomerID` as id3, t4.`CustomerID` as id4, t1.`CustomerName` AS startpoint, t3.`CustomerName` AS endpoint, t1.Country, t2.Country, t3.Country, t4.Country
FROM `Customers` AS t1, `Customers` AS t2, `Customers` AS t4, `Customers` AS t3
WHERE t1.`CustomerID` = t2.`CustomerID`-1 AND t2.`Country` = t4.`Country` AND t3.`CustomerID` = t4.`CustomerID`+1 AND t2.CustomerID <> t3.CustomerID AND t1.CustomerID < t3.CustomerID AND t1.`Country` <> t2.`Country` AND t3.`Country` <> t4.`Country` AND t2.Country = "Mexico"[/src]

 

[drfuture]

Ist die Liste länger? Oder nur diese Werte, also ein Start, ein Ende und Werte dazwischen?
Wenn mehrere, wie unterscheidet sich Vorgang 1 von 2, also wie erkennst du im Zweifel ob Anfang oder Ende m
Wenn es nur ein Start ist würde ich eine Liste *gültiger* Werte erstellen indem ich in einem select nur die ids ausgebe bei denen ein Countdown auf den Wert kleiner 2 ist. Dann im äußeren select ein where in(select...)

Am Handy ist es gerade etwas schlecht einen echten query zu bauen

Ob das dann unter dem Strich Effektiver ist müsste man messen..

 

[Roin]

Also ich habe eine Liste mit einigen Zehntausend Werten (gerne auch mal eine Millionen).
Und "mainvalue" ist beispielsweise ein Sensorinput oder Ähnliches. Und dann ist, auf das obrige Beispiel bezogen, alles i.O., was kleiner als 15 aber größer als 14 ist.
Also 15 > x > 14 ist als ok anzusehen. Alles andere als Fehler. Und nun möchte ich Beginn der "Störung" und Dauer der Störung ausgegeben haben.
Mit den Daten muss das ja möglich sein.
Aber irgendwie nicht so einfach...

Der zweite Teil meines ersten Posts gibt zum Beispiel auch Werte aus, die nicht in unmittelbarer nähe zueinander liegen. Da weiß ich aber auch nicht, wie ich das wegbekommen soll ...

Um die Auswertung allgemein aber zu vereinfachen, möchte ich mit meiner Query nur Beginn und Ende eines Wertes erhalten. Ob der Wert, der dann die Zwischenzeit eingestellt ist "ok" ist oder nicht soll dann die nachgelagerte Logik klären. So bleibe ich deutlich flexibler in der Anwendung.

Ich hatte gerade ja sogar noch ein GROUP im Kopf, allerdings passiert das ja nicht so, wie ich das möchte...

 

[werner]

Hilft dir vielleicht das weiter? Hört sich doch ähnlich an:

You do not have permission to view link please Anmelden or Registrieren

Dann den letzten und ersten Timestamp der zusammengefassten Reihe nutzen, um die Dauer auszulesen (Anwendungsseite)

 

[drfuture]

Hm wenn ich deine Aufgabenstellung richtig verstehe ist dann das Beispiel oben nicht etwas schlecht? - dann ist hier ja der Beginn + Ende die erste Zeile - sprich als Ergebnis dürftest du bei dem Beispiel oben nur genau ein Ergebnis bekommen?

 

[Roin]

[TABLE="class: grid"]
[TR]
[TD]ID[/TD]
[TD]Timepoint[/TD]
[TD]mainvalue[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2017-07-19 21:42[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017-07-19 21:43[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017-07-19 21:44[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2017-07-19 21:46 (achtung!)[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2017-07-19 21:46[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2017-07-19 21:47[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2017-07-19 21:48[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2017-07-19 21:49[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2017-07-19 21:50[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2017-07-19 21:51[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2017-07-19 21:52[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2017-07-19 21:53[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2017-07-19 21:54[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2017-07-19 21:55[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]2017-07-19 21:56[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2017-07-19 21:57[/TD]
[TD]true[/TD]
[/TR]
[/TABLE]

Damit haben wir ein anderes Beispiel. Wie zu beachten ist, die Zeitpunkte sind nicht eindeutig zu den IDs. Es können auch mal mehrere Werte pro Zeitpunkt vorkommen oder auch mal einige Zeit lang kein Wert aufgenommen worden sein.

Output sollte etwa folgendes sein:
[TABLE="class: grid"]
[TR]
[TD]Anfangszeitpunkt[/TD]
[TD]Endzeitpunkt[/TD]
[TD]Dauer (optional)
[/TD]
[TD]Status (optional)[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:42[/TD]
[TD]2017-07-19 21:42[/TD]
[TD]1[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:43[/TD]
[TD]2017-07-19 21:46[/TD]
[TD]4[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:47[/TD]
[TD]2017-07-19 21:49[/TD]
[TD]3[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:50[/TD]
[TD]2017-07-19 21:51[/TD]
[TD]2[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:52[/TD]
[TD]2017-07-19 21:53[/TD]
[TD]2[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:54[/TD]
[TD]2017-07-19 21:56[/TD]
[TD]3[/TD]
[TD]false[/TD]
[/TR]
[TR]
[TD]2017-07-19 21:57[/TD]
[TD]2017-07-19 21:57[/TD]
[TD]1[/TD]
[TD]true[/TD]
[/TR]
[/TABLE]

Dabei sei die erste und letzte Spalte optional (da keine Daten davor und keine Daten dahinter vorhanden sind).

Alternativ sollte die Query auch dahingehend erweitert werden, dass nur das letzte derartige Ergebnis ausgegeben wird (im Zweifel könnte man das aus dem Ergebnis-Array bestimmen).
Die dritte Spalte (Dauer) kann aus den bestimmten Daten sehr einfach errechnet werden - daher nur eine Option, diese anzugeben. Der Status (Spalte vier) ist ebenfalls optional aber es wäre schön, wenn man diesen dennoch ausgeben könnte.

Meine Querys sind leider noch falsch und ich bin mir noch nicht sicher, woran es liegt bzw. weiß nicht, wie ich diese Verbessern kann.

EDIT

Hilft dir vielleicht das weiter? Hört sich doch ähnlich an:

You do not have permission to view link please Anmelden or Registrieren

Da hast du Recht. Das hört sich sehr ähnlich an. Das schaue ich mir grad Mal näher an. Scheint fast so, als ob ich das Beispiel umsonst erweitert habe
Dabei habe ich deinen Post schlichtweg überlesen

EDIT2:
Das ist anscheinend genau das was ich suche. Allerdings muss ich zugeben, dass ich die Query nicht so wirklich verstehe. Kann mir die jemand noch einmal erklären?

[src=mysql]@lastSeq := if( t.action = @lastAction AND
t.postID = @lastPostID AND
t.postType = @lastPostType, @lastSeq, @lastSeq +1 ) as ActionSeq[/src]
Wenn die Bedingung, dass sich nichts geändert hat, erfüllt ist, dann nehme die bisherige Seq (lastSeq) sonst inkrementiere um eins.

[src=mysql]( select @lastAction := ' ',
@lastPostID := 0,
@lastPostType := ' ',
@lastSeq := 0 ) sqlVars[/src]
Erzeuge Werte - lastSeq = 0 - ... Sozusagen eine Initialisierung der Variablen

[src=mysql]@lastSeq := if(..., @lastSeq, @lastSeq +1 ) as ActionSeq,
@lastAction := t.action,
@lastPostID := t.postID,
@lastPostType := t.PostType[/src]
Passe bisherige Variablen an die neuen Werte der jeweiligen Zeile an.

[src=mysql]group by
PreQuery.postID,
PreQuery.ActionSeq,
PreQuery.PostType,
PreQuery.Action[/src]
Fasse alle Werte zusammen, die u.A. die gleiche ActionSeq haben --> Es hat sich in diesen Zeilen keiner der betrachteten Werte geändert.

Alles richtig verstanden? Oder habe ich da einen groben Fehler drin?

EDIT 3
Meine Query müsste dann in etwa so aussehen:
[src=mysql]select
PreQuery.mainvalue,
min( PreQuery.timepoint ) as Startzeitpunkt,
max( PreQuery.timepoint ) as Endzeitpunkt,
TIMESTAMPDIFF(SECOND, min( PreQuery.timepoint ), max( PreQuery.timepoint )) as Dauer,
count(*) as AnzahlWerte
from
( select
t.*,
@lastSeq := if( t.mainvalue = @lastMainvalue, @lastSeq, @lastSeq +1 ) as ActionSeq,
@lastMainvalue := t.mainvalue
from
t,
( select @lastMainvalue := ' ',
@lastSeq := 0 ) sqlVars
order by
t.timepoint ) PreQuery
group by
PreQuery.ActionSeq,
PreQuery.mainvalue
[/src]

You do not have permission to view link please Anmelden or Registrieren