Join und Group By

Surffreak · 17.12.2011, 21:09

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:

Code:

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

mermshaus · 17.12.2011, 21:55

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.

Surffreak · 18.12.2011, 11:09

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.

mermshaus · 18.12.2011, 12:55

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:

Code:

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.

Surffreak · 18.12.2011, 15:38

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?

Code:

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?

Zitat:

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.

mermshaus · 19.12.2011, 00:37

Zitat:

Zitat von Surffreak

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.

Zitat:

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.)

Zitat:

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.

Zitat:

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.

Zitat:

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.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
OUTER JOIN Abfrage, die evtl. keine Treffer zurückgeben könnte	noop	Datenbanken	1	27.01.2011 20:23
[Erledigt] JOIN über 3 Tabellen + 2 m:n Tabellen	dennis81	Datenbanken	7	08.12.2010 15:36
[Erledigt] left join problem	Bernd-m	Datenbanken	9	24.08.2009 13:52
JOIN und LEFT JOIN verbinden?	Dynamite53	Datenbanken	8	15.05.2009 15:16
[Erledigt] select() method zend sum() ohne group by	cosmicadi	PHP Tipps 2009	1	29.04.2009 18:26
Persistence Framework	#Avedo	Software-Design	37	28.03.2009 17:32
Where Anweisung vor GROUP BY einbinden.	Kronic73	Datenbanken	6	06.03.2009 09:40
Bräuchte hilfe bei schleifenproblem innerhalb SQL anweisung	Kronic73	Datenbanken	8	01.03.2009 01:10
[Erledigt] GROUP BY Reihenfolge	stefanjann	Datenbanken	4	02.12.2008 14:53
[Erledigt] mit einer CHECKBOX feld nach mehreren wörtern durchsuchen	taurus	Datenbanken	20	01.12.2008 10:49
[Erledigt] Left join und inner join	stefanjann	Datenbanken	7	16.10.2008 15:45
INNER JOIN, GROUP BY und ... schwer zu erklären	abyss	Datenbanken	2	15.06.2008 00:55
Problem mit Inner join	Jabi	Datenbanken	15	09.05.2006 14:10
inner join aus db		PHP Tipps 2006	8	22.01.2006 01:23
[Erledigt] Irgendein Join oder doch nur GROUP ???		Datenbanken	7	12.10.2005 13:08

17.12.2011, 21:09
Surffreak Neuer Benutzer Registriert seit: 16.02.2010 Beiträge: 13 PHP-Kenntnisse: Anfänger	Join und Group By 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: Code: 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


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

17.12.2011, 21:55
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	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. __________________ Blog \| Buch \| Kaloa

18.12.2011, 12:55
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	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: Code: 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. __________________ Blog \| Buch \| Kaloa