Dauer zwischen zwei Einträgen berechnen in der SELECT Abfrage?

[Skin]

ich brauch mal einen Anstoss in die Richtig Richtung. Ich rufe über meine mysql Datenbank alle relevanten Daten ab, darunter auch die jeweilige Uhrzeit zum Eintrag.
dann gebe ich alles in einer while Schleife aus. Wenn ich aber den Zeitraum zwischen zwei Einträgen berechnen will, habe ich diesen erst immer einen Schritt später als ich ihn brauche.

Beispiel

Ausgabe:
1. Eintrag, Zeitpunkt, (Hier möchte ich die DAUER hin haben zwischen 1 und 2)
2. Eintrag, Zeitpunkt, (Hier möchte ich die DAUER hin haben zwischen 2 und 3)
3. Eintrag, Zeitpunkt, (Hier möchte ich die DAUER hin haben zwischen 3 und 4)
4. Eintrag, Zeitpunkt, (Hier möchte ich die DAUER hin haben zwischen 4 und 5)

ich kann aber erst immer ein Zeile später, die Dauer berechnen.

Kann ich die Dauer zwischen zwei EINTRÄGE schon in meiner SELECT Abfrage berechnen?

Skin

[Tom]

Hello,

Kann ich die Dauer zwischen zwei EINTRÄGE schon in meiner SELECT Abfrage berechnen?

Das kannst Du, aber natürlich nur dann, wenn der jeweilige Eintrag auch einen Nachfolger (oder Vorgänger) hat.

Schau Dir mal die Beispiele zu den Benutzerdefinierten Variablen in MySQL an. Die sind dafür bestens geeignet. http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Kann ich die Dauer zwischen zwei EINTRÄGE schon in meiner SELECT Abfrage berechnen?
Das kannst Du, aber natürlich nur dann, wenn der jeweilige Eintrag auch einen Nachfolger (oder Vorgänger) hat.
Schau Dir mal die Beispiele zu den Benutzerdefinierten Variablen in MySQL an. Die sind dafür bestens geeignet. http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Nein, nicht wenn ein nachfolgender Satz beim vorherigen berücksichtigt werden soll - nur umgekehrt. Apropos umgekehrt - Sortierreihenfolge umdrehen, Berechnung zum Vorgänger, der ja eigentlich Nachfolger ist, durchführen und anschließend wieder wie gewünscht sortieren.

Lo!

[Tom]

Hello,

Kann ich die Dauer zwischen zwei EINTRÄGE schon in meiner SELECT Abfrage berechnen?

Das kannst Du, aber natürlich nur dann, wenn der jeweilige Eintrag auch einen Nachfolger (oder Vorgänger) hat.

Schau Dir mal die Beispiele zu den Benutzerdefinierten Variablen in MySQL an. Die sind dafür bestens geeignet. http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Das geht schon. Man muss nur erst den Satz finden, kann dann damit den Nachfolger bestimmen und die relevanten Dinge merkt man sich dann eben in den benutzerdefinierten Variablen.

Es geht mit einem Subselect und einer Limit-Klausel.

Aus dem Handgelenk bekomme _ich_ das jetzt nicht hin, weiß aber, dass wir es hier schon mal gemeinsam gelöst haben, mit _einem_ Select (mit Subselect) einen Nachfolger zu finden. Das Ausrechnen der Differenz ist dann ja nur noch ein Witz.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Das geht schon. Man muss nur erst den Satz finden, kann dann damit den Nachfolger bestimmen und die relevanten Dinge merkt man sich dann eben in den benutzerdefinierten Variablen.
Es geht mit einem Subselect und einer Limit-Klausel.

Ja, aber das ist umständlich, weil dieses Subselect für jeden Datensatz ausgeführt werden muss. Insgesamt zweimal umsortieren und einmal gerade durchlaufen ist sicher auch für das DBMS einfacher.

Aus dem Handgelenk bekomme _ich_ das jetzt nicht hin, weiß aber, dass wir es hier schon mal gemeinsam gelöst haben, mit _einem_ Select (mit Subselect) einen Nachfolger zu finden. Das Ausrechnen der Differenz ist dann ja nur noch ein Witz.

Das wäre: alles wählen, was größer als der aktuelle Wert ist, aufsteigend sortieren und auf 1 limitieren. Das setzt voraus, dass die Werte, nach denen sortiert wird, einmalig sind.

Lo!

[Tom]

Hello,

Das wäre: alles wählen, was größer als der aktuelle Wert ist, aufsteigend sortieren und auf 1 limitieren. Das setzt voraus, dass die Werte, nach denen sortiert wird, einmalig sind.

Wenn man nur den Abstand zu dem/den nächsten haben will, ist das ja erstmal egal, wieviele auf den nächsten vorhandenen Wert fallen.

Sonst muss man eben gruppieren. Da wird es dann aber schon sehr speziell und der OP sollte erstmal genauere Angaben machen, was er vor hat.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Das wäre: alles wählen, was größer als der aktuelle Wert ist, aufsteigend sortieren und auf 1 limitieren. Das setzt voraus, dass die Werte, nach denen sortiert wird, einmalig sind.
Wenn man nur den Abstand zu dem/den nächsten haben will, ist das ja erstmal egal, wieviele auf den nächsten vorhandenen Wert fallen.

Kommt drauf an, wie "nächster" definiert ist - nächster Datensatz oder nächsthöherer Wert. Wenn es Timestamps sind, und es davon zwei gleiche gibt (die sich nur in unwichtigen Millisekunden unterscheiden), will man vielleicht einmal 0 als Differenz haben sowie einmal den Abstand zum nächsten, nicht zweimal die Diffenz zum nächsthöheren Wert. Dann braucht man für das Subselect zwei Sortierkriterien, um wirklich den nächsten zu erwischen, auch wenn der Wert gleichgeblieben ist - gut, braucht man beim hin- und hersortieren auch.

