Mehrstufige Gruppierung

[Gregor]

Hallo Selfhtml-Forum,
da ich im Archiv und auch über Google nichts direkt gefunden habe was mein Problem betritt, erstelle ich mal einen neuen Beitrag.

Ich habe eine MySQL Datenbank, die die Bilanzen von Tischtennisspielen enthält. Die Tabelle "bilanzen" enthält für jeden für jeden Spieltag (repräsentiert über spielId) die Resultate der Spieler (repräsentiert durch SpielerId). Falls es ein Doppel war, enthält die Spalte "doppelpartnerId" die Id des Doppelpartners, sonst ist das Feld NULL.

Beispiel:
spielId | spielerId | doppelpartnerId | gewonnen | verloren
----------------------------------------------------------------
   1    |    11     | NULL            |    2     |  0
   1    |    11     | 12              |    0     |  1
   2    |    11     | 12              |    1     |  0
   3    |    11     | 33              |    1     |  0

Mein Ziel ist es, daraus eine Tabelle mit den Doppelbilanzen zu erstellen, wobei jede Doppelpaarung nur einmal auftauchen soll und ihre Bilanzen addiert werden:

spielerId | doppelpartnerId | gewonnen | verloren
----------------------------------------------------
11        | 12              |    1     |  1
11        | 13              |    1     |  0

Das Problem ist, das eine einfache Gruppierung über spielerId bzw doppelpartnerId nicht ausreicht, weil die Doppelpaarungen ja immer noch unterschiedlich sein können. Ich bräuchte also eine Abfrage die zuerst nach spielerId gruppiert und danach nach doppelparnterId, und über den entstehenden resultaten dann die SUM() für gewonnen und verloren aufruft.
Also so in etwa (syntaktisch nicht korrekt):

SELECT spielerId, doppelpartnerId, SUM(gewonnen) AS gew, SUM(verloren) AS verl
FROM bilanzen
GROUP BY spielerId, GROUP BY doppelpartnerId
HAVING doppelpartnerId NOT NULL;

Ist so etwas überhaupt möglich oder muss ich da kompliziertere Umwege machen?
Schön wäre auch, wenn in der gleichen Abfrage statt der SpielerIds die Namen der Spieler ausgegeben würden (Verknüpfung bilanzen.spielerId/doppelpartnerId mit Tabelle spieler.name), aber das wäre ja auch noch anders lösbar (über zusätzliche Abfragen), wenn ich erstmal die Ids habe.

mfg Gregor

[Rouven]

Hi,

Das Problem ist, das eine einfache Gruppierung über spielerId bzw doppelpartnerId nicht ausreicht, weil die Doppelpaarungen ja immer noch unterschiedlich sein können. Ich bräuchte also eine Abfrage die zuerst nach spielerId gruppiert und danach nach doppelparnterId, und über den entstehenden resultaten dann die SUM() für gewonnen und verloren aufruft.

ähm, was ist dein Problem? Du möchtest alle zusammenfassen, bei denen spielerId und doppelpartnerId übereinstimmen. Tu das, streich einfach aus deinem Syntaxvorschlag das zweite GROUP BY
GROUP BY spielerId, doppelpartnerId

Oder übersehe ich jetzt was?

Schön wäre auch, wenn in der gleichen Abfrage statt der SpielerIds die Namen der Spieler ausgegeben würden (Verknüpfung bilanzen.spielerId/doppelpartnerId mit Tabelle spieler.name), aber das wäre ja auch noch anders lösbar (über zusätzliche Abfragen), wenn ich erstmal die Ids habe.

Das ist sicherlich eine spannende Frage. Ich - als Freund von Subselects - würde die gesamte obige Abfrage in ein Subselect packen und einen Join in die Namenstabelle auf Basis der IDs anschließen. Ob das DBMS das mitmacht hängt davon ab was du im Einsatz hast.
Alternativ könntest du den Join auch schon in der Abfrage mit einfließen lassen und anschließend nach ID+Name gruppieren.

MfG
Rouven

[Gregor]

Hi

ähm, was ist dein Problem? Du möchtest alle zusammenfassen, bei denen spielerId und doppelpartnerId übereinstimmen. Tu das, streich einfach aus deinem Syntaxvorschlag das zweite GROUP BY
GROUP BY spielerId, doppelpartnerId

Oder übersehe ich jetzt was?

ARG :-) Nene das geht schon so, nur das war die eine Variante die ich noch nicht ausprobiert hatte.

Schön wäre auch, wenn in der gleichen Abfrage statt der SpielerIds die Namen der Spieler ausgegeben würden (Verknüpfung bilanzen.spielerId/doppelpartnerId mit Tabelle spieler.name), aber das wäre ja auch noch anders lösbar (über zusätzliche Abfragen), wenn ich erstmal die Ids habe.
Das ist sicherlich eine spannende Frage. Ich - als Freund von Subselects - würde die gesamte obige Abfrage in ein Subselect packen und einen Join in die Namenstabelle auf Basis der IDs anschließen. Ob das DBMS das mitmacht hängt davon ab was du im Einsatz hast.
Alternativ könntest du den Join auch schon in der Abfrage mit einfließen lassen und anschließend nach ID+Name gruppieren.

Habs mit Sub-Selects gelöst, klappt sogar:
SELECT
(SELECT name FROM spieler WHERE id = spielerId) AS spieler1,
(SELECT name FROM spieler WHERE id = doppelpartnerId) AS spieler2, SUM( gewonnen ) AS gew, SUM( verloren ) AS verl
FROM bilanzen
GROUP BY spielerId, doppelpartnerId
HAVING doppelpartnerId IS NOT NULL

