Join und Group By

[Forumsgast]

Hallo Community,

ich bin soeben auf ein Problem gestoßen, welches ich einfach nicht lösen kann. Ich habe versucht zu googlen, was aber sehr schwierig ist, da ich nicht weiß wie ich das Problem benennen soll. Daher wusste ich auch nicht, wie ich den Thread am besten benenne.
Vielleicht könnt ihr mir helfen

Es geht darum, dass ich ein Projekt zur Verwaltung der Ergebnisse von Sportveranstaltungen erstellt habe.
Nun habe ich vier verschiedene Tabellen:
1. 'zeiten'
2. 'sportler'
3. 'veranstaltungen'
4. 'strecken'

Die Tabelle Zeiten beinhaltet:
id,
sportlerid,
streckenid,
disziplin,
zeit,
veranstaltungsid


Es gibt nur eine bestimmte Anzahl an Disziplinen kombiniert mit den Streckenlängen, sodass ich die schnellsten Zeiten des einzelnen Sportlers in einer Tabelle anzeigen lasse.

Die Select-Abfrage dafür lautet:

SELECT z.disziplin, s.laenge, MIN(z.zeit), z.veranstaltungsid 
FROM zeiten AS z, 
strecken AS s, 
veranstaltungen as v 
WHERE z.sportlerid = 21 //entsprechende Sportlerid, nach der gesucht wird 
AND z.streckenid = s.id 
AND z.veranstaltungsid = v.id 
GROUP BY z.disziplin, s.laenge

Dies klappt auch soweit hervorragend, alle Zeiten werden passend angezeigt.
Was mir jetzt aber Sorgen bereitet, ist, dass die Veranstaltungsid nicht richtig mit abgefragt wird. Bei den ID's 1-10 funktioniert es. Wenn jedoch Zeiten von Veranstaltung 11 oder 12 angezeigt werden sollen, gibt er mir als ID's 1 bzw. 2 mit.

Ich hoffe, dass ich es einigermaßen verständlich erklärt habe, denn ich finde es selbst ziemlich merkwürdig.
Die ID's der Veranstaltungen sind richtig in der Tabelle zeiten eingetragen und wenn ich einen ähnlichen JOIN mache, bei dem jedoch nur eine bestimmte Zeit abgefragt wird, bekomme ich auch die richtige ID.

Habe ich etwas grundlegendes nicht verstanden oder hat das ganze einen anderen Grund?
Falls ihr noch weitere Details benötigt, fragt gerne nach.

Vielen Dank schon im Vorraus,
Surffreak

[Forumsgast]

Die Datenbank weiß nicht, dass sie z.veranstaltungsid wählen soll, wo z.zeit = MIN(z.zeit) ist. Sie wählt einfach „irgendeine“ Veranstaltungs-ID aus der Gruppe.

Ich würde spontan vorschlagen, SELECT z.disziplin, s.laenge, MIN(z.zeit) zu wählen und die Veranstaltungs-ID per JOIN zu ergänzen. Ich weiß aber nicht, ob das die beste Lösung ist.

Wenn du importierbare SQL-Testdaten posten könntest, kann ich dir so eine Query schreiben, aber aus dem Kopf kriege ich das glaube ich nicht fehlerfrei hin.

[Forumsgast]

Danke schonmal!
Ich verstehe zwar nicht genau, wieso die Datenbank nicht weiß, dass sie die VeranstaltungsID die zur Zeit gehört nehmen soll, aber vielleicht kommt das noch durch mehr Arbeit mit SQL

Ich habe die Tabellen vorbereitet, so dass du das ausprobieren kannst wenn du magst. Sie sind auf das minimalste gekürzt.
Am besten nimmst du zum Testen die Sportler ID 10.

[Forumsgast]

Das scheint doch eine Anwendung von within-group aggregates zu sein.

- http://www.artfulsoftware.com/infotree/queries.php#101

Ich denke, eine Möglichkeit wäre so:

SELECT
        z.disziplin,
        z.streckenid,
        z.zeit,
        z.veranstaltungsid
FROM
        zeiten AS z
INNER JOIN
    (
        SELECT
                subz.disziplin,
                subz.streckenid,
                MIN(subz.zeit) AS min_zeit
        FROM
                zeiten AS subz
        WHERE
                subz.sportlerid = 10
        GROUP BY
                subz.disziplin,
                subz.streckenid
    ) AS sub
    ON
        z.zeit = sub.min_zeit
    AND
        z.disziplin = sub.disziplin  
    AND
        z.streckenid = sub.streckenid      
ORDER BY
        z.disziplin ASC,
        z.streckenid ASC

Wenn du noch weitere Angaben mitliefern möchtest, müsstest du die noch an das äußere SELECT dranjoinen.

Hat ein Sportler eine identische Bestzeit in einer Disziplin auf einer Strecke mehrfach erreicht, sollten alle Einträge Teil der Ergebnismenge sein.

Die Schwierigkeit bei solchen Problemstellung ist für mich immer, zu erkennen, was alles nicht relevant ist.