Lo!

[Tom]

Hello,

Es geht mit einem Subselect und einer Limit-Klausel.

Aus dem Handgelenk bekomme _ich_ das jetzt nicht hin, weiß aber, dass wir es hier schon mal gemeinsam gelöst haben, mit _einem_ Select (mit Subselect) einen Nachfolger zu finden. Das Ausrechnen der Differenz ist dann ja nur noch ein Witz.

Das ist nicht genau das, was Skin wollte, aber die Richtung passt schon.

http://forum.de.selfhtml.org/archiv/2009/7/t188873/#m1258243

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[ChrisB]

Hi,

dann gebe ich alles in einer while Schleife aus. Wenn ich aber den Zeitraum zwischen zwei Einträgen berechnen will, habe ich diesen erst immer einen Schritt später als ich ihn brauche.

Dann mache andere „Schritte“.

Ausgabe:

1. Eintrag, Zeitpunkt, (Hier möchte ich die DAUER hin haben zwischen 1 und 2)
2. Eintrag, Zeitpunkt

Lies den zweiten Eintrag bereits ein, bevor du die Ausgabe für den ersten komplettierst.

MfG ChrisB

[Hubertus]

ich würde es so versuchen

SELECT a.id, a.eintrag, a.wann, IFNULL( SEC_TO_TIME( UNIX_TIMESTAMP( b.wann ) - UNIX_TIMESTAMP( a.wann ) ) , 0 ) AS differenz  
FROM daten a  
LEFT JOIN daten b ON ( a.id < b.id )  
GROUP BY a.id  
ORDER BY id

id eintrag wann differenz
1 Eintrag 1 2011-04-05 15:26:34 00:00:34
2 Eintrag 2 2011-04-05 15:27:08 00:03:00
3 Eintrag 3 2011-04-05 15:30:08 0

Viele Grüße
Hubertus

[Skin]

Hallo Hubertus,

erst mal danke für deine Hilfe (den anderen natürlich auch) aber leider hängst sich dein Code bei mir auf und tut somit leider nichts, aber um den Code trotzdem zu verstehen habe ich noch ein paar Fragen dazu.

Muss ich unbedingt eine ID Spalte haben, oder kann ich nicht auch

statt: LEFT JOIN daten b ON ( a.id < b.id )

neu: LEFT JOIN daten b ON ( a.date < b.date )

machen?

Und warum wird dann, wenn ich id habe/nehme id gruppiert

SELECT a.id, a.eintrag, a.wann, IFNULL( SEC_TO_TIME( UNIX_TIMESTAMP( b.wann ) - UNIX_TIMESTAMP( a.wann ) ) , 0 ) AS differenz  
FROM daten a  
LEFT JOIN daten b ON ( a.id < b.id )  
GROUP BY a.id  
ORDER BY id

Skin

[Skin]

Jetzt bin ich es nochmal, ich habe jetzt deinen CODE 1 zu 1 an meine Tabelle angepasst,
es läuft braucht die Abfrage dauerte 58.1154 sek. (Tabelle hat 28000 Einträge). Ich habe extra die Berechnung eingegrenzt (denke ich jedenfalls)

SELECT a.id, a.eintrag, a.date, IFNULL( SEC_TO_TIME( UNIX_TIMESTAMP( b.date ) - UNIX_TIMESTAMP( a.date ) ) , 0 ) AS differenz  
FROM daten a  
LEFT JOIN daten b ON ( a.id < b.id )  
WHERE a.date > '2011-02-24 00:00:0'  
GROUP BY a.id  
ORDER BY id

Skin

[dedlfix]

Hi!

Jetzt bin ich es nochmal, ich habe jetzt deinen CODE 1 zu 1 an meine Tabelle angepasst,
es läuft braucht die Abfrage dauerte 58.1154 sek. (Tabelle hat 28000 Einträge). Ich habe extra die Berechnung eingegrenzt (denke ich jedenfalls)

Und was ist mit einem der anderen Vorschläge?

SELECT a.id, a.eintrag, a.date, IFNULL( SEC_TO_TIME( UNIX_TIMESTAMP( b.date ) - UNIX_TIMESTAMP( a.date ) ) , 0 ) AS differenz

FROM daten a
LEFT JOIN daten b ON ( a.id < b.id )
WHERE a.date > '2011-02-24 00:00:0'
GROUP BY a.id
ORDER BY id

  
Dieser Self-Join erzeugt erst einmal einen riesigen Datenberg, weil für jeden Datensatz soviele neue erzeugt werden, wie es welche mit kleinerer ID gibt - macht bei 28000 mal eben 784 Millionen temporäre Datensätze - hast du dafür genug Arbeitsspeicher frei? Anschließend werden sie durch das GROUP BY eingedampft. Und so wie das Statement jetzt ist, kann es auch nur von MySQL ausgeführt werden, weil die anderen Systeme beim Gruppieren wesentlich strenger sind, was das Verwenden von Feldern in der SELECT-Klausel angeht, über die nicht gruppiert wurde.  
  
Mit dem Ansatz kommst du prinzipbedingt auf keinen grünen Zweig, weil du beim Join keine Limitierung auf einen Datensatz anwenden kannst. Da wird wohl eher noch der Ansatz mit dem Subselect nach dem Vorgänger schneller sein. Ich schätze, der Umsortier-und-User-Variablen-Vorschlag wird das Rennen machen. (Indexe auf den Sortierkriterien einerseits und den Vorgänger-Find-Kriterien andererseits sollten vorhanden sein.)  
  
  
Lo!