SELF JOIN - Beispiel konstruieren

[Jürgen Thomas]

Hallo,

ich befasse mich zz. etwas grundsätzlicher mit SQL und mögliche jetzt zum SELF JOIN sinnvolle Beispiele basteln. So könnten doppelte Datensätze gefunden werden; bei Andreas Kosch finde ich wiederholt die Berechnung der Differenz von km-Ständen.

Bei einer "eigenen Situation" komme ich nicht auf die zündende Idee. Ich habe eine Tabelle Schadensfaelle, bei der eine Versicherung für jedes Fahrzeug die Vorfälle (zusammen mit Schadenshöhe usw.) registriert. Diese Tabelle enthält auch ein Feld Datum. Ich möchte jetzt die Abstände zwischen den Vorfällen für ein bestimmtes Fahrzeug bestimmen. (Die ganzen WHERE-Bedingungen kann ich mir selbst zusammenreimen.)

Das folgende genügt natürlich noch nicht:

SQL-Code:

select a.Datum, b. Datum, a.Datum - b.Datum
from Schadensfaelle a
join Schadensfaelle b on b.id > 0  // nur fiktiv, damit eine Bedingung notiert ist
where a.Datum > b.Datum

Dies liefert zu viele Informationen:

Code:

DATUM           DATUM1       SUBTRACT
03.06.2008      01.02.2008        123
13.09.2008      01.02.2008        225  //  diese Zeile stört
13.09.2008      03.06.2008        102

Gibt es einen einfachen Trick, sodass sowohl bei a.Datum als auch bei b.Datum jedes Datum nur einmal auftaucht und die zeitliche Reihenfolge eingehalten wird?

Recht herzlichen Dank! Jürgen

[mkinzler]

Distinct oder wenn du festlegen willst welches Datum genommen wird ein MIN/MAX und Gruppierung

[Jürgen Thomas]

DISTINCT klingt gut, danke! Jürgen

[DeddyH]

MIN erscheint mir aber geeigneter. Du willst ja den kürzesten zeitlichen Abstand, oder nicht?

[Jürgen Thomas]

So, ich habe jetzt alles ausprobiert und eure Tipps berücksichtigt und habe das gewünschte Ergebnis erhalten:

SQL-Code:

select a.Datum, b.Datum, a.Datum - b.Datum
from Schadensfaelle a
join Schadensfaelle b 
  on b.Datum = ( select max(c.Datum) from Schadensfaelle c where a.Datum > c.Datum )

MAX deshalb, weil aus c das größte Datum gesucht wird, das kleiner als a.Datum ist.

Code:

DATUM           DATUM1       SUBTRACT
03.06.2008      01.02.2008        123
13.09.2008      03.06.2008        102

Es gefällt mir noch nicht, dass ich die Tabelle wegen der MAX-Bestimmung noch ein drittes Mal einbinden muss. Die Geschwindigkeit beeinträchtigt das wohl nicht, da dürfte jedes DBMS "intelligent" genug sein; aber die Übersichtlichkeit leidet doch etwas.

Gibt es noch bessere Vorschläge?

Danke jedenfalls

[DeddyH]

Ausgehend von der Annahme, dass in der Tabelle auch die Fahrzeug-Id hinterlegt ist, könnte man das so machen:

SQL-Code:

select a.Fahrzeug_Id, a.Datum, b.Datum, MIN(a.Datum - b.Datum)
from Schadensfaelle a
join Schadensfaelle b
  on b.Fahrzeug_Id = a.Fahrzeug_Id
group by a.Fahrzeug_Id, a.Datum, b.Datum

[Jürgen Thomas]

Hallo Detlef,

klappt leider genausowenig wie meine früheren Versuche. Zum einen muss noch "where a.Datum > b.Datum" hinzugefügt werden; es kommt aber immer die Ausgabe mit 3 Zeilen (wie oben), weil die MAX-Bedingung fehlt.

Die Fahrzeug_ID steht in einer anderen Tabelle, die alle Fahrzeuge zu einem Schadensfall verknüpft; aber das können wir im Moment vernachlässigen und so tun, als stünde sie direkt zur Verfügung.

Weitere Ideen? Jürgen

[DeddyH]

Nun hab ich sowas ähnliches wie Du (wobei man vermutlich die Join-Bedingung weglassen kann, ich hab es nicht getestet):

SQL-Code:

SELECT A.Datum, B.Datum, MIN(A.Datum - B.Datum)
FROM Schadensfaelle A
JOIN Schadensfaelle B
  ON A.Datum > B.Datum
GROUP BY A.Datum, B.Datum
HAVING B.Datum = (
   SELECT MAX(Datum) FROM Schadensfaelle
   WHERE Datum < A.Datum)

[edit] Das MIN kann so vermutlich auch entfallen, einfach mal ausprobieren. [/edit]

[Jürgen Thomas]

Hallo Detlef,