[Forumsgast]

Danke mermshaus!
Dadurch habe ich jetzt auch verstanden, wo das Problem liegt und konnte ein bisschen gezielter rumprobieren..

Dein Lösungsansatz hat zwar Prima soweit funktioniert, jedoch finde ich den ganz schön kompliziert. Und da ich eigentlich immer verstehen will, was ich da auch mache, hab ich mir den Bereich auf der Seite die du dazugeschrieben hast mal angeschaut und mich schlaugemacht.

Würde demnach auch folgendermaßen Ansatz Sinn machen oder gibts da bezüglich der Geschwindigkeit oder sonstigem noch Probleme?

SELECT 
             *
FROM (
             SELECT 
                       z.sportlerid, 
                       s.laenge, 
                       z.disziplin, 
                       z.zeit, 
                       z.veranstaltungsid
             FROM 
                       `zeiten` AS z,
                       `strecken` AS s

             WHERE 
                        z.sportlerid = 10
                  AND
                        z.streckenid = s.id
                         
             ORDER BY 
                        z.zeit ASC
         ) AS sub
GROUP BY  
             sub.laenge, 
             sub.disziplin

Des Weiteren habe ich dann noch einen Auswahlfaktor, nennen wir es Typ.
Dieser Typ ist mit in der Tabelle der Veranstaltungen gespeichert. Mit dem vorherigen Code kann ich nun die Bestzeiten abfragen, ohne Rücksicht auf den Typ. Das ist auch in Ordnung so.
Wenn ich aber nun nur die Bestzeiten, die bei einem bestimmten Veranstaltungstyp erzielt wurden, abfragen will, kann ich einfach im Subselect die Tabelle 'veranstaltungen' hinzufügen und dort als Bedingung 'typ = 3' ergänzen, ist das korrekt?

Die Schwierigkeit bei solchen Problemstellung ist für mich immer, zu erkennen, was alles nicht relevant ist.

Hmm.. das ist jetzt auch schwierig zu beantworten, ich würde sagen an sich lässt sich erst einmal nicht vernachlässigen. Später soll denke ich mal auch noch die Bedingung mit reingebracht werden, dass nur Zeiten aus einem bestimmten Jahr oder Zeitraum nachgefragt werden. Dies lässt sich dann aber, soweit ich glaube, analog wie das mit dem Typ bewerkstelligen, da das Datum ja auch mit den Veranstaltungen gespeichert wird.

[Forumsgast]

Dein Lösungsansatz hat zwar Prima soweit funktioniert, jedoch finde ich den ganz schön kompliziert.

Vermutlich hauptsächlich, weil du Theta-Style-Joins vorziehst (JOIN-Bedingung in WHERE-Klausel).

Na ja, das Konzept habe ich ja verlinkt. Ich habe für mein Beispiel die Variante gewählt, die ich zu dem Zeitpunkt am besten verstanden habe. Die muss nicht optimal sein.

Würde demnach auch folgendermaßen Ansatz Sinn machen oder gibts da bezüglich der Geschwindigkeit oder sonstigem noch Probleme?

Was Geschwindigkeit angeht, bin ich in Sachen SQL kein Experte. Sowas ist aber generell auch immer erst dann ein Problem, wenn es ein Problem ist. (Stichwort: Premature Optimization)

Ich glaube aber, deine Query funktioniert höchstens zufällig (also nicht definiert). Ich bin mir nicht absolut sicher, was die Dokumentation sagt, aber ich meine, dass in einem GROUP-BY-Statement alle Felder, über die nicht gruppiert wird und die nicht mit einer Aggregate-Funktion (MIN, MAX, …) versehen sind, grundsätzlich einen undefinierten Wert enthalten.

Anders gesagt: Der Ansatz mit der vorhergehenden Sortierung kann bei der aktuellen DBMS-Version klappen, aber es ist nicht auszuschließen, dass der Algorithmus dahinter bei einer anderen Versionen verändert wird. Das ist also auf Sand gebaut. (Wie gesagt, ich bin mir nicht absolut sicher, ob das Verhalten definiert ist oder nicht.)

Wenn ich aber nun nur die Bestzeiten, die bei einem bestimmten Veranstaltungstyp erzielt wurden, abfragen will, kann ich einfach im Subselect die Tabelle 'veranstaltungen' hinzufügen und dort als Bedingung 'typ = 3' ergänzen, ist das korrekt?

Vorsichtiges Ja.

Hmm.. das ist jetzt auch schwierig zu beantworten, ich würde sagen an sich lässt sich erst einmal nicht vernachlässigen.

Mir ging es bei der Aussage darum, dass ich erst spät erkannt habe, dass sich das Problem wohl auf die angesprochenen within-group aggregates zurückführen lässt. Die vielen Joins haben mich verwirrt.

Dies lässt sich dann aber, soweit ich glaube, analog wie das mit dem Typ bewerkstelligen, da das Datum ja auch mit den Veranstaltungen gespeichert wird.

Das wird in jedem Fall noch mal etwas Überlegung benötigen.