MYSQL: Berechnung über zwei Datensätze

[Michael R.]

Moin,

ich habe eine Datenbankabfrage in MYSQL die mir Datensätze nach Datum sortiert liefert.

Nun möchte ich in einem nummerischen Feld die Differenz zum jeweiligen nächsten Datensatz berechnen.

Dies mache ich zur Zeit mit einer Schleife über alle zurückgegebenen Datensätze in PHP.

Gibt es auch eine Möglichkeit dies direkt in der SQL-Abfrage durchzuführen?

Für hilfreiches Tipps wäre ich dankbar.

GRuß Michael

[dedlfix]

echo $begrüßung;

ich habe eine Datenbankabfrage in MYSQL die mir Datensätze nach Datum sortiert liefert.
Nun möchte ich in einem nummerischen Feld die Differenz zum jeweiligen nächsten Datensatz berechnen.
Gibt es auch eine Möglichkeit dies direkt in der SQL-Abfrage durchzuführen?

Nein, das dürfte nicht gehen. Es sei denn, du kannst gruppieren, und dann wüsste ich nicht, was du als Differenzfunktion verwenden könntest. Die Berechnung der Spalteninhalte der Ergebnismenge erfolgt vor der Sortierung. Insofern besteht keine Möglichkeit "vorhergehend" und "nachfolgend" beim Berechnen zur Verfügung zu haben. Was aber geht wäre eine Stored Procedure. Da könntest du einen Cursor auf die sortierte Datenmenge setzen, in Variablen die Feldinhalte zwischenspeichern, berechnen, in einer temporären Tabelle ablegen und deren Inhalt dann zurückliefern.

echo "$verabschiedung $name";

[Michael R.]

Nein, das dürfte nicht gehen.

Schade, aber deine Erklärung klingt ziemlich logisch.

Was aber geht wäre eine Stored Procedure.

Da muss ich mal mit beschäftigen, danke für das Stichwort.

GRuß Michael

[globe]

n'abend,

echo $begrüßung;

ich habe eine Datenbankabfrage in MYSQL die mir Datensätze nach Datum sortiert liefert.
Nun möchte ich in einem nummerischen Feld die Differenz zum jeweiligen nächsten Datensatz berechnen.
Gibt es auch eine Möglichkeit dies direkt in der SQL-Abfrage durchzuführen?

Nein, das dürfte nicht gehen.

Es ist - wie so oft - nur eine Frage der Widerlichkeit.

Mittels Joins / Subselects lässt sich für jeden Datensatz der in der Sequenz nächste Datensatz "anbinden" - und somit auch eine Differenz ermitteln.

/* bauen wir uns eine simple Tabelle */  
create table sequenz(  
  prike int(10) NOT NULL PRIMARY KEY auto_increment,  
  datum DATE  
);  
/* werfen wir ein bisschen Datenmüll rein */  
INSERT INTO sequenz (datum) VALUES  
('2008-09-19'),  
('2008-09-09'),  
('2008-08-30'),  
('2008-08-08'),  
('2008-08-08');

Auf unseren Demodaten können wir nun ein paar Abfragen fahren:

/* (Query01:) Folgedatensatz anhand des Feldes prike ermitteln, wobei immer der nächst höhere Wert angehängt werden soll. Lücken in der Sequenz sollen dabei keine Probleme machen */  
SELECT *  
  FROM sequenz s1  
  JOIN sequenz s2  
    ON ( s2.prike = (  
      SELECT MIN(prike)  
        FROM sequenz si  
       WHERE si.prike > s1.prike )  
       );

+-------+------------+-------+------------+  
| prike | datum      | prike | datum      |  
+-------+------------+-------+------------+  
|     1 | 2008-09-19 |     2 | 2008-09-09 |  
|     2 | 2008-09-09 |     3 | 2008-08-30 |  
|     3 | 2008-08-30 |     4 | 2008-08-08 |  
|     4 | 2008-08-08 |     5 | 2008-08-08 |  
+-------+------------+-------+------------+  

/* (Query02:) Die Anforderungen von Query01 erweitert um die Differenz der Datümser™ */  
SELECT *, DATEDIFF(s1.datum, s2.datum)  
  FROM sequenz s1  
  JOIN sequenz s2  
    ON ( s2.prike = (  
      SELECT MIN(prike)  
        FROM sequenz si  
       WHERE si.prike > s1.prike )  
       );

+-------+------------+-------+------------+------------------------------+  
| prike | datum      | prike | datum      | DATEDIFF(s1.datum, s2.datum) |  
+-------+------------+-------+------------+------------------------------+  
|     1 | 2008-09-19 |     2 | 2008-09-09 |                           10 |  
|     2 | 2008-09-09 |     3 | 2008-08-30 |                           10 |  
|     3 | 2008-08-30 |     4 | 2008-08-08 |                           22 |  
|     4 | 2008-08-08 |     5 | 2008-08-08 |                            0 |  
+-------+------------+-------+------------+------------------------------+

Wir haben an dieser Stelle nun die Anzahl der Tage, die zwischen den Datümsern™ zwischen den Datensätzen liegen, welche anhand des Feldes prike verbunden wurden.

Freilich können wir den jeweils nächsten Datensatz auch anhand des Datums ermitteln

