SQL MySQL durchschnittlicher Zeitabstand

[DDD92]

Sehr geehrte Forenmitglieder,

ich habe derzeit ein Problem mit der Ermittlung der zeitlichen Dauer zwischen 2 Datenbankeinträgen. In meine Tabelle habe ich eine Spalte wechseldatum mit dem Datentyp DATETIME deklariert, in die ich das aktuelle Datum der Erstellung des Eintrages übergebe. Nun möchte ich die durchschnittliche Zeit, die zwischen den jeweilig nacheinander folgenden Einträgen der Tabelle liegt ermitteln und ausgeben. Ich habe es schon mit AVG probiert, aber da das ein arithmetischer Befehl ist, bekomme ich unrealistische Zahlen (da ja Minute/Sekunde/.. ja nur bis 60 und nicht 100 geht).'
Gibt es direkt in einer MySQL-Abfrage die Möglichkeit einer Schleife, die durch die ganze Spalte hinweg DATEDIFF ausführt, den Wert (in Stunden z.B.) addiert und dann durch die Summe der Einträge teilt, um so die durchschnittliche Dauer zwischen den Einträgen ermittelt?

Grüße Dirk

 

[Loopman]

Kann man das nicht einfach mit "between" machen?

Edit: Achso, nachher der Durchschnitt....

Edit2: Ich glaube das gibt es nicht.

 

[rg88]

Zum rechnen eignet sich ein timestamp viel besser.
Würde da eine View oder Hilfstabelle anlegen

SET B.date_added = FROM_UNIXTIME(A.date_added)
WHERE A.id=B.id

 

[Andreas_]

Ich habe es schon mit AVG probiert, aber da das ein arithmetischer Befehl ist, bekomme ich unrealistische Zahlen (da ja Minute/Sekunde/.. ja nur bis 60 und nicht 100 geht).'

Das Problem wird sogar in der Doku beschrieben, zusammen mit einer Lösungsmöglichkeit:

The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

 

[alxtraxxx]

TIMEDIFF mit TIME_TO_SEC nutzen, man nutze das Handbuch ider die fertigen Beispiele die es überall auf Google gibt

 

[DDD92]

Tut mir leid, dass ich jetzt erst antworte, aber ich war beruflich viel unterwegs...

"Between" höre ich jetzt zum ersten mal. :\

Ich hatte den Datentyp auch erst am TIMESTAMP, aber der geht ja nur bis ca. 2030 und ich will dahingehend keine Einschränkungen haben.

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
2.SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

Diese Befehle sehe ich zum ersten mal jetzt und ich habe viel gegoogelt, um einen Forumeintrag zu vermeiden, wenn es auf anderen Seite bereits ein passende Lösung gibt. :/
Ich werde versuchen, das in meiner Anwendung zu implementieren.
Vielen Dank auf jeden Fall für jeden Hilfe!!

 

[alxtraxxx]

Bis 2030 wird der bestimmt erweitert oder einfach genullt, hab mich nicht mit beschäftigt. Wenn Deine Intervalle zwischen den Einträge nicht wahnsinnig groß z.B. alles am gleichen Tag sind wird die Umstellung Dich vermutlich auch maximal 1 Tag kosten.
Falls die Tabelle groß wird sollten solche Statistiken auch eher ausgelagert werden.
In der Regel hat man dafür dann Extratabellen sofern die genutzte DB-Engine/Frontend so etwas nicht können.
In Deinem Fall könnte man in einer Tabelle immer die Zeit pro 2 Abfragen speichern, dann wäre die Auswertung einfacher.

 

[wiztm]

Es soll doch ein Schnitt ausgegeben werden. Also die Differenz zwischen ersten Eintrag und letztem Eintrag durch die Anzahl der Einträge, oder nicht?
Also erst mal das Intervall bilden. z.B. in Sekunden. Dann durch die Anzahl dividieren:

SELECT TIMESTAMPDIFF(SECOND, min(wechseldatum), max(wechseldatum)) / count(*) as avg FROM lebensdauer;

 

[Andreas_]

Also erst mal das Intervall bilden. z.B. in Sekunden. Dann durch die Anzahl dividieren:

SELECT TIMESTAMPDIFF(SECOND, min(wechseldatum), max(wechseldatum)) / count(*) as avg FROM lebensdauer;

Was hat Deine Berechnung mit dem Durchschnitt zu tun, den der TE haben möchte?

 

[wiztm]

Wenn die Tabelle fortlaufend gefüllt wird, einiges?
​Deswegen frag ich ja.

 

[Andreas_]

Wenn die Tabelle fortlaufend gefüllt wird, einiges?

Du teilst die Spannweite durch die Anzahl der Datensätze, das hat mit Durchschnitt nichts zu tun.

Beispiel mit 3 Werten: 3, 4, 5

Durchschnitt (arithmetisches Mittel): (3+4+5)/3 = 4

Deine Rechenmethode: (5-3)/3 = 2/3

 

[rg88]

@Andreas: Du machst doch hier selbst nen Fehler, glaub ich.. Es interessiert ja der Durchschnitt der Abstände.

Also: (diff(3,4) + diff(4,5)) / (anzahl -1)
=> (1 + 1) / (3-1) => 2/2 => 1