diese Lösung ist faktisch identisch mit meiner in #5. Das MIN kann in der Tat entfallen. Ich hatte die MAX-Abfrage in die ON-Bedingung eingebaut, während du sie in die HAVING-Klausel verschoben hast. Da dürfte mein Verfahren noch etwas effektiver sein (auch wenn es nicht so schön aussieht und der Feststellung in meiner anderen Diskussion widerspricht, was zur ON-Bedingung und was zur WHERE-Bedingung gehören soll).

Auf das JOIN kann sicher nicht verzichtet werden (ich wüsste auch nicht wie), weil ich ein und dasselbe Feld zweimal aus verschiedenen Datensätzen benötige.

Wenn es keine wesentlichen neuen oder besseren Vorschläge gibt, können wir es wohl dabei belassen.

Danke! Jürgen

[DeddyH]

Ich meinte nicht den Join an sich, sondern dessen Bedingung, da diese ja quasi durch die HAVING-Klausel sowieso hineinkommt.

[alzaimar]

Vielleicht mal ohne Self Join?

SQL-Code:

Select A.Datum, 
      (Select Min (B.Datum - A.Datum) 
         From Schadensfaelle B 
        Where B.Datum>A.Datum) as AbstandZumNaechstenSchadensfall
  From Schadensfaelle A

[DeddyH]

Da fehlt aber noch B.Datum ;)

[alzaimar]

Wozu braucht man denn B.Datum, wenn sich das aus A.Datum + Differenz ergibt und es berechnete Felder in Delphi gibt und *lufhol* überhaupt?
Aber bitte :mrgreen: :

SQL-Code:

Select X.Datum as A-Datum,
       X.Datum+AbstandZumNaechstenSchadensfall as B-Datum,
       X.AbstandZumNaechstenSchadensfall  
From (
  Select A.Datum,
      (Select Min (B.Datum - A.Datum)
         From Schadensfaelle B
        Where B.Datum>A.Datum) as AbstandZumNaechstenSchadensfall
  From Schadensfaelle A
) X

[DeddyH]

Er möchte in seiner Ergebnismenge die beiden Daten sowie die Differenz haben.

[Jürgen Thomas]

Und ich möchte ein Beispiel für SELF JOIN konstruieren (so steht es auch im Titel)! Jedenfalls vom Thema und der Theorie her ist meine Problemstellung dafür schließlich geeignet, oder etwa nicht? Ein und dasselbe Feld aus zwei verschiedenen Datensätzen derselben Tabelle - das ist doch der Kern von SELF JOIN?!

Für die Fragestellung "Abstände zwischen den Vorfällen" genügt eigentlich die Differenz. Aber in der Ergebnistabelle sagt das nicht allzuviel aus; deshalb sollten beide Datumsangaben mit zurückgeliefert werden. Alles klar?

Jürgen

[Apollonius]

Ich würde von Detlefs Code in #6 ausgehen. So wie ich das sehe, wird lediglich einmal zu viel gegroupt. Wenn man nur nach der Fahrzeug-ID und a.Datum gruppiert und zusätzlich noch where a.Datum > b.Datum einfügt, müsste das klappen; statt b.Datum muss dann natürlich MAX(b.Datum) selektiert werden.

SQL-Code:

select a.Fahrzeug_Id, a.Datum, MAX(b.Datum), MIN(a.Datum - b.Datum)
from Schadensfaelle a
join Schadensfaelle b
  on b.Fahrzeug_Id = a.Fahrzeug_Id
where a.Datum > b.Datum
group by a.Fahrzeug_Id, a.Datum

[Jürgen Thomas]

Klappt, danke! Jürgen

[alzaimar]

Zitat:

Zitat von Jürgen Thomas

Ein und dasselbe Feld aus zwei verschiedenen Datensätzen derselben Tabelle - das ist doch der Kern von SELF JOIN?!

Ich dachte immer, das Problem auf elegante und einfach Art zu lösen, ist der Kern.

Als Paradebeispiel für Self joins würde ich rekursive Tabellen nehmen, bei denen also eine Spalte ein FK auf den PK der Tabelle (Arbeiter->Vorgesetzter). Da bleibt einem nichts anderes übrig, als ein Self Join zu nehmen.

[Jürgen Thomas]

Zitat:

Zitat von alzaimar

Ich dachte immer, das Problem auf elegante und einfach Art zu lösen, ist der Kern.

Bitte verstehe mich nicht falsch: Grundsätzlich stimme ich dir natürlich zu. In meiner konkreten Situation (ich vervollständige auf WikiBooks Einführung in SQL) ging es mir wirklich ausdrücklich und ausschließlich um ein konstruiertes Beispiel für Self-Join.

Zitat:

Als Paradebeispiel für Self joins würde ich rekursive Tabellen nehmen, bei denen also eine Spalte ein FK auf den PK der Tabelle (Arbeiter->Vorgesetzter). Da bleibt einem nichts anderes übrig, als ein Self Join zu nehmen.

Danke, das werde ich noch berücksichtigen.

Danke jedenfalls nochmals für alle Hinweise! Jürgen