/* (Query03:) Die Anforderungen von Query02 mit datum als relevantes Feld zur Verbindung mit dem nächsten Datensatz: */  
SELECT *, DATEDIFF(s1.datum, s2.datum)  
  FROM sequenz s1  
  JOIN sequenz s2 ON ( s2.datum = (  
      SELECT MIN(datum)  
        FROM sequenz si  
       WHERE si.datum > s1.datum ));  

+-------+------------+-------+------------+------------------------------+  
| prike | datum      | prike | datum      | DATEDIFF(s1.datum, s2.datum) |  
+-------+------------+-------+------------+------------------------------+  
|     2 | 2008-09-09 |     1 | 2008-09-19 |                          -10 |  
|     3 | 2008-08-30 |     2 | 2008-09-09 |                          -10 |  
|     4 | 2008-08-08 |     3 | 2008-08-30 |                          -22 |  
|     5 | 2008-08-08 |     3 | 2008-08-30 |                          -22 |  
+-------+------------+-------+------------+------------------------------+  

Wie wir erkennen können gibt es hier zwei "Probleme". Zum einen sind die Differenzen negativ, zum anderen zeigen die beiden Datensätze 4 und 5 auf den Datensatz 3. Das erste Problem bekommen wir mit ABS() (Absolutwert) in den Griff. Um das ganze besser zu illustrieren fügen wir nochmal ein bisschen Datenmüll ein: INSERT INTO sequenz (datum) VALUES ('2008-09-19'), ('2008-09-29'); Gegen das zweite Problem können wir ohne den Einsatz einer weiteren verknüpfungsrelevanten Spalte nicht lösen.

/* (Query04:) Die Anforderungen von Query03 mit datum als relevantes Feld zur Verbindung mit dem nächsten Datensatz: */  
SELECT *, ABS(DATEDIFF(s1.datum, s2.datum))  
  FROM sequenz s1  
  JOIN sequenz s2 ON ( s2.datum = (  
      SELECT MIN(datum)  
        FROM sequenz si  
       WHERE si.datum > s1.datum ));  

+-------+------------+-------+------------+-----------------------------------+  
| prike | datum      | prike | datum      | ABS(DATEDIFF(s1.datum, s2.datum)) |  
+-------+------------+-------+------------+-----------------------------------+  
|     2 | 2008-09-09 |     1 | 2008-09-19 |                                10 |  
|     3 | 2008-08-30 |     2 | 2008-09-09 |                                10 |  
|     4 | 2008-08-08 |     3 | 2008-08-30 |                                22 |  
|     5 | 2008-08-08 |     3 | 2008-08-30 |                                22 |  
|     2 | 2008-09-09 |     6 | 2008-09-19 |                                10 |  
|     1 | 2008-09-19 |     7 | 2008-09-29 |                                10 |  
|     6 | 2008-09-19 |     7 | 2008-09-29 |                                10 |  
+-------+------------+-------+------------+-----------------------------------+  

Die Spielchen könnten noch beliebig weiter getrieben werden, aber hier ruft ein Bett nach mir... :)

weiterhin schönen abend...

[Bademeister]

Hi Michael,
es dürfte gehen, wenn man denn will (aber ich halte es für nicht unbedingt empfehlenswert), in dem man die Tabelle mit sich selbst verknüpft und entsprechend gruppiert:

Ich gehe mal der Einfachheit halber davon aus, dass Datum eine Ganzzahl, etwa ein Unix-Timestamp, ist - ggf. musst Du das entsprechend anpassen.

Dann wäre das (ungetestete) Beispiel

SELECT
     T1.Datum,
     min(if(T2.Datum - T1.Datum >= 0, T2.Datum - T1.Datum, 2147483647))
FROM
     Datumstabelle T1, Datumstabelle T2
GROUP BY
     T1.Datum
ORDER BY
     T1.Datum ASC;

Ich hab einfach mal aufsteigend sortiert, geht nicht genau aus dem Posting hervor. Wenn außerdem das Datum in der Tabelle nicht UNIQUE ist, müsstest du stattdessen nach dem Primary Key o.ä. gruppieren, um nichts zu verlieren.

Die hässliche lange Zahl da wäre eine obere Schranke, die verhindert, dass negative Werte bei den Vergleichen das Minimum kaputt machen - soll ja nur mit den späteren Zeiten verglichen werden (die Zahl da ist das Maximum eines 32-Bit-Unix-Timestamps, hab ich gerade bei Wikipedia gelernt).

Das Ganze besticht aber erstens nicht durch Eleganz, wie ich finde, und zweitens ist es (wahrscheinlich) deutlich komplexer als Deine Version. Jenseits des Sortierens der Daten, das Du ja so oder so machst, ist die Komplexität für die Berechnung aller Abstände hier quadratisch in der Anzahl der Daten, weil das vor dem Sortieren passiert.

(Es sei denn, das DBMS hat eine sehr schlaue innere Optimierung, die die jeweiligen Minima anhand EINER vorsortierten Liste berechnet. Davon hab ich leider nicht viel Ahnung, aber mein Gefühl sagt mir, dass das zuviel verlangt ist.)

Das Berechnen der Abstände in PHP, wie Du es machst, geht natürlich in linearer Zeit. Ich weiß nicht, um wie viele Datensätze es geht, aber prinzipiell würde ich wohl am ehesten einfach dabei bleiben, es in PHP zu machen ;-)

Viele Grüße,
der Bademeister

[Michael R.]

Danke für die weiteren Lösungsvorschläge, jetzt bin ich mir wenigstens sicher keine einfache Lösung übersehen zu haben.