Damit hat an dann den mittleren Zeitabstand der einzelnen Einträge. Oder hab ich mich jetzt vertan?

 

[alxtraxxx]

Wenn immer paarweise gezählt wird ist es aber nur die halbe Anzahl und man könnte gleich /2 machen statt irgendwas durch Anzahl zu teilen

 

[rg88]

Verstehe deinen Gedankengang nicht, alx.

 

[wiztm]

Wenn ich von 20 Uhr bis 24 Uhr Messungen vorgenommen habe und nun wissen möchte wie viel Zeit im Schnitt zwischen den Messungen vergangen ist nehme ich nun mal (24-20) / Anzahl der Messungen.
​Damit kenn ich die Intervalllänge.

 

[alxtraxxx]

diff(3,4) + diff(4,5)) / (anzahl -1)
=> (1 + 1) / (3-1) => 2/2 => 1

Hier ist die Anzahl 2 nicht 4 denn man hat nur 2 Paare.
Allerdings hat der TE ja sein zwischen 2 Datenbankeinträgen nicht genauer definiert. Ich meinte damit Anzahl Aller Records in der Tabelle/2, um Missverständnissen vorzubeugen.Wenn er witzm's Lösung gemeint hätte wäre die Angabe der 2 ja unnötig gewesen. Witzm zählt wenns um Paare gehen würde auch die Pausen mit.

 

[Andreas_]

@Andreas: Du machst doch hier selbst nen Fehler, glaub ich.. Es interessiert ja der Durchschnitt der Abstände.

Der Durchschnitt der Abstände zu einem unbekannten Ausgabedatum. Wenn das Ausgabedatum gleich ist, kann man auch direkt den Durchschnitt der Wechseldaten benutzen.

Also: (diff(3,4) + diff(4,5)) / (anzahl -1)
=> (1 + 1) / (3-1) => 2/2 => 1

Damit hat an dann den mittleren Zeitabstand der einzelnen Einträge. Oder hab ich mich jetzt vertan?

Der mittlere Zeitabstand würde auch den Abstand zwischen 3 und 5 berücksichtigen und nicht nur den Abstand zwischen zwei aufeinanderfolgenden Werten ... Ich kann mir keine sinnvolle Anwendung für den "mittleren Zeitabstand" vorstellen, in der Regel interessiert die durchschnittliche Zeitdauer.

Ergänzung (16. August 2017)

Wenn ich von 20 Uhr bis 24 Uhr Messungen vorgenommen habe und nun wissen möchte wie viel Zeit im Schnitt zwischen den Messungen vergangen ist nehme ich nun mal (24-20) / Anzahl der Messungen.
​Damit kenn ich die Intervalllänge.

Und was bringt Dir diese Intervalllänge?

Mal ein Beispiel mit Bezug zum Ursprungsposting: Ein Teil wird nach 10 Tagen, zwei Teile nach 20 Tagen, ein Teil nach 30 Tagen ein weiteres Teil nach 100 Tagen gewechselt. Da hättest Du eine Intervalllänge von (100-10)/5=18. Wenn man aber die durchschnittliche Zeit bis zum Austausch haben möchte - arithmetisches Mittel: (10+2*20+30+100)/5=36

 

[alxtraxxx]

Ich kann mir keine sinnvolle Anwendung für den "mittleren Zeitabstand" vorstellen, in der Regel interessiert die durchschnittliche Zeitdauer.

Es gibt genug Szenarien, wenn mehrere Abfragen zu einer Transaktion gehören und ich wissen möchte wie lange eine Transaktion dauert will ich nicht die Zeiten drin haben zwischen der letzten Abfrage einer Transaktion und der ersten der nächsten. Genauso wie es Mittelwerte gibt bei denen man die 0 nicht mitzählt weil sie sinnfrei im Kontext ist.
Wenn Du halt nur einfache Szenarien kennst sind deshalb andere nicht sinnfrei.
Kommt immer darauf an was man auswerten will. Will man die Gesamtreisezeit in km/h umrechnen müssen die Pausen rein, möchte man wissen welcher Durchnitt "gefahren" wurde müssen sie raus.

 

[wiztm]

Und was bringt Dir diese Intervalllänge?

Im Kontext zum Ersteller wissen wir das nicht.
Wir werden erst näheres erfahren falls er sich noch einmal meldet.

 

[Andreas_]

Es gibt genug Szenarien, wenn mehrere Abfragen zu einer Transaktion gehören und ich wissen möchte wie lange eine Transaktion dauert will ich nicht die Zeiten drin haben zwischen der letzten Abfrage einer Transaktion und der ersten der nächsten. Genauso wie es Mittelwerte gibt bei denen man die 0 nicht mitzählt weil sie sinnfrei im Kontext ist.
Wenn Du halt nur einfache Szenarien kennst sind deshalb andere nicht sinnfrei.

Mein Szenario bezieht sich auf die wenigen vom TE gemachten Angaben und sind nicht frei aus der Luft gegriffen wie Deine ...

Ich spekuliere nicht darüber, was Du kennst und was nicht, sondern was zu den gemachten Angaben passt.