Vielen Dank für die schnelle Hilfe

mfg Gregor

[Ilja]

yo,

ARG :-) Nene das geht schon so, nur das war die eine Variante die ich noch nicht ausprobiert hatte.

besser als ausprobieren wäre ein blick in die online-doku gewesen, dort stehen auch beispiele für gruppierungen.

SELECT
(SELECT name FROM spieler WHERE id = spielerId) AS spieler1,
(SELECT name FROM spieler WHERE id = doppelpartnerId) AS spieler2, SUM( gewonnen ) AS gew, SUM( verloren ) AS verl
FROM bilanzen
GROUP BY spielerId, doppelpartnerId
HAVING doppelpartnerId IS NOT NULL

erstens wäre ich bei mehreren tabellen vorsichtig, was die eindeutigkeit von spaltennamen angeht, sprich ich würde die tabellennamen noch zu den spaltennamen hinzufügen, beispiel:

(SELECT spieler.name FROM spieler WHERE spieler.id = bilanzen.spielerId) AS spieler1

wenn du willst, kannst du auch aliasnamen für die tabellen benutzen. des weiteren hat die bedingung doppelpartnerId IS NOT NULL nichts in der HAVING klausel zu suchen, sondern gehört in die WHERE klausel. und drittens fäßt du spiele nicht zusammen, wo die reihenfolge der spielerID und der doppelpartnerID genau umgekehrt ist. oder aber du hast redundanz in der tabelle, wenn du jeweils für jeden doppelpartner einen datensatz speicherst. letztlich ist meiner meinung nach die problematik auf das daten-design zurückzuführen.

vielleicht ließt du das ja hier noch, in aller regel melden man sich nicht mehr, wenn man glaubt, eine lösung gefunden zu haben.

Ilja

[Gregor]

besser als ausprobieren wäre ein blick in die online-doku gewesen, dort stehen auch beispiele für gruppierungen.

Hm. Also in der Doku wo ich gekuckt hab stands halt so direkt nicht drin und auch kein Beispiel, wer kann schon ahnen das mit "expr" mehrere Spalten durch Kommata separiert gemeint ist...  (http://dev.mysql.com/doc/refman/5.0/en/select.html).

SELECT
(SELECT name FROM spieler WHERE id = spielerId) AS spieler1,
erstens wäre ich bei mehreren tabellen vorsichtig, was die eindeutigkeit von spaltennamen angeht, sprich ich würde die tabellennamen noch zu den spaltennamen hinzufügen, beispiel:

Da die einzelnen Abfragen in immer nur einer Tabelle nachschauen und sich nicht überschneiden hab ich mir den Extraaufwand gespart. Bei JOINs und anderen Anfragen mache ich schon genau so wie du es vorgeschlagen hast (Tabellenalias.Spaltenname)

des weiteren hat die bedingung doppelpartnerId IS NOT NULL nichts in der HAVING klausel zu suchen, sondern gehört in die WHERE klausel.

Ich dachte man sollte bei Gruppierungen das HAVING benutzen weil das WHERE das Ergebnis der Gruppierung zu dem Zeitpunkt ja nicht kennt (meine ich mal irgendwo gehört zu haben, lasse mich da aber gerne belehren)

und drittens fäßt du spiele nicht zusammen, wo die reihenfolge der spielerID und der doppelpartnerID genau umgekehrt ist. oder aber du hast redundanz in der tabelle, wenn du jeweils für jeden doppelpartner einen datensatz speicherst. letztlich ist meiner meinung nach die problematik auf das daten-design zurückzuführen.

Ich habe keine Redundanz in der Tabelle, jedes Doppel kriegt genau eine Tabellenzeile. Das Problem ist mir auch schon aufgefallen, trifft aber in diesem Anwendungsfall nicht zu, weil bei den Doppelspielern der auf dem Mannschaftsmeldeformular höher aufgestellte Spieler (der nominell stärkere) immer als erstes genannt wird, und die Doppel nur in dieser Reihenfolge angegeben werden. Der Problemfall tritt also nicht auf (solange die Mannschaft nicht umgestellt wird, was höchstens NACH einer Saison passiert, ab dann heißt das Doppel auch offiziell anders).

mfg Gregor Jochmann

[Ilja]

yo,

Ich dachte man sollte bei Gruppierungen das HAVING benutzen weil das WHERE das Ergebnis der Gruppierung zu dem Zeitpunkt ja nicht kennt (meine ich mal irgendwo gehört zu haben, lasse mich da aber gerne belehren)

wenn sich die selektion aber erst gar nicht auf die gruppierungen bezieht, sondern ganz unabhängig davon datensätze selektiert, dann gehört es immer noch in die WHERE klausel. würde ja auch keinen sinn machen, erst datensätze mit in das result-set reinnehmen und dann mit HAVING wieder ausschließen. anders sieht es zum beispiel bei den aggregat-funktionen aus. diese beziehen sich auf die jeweiligen gruppierungen, zum beispiel zeige mit alle gruppierungen an mit einer anzahl von datensätze größer 10 an (HAVING count(*) > 10).

trifft aber in diesem Anwendungsfall nicht zu, weil bei den Doppelspielern der auf dem Mannschaftsmeldeformular höher aufgestellte Spieler (der nominell stärkere) immer als erstes genannt wird, und die Doppel nur in dieser Reihenfolge angegeben werden.

ist eine schwachstelle, weil du dich darauf verlasen musst. wenn es geht, würde ich mich von seiten der datenbank (abfrage) davon unabhängig machen.

Ilja