MySQL: Erweiterte Abfrage - Sollte/darf man das so tun?

[Nico R.]

Hallo zusammen,

ich habe festgestellt, dass die SQL-Abfrage aus meinem letzten Beitrag noch zu unpräzise ist. Ich erhalte ja aktuell die Gesamtstatistik eines Spielers aus einer Saison.

Nun kann es allerdings in einer Saison fünf verschiedene Wettbewerbstypen (Liga, Landespokal, DFB-Pokal, Testspiel, Relegation) geben. Ich möchte gerne pro Saison die jeweiligen Gesamtsummen für diese Wettbewerbe erhalten.

Ich könnte nun meine bestehende SQL-Abfrage entsprechend erweitern, die liefert im Test auch schon die korrekten Daten. Dabei entsteht allerdings ein ziemliches Monstrum an Abfrage. Ich habe meine Zweifel, ob man das so tun sollte. Hier die erweiterte Abfrage mit zwei verschiedenen Wettbewerbstypen (eigentlich sind es wie gesagt fünf):

SELECT 		
	saison_spieler.saison_id,
	saison_spieler.nr,
	saison_spieler.pos,
	saison.name,
	saison.jahr,
    liga.einsaetze, liga.eingew, liga.ausgew, liga.spielzeit, liga.tore, liga.vorlagen, liga.gelb, liga.gelb_rot, liga.rot,
    pokal.einsaetze, pokal.eingew, pokal.ausgew, pokal.spielzeit, pokal.tore, pokal.vorlagen, pokal.gelb, pokal.gelb_rot, pokal.rot    
FROM 
	`saison_spieler`
JOIN
	`saison`
    ON saison.id = saison_spieler.saison_id
JOIN
	(SELECT 
     	spieltag.saison_id,
		COUNT(spielerstatistik.spieler_id) AS einsaetze,
		COUNT(spielerstatistik.eingew) AS eingew,
		COUNT(spielerstatistik.ausgew) AS ausgew,
		SUM(spielerstatistik.spielzeit) AS spielzeit,
		SUM(spielerstatistik.tore) AS tore,
		SUM(spielerstatistik.vorlagen) AS vorlagen,
		COUNT(spielerstatistik.gelb) AS gelb,
		COUNT(spielerstatistik.gelb_rot) AS gelb_rot,
		COUNT(spielerstatistik.rot) AS rot
    FROM
    	spielerstatistik 
    JOIN
    	spieltag
        ON spieltag.id = spielerstatistik.spieltag_id
    WHERE 
		spielerstatistik.spieler_id = 12 AND spieltag.wettbewerb = 1
	GROUP BY
		saison_id     
    ) AS liga    
    ON liga.saison_id = saison_spieler.saison_id
JOIN
	(SELECT 
     	spieltag.saison_id,
		COUNT(spielerstatistik.spieler_id) AS einsaetze,
		COUNT(spielerstatistik.eingew) AS eingew,
		COUNT(spielerstatistik.ausgew) AS ausgew,
		SUM(spielerstatistik.spielzeit) AS spielzeit,
		SUM(spielerstatistik.tore) AS tore,
		SUM(spielerstatistik.vorlagen) AS vorlagen,
		COUNT(spielerstatistik.gelb) AS gelb,
		COUNT(spielerstatistik.gelb_rot) AS gelb_rot,
		COUNT(spielerstatistik.rot) AS rot
    FROM
    	spielerstatistik 
    JOIN
    	spieltag
        ON spieltag.id = spielerstatistik.spieltag_id
    WHERE 
		spielerstatistik.spieler_id = 12 AND spieltag.wettbewerb = 2
	GROUP BY
		saison_id     
    ) AS pokal    
    ON pokal.saison_id = saison_spieler.saison_id    
WHERE
	saison_spieler.spieler_id = 12

Offenbar ist das prinzipiell für MySQL keine große Herausforderung. Ich bekomme in phpmyadmin die Auskunft "Die Abfrage dauerte 0,0000 Sekunden". Aber dennoch frage ich ja fünfmal die gleiche Art von Daten ab. In PHP kann ich die Erstellung des Querys in eine Funktion packen und somit Redundanzen vermeiden. Sollte/könnte ich das auch in SQL tun? Bewegt sich der Query noch einem "normalen" Rahmen? Ich hatte so etwas bisher noch nicht.

Schöne Grüße

Nico

[MudGuard]

Hi,

Nun kann es allerdings in einer Saison fünf verschiedene Wettbewerbstypen (Liga, Landespokal, DFB-Pokal, Testspiel, Relegation) geben. Ich möchte gerne pro Saison die jeweiligen Gesamtsummen für diese Wettbewerbe erhalten.

klingt für mich spontan nach einem GROUP BY Wettbewerbstyp.

cu,
Andreas a/k/a MudGuard

[Nico R.]

Hallo Andreas,

das geht wohl nicht, da ich schon nach saison_id gruppiere. Hier mal die Ergebnismenge zu der obigen Abfrage:

Hier sind natürlich die Feldnamen noch nicht per Alias präzisiert (liga.spielzeit usw.), aber grundsätzlich ist das schon genau das Ergebnis, das ich für die Darstellung der Daten brauche.

Ich glaube meine Frage, war daher auch überflüssig, denn im Grunde funktionierts ja. Und egal wie ichs drehe oder wende, z.B. nicht in eine einzige Abfrage packe, sondern in separate und dann dort meinetwegen per GROUP BY spieltag.wettbewerb strukturiere, muss ich ja doch irgendwie genau diese Daten abfragen. Glaube ich…

Für Anregungen und Hilfe bin ich natürlich trotzdem dankbar.

Schöne Grüße

Nico

[Rolf B]

Hallo Nico,

Die Tabellenstruktur der Abfrage muss nicht identisch mit der Tabellenstruktur der Ausgabe sein. Ein paar Umbauten sind schon erlaubt und ggf auch einfacher zu handhaben

Rolf

[MudGuard]

Hi,

das geht wohl nicht, da ich schon nach saison_id gruppiere.

Das ist kein Argument: man kann nach mehreren Spalten gruppieren.

Ich hab mir Deine Konstruktion nicht näher angeschaut (wie auch, gibt ja keine Tabellenbeschreibung), ggf. muß auch ein Group By am äußeren Select stattfinden.

cu,
Andreas a/k/a MudGuard

[Nico R.]

Hi Andreas,

Das ist kein Argument: man kann nach mehreren Spalten gruppieren.

Wieder was gelernt. Und auch gleich angewandt. Siehe Rolfs Abfrage.

Schöne Grüße

Nico

[Rolf B]

Hallo Nico,

WILLST du oder musst du alle Daten zu einer Saison in einer Zeile haben?

Es wäre einfacher, je Saison und Wettbewerb eine Zeile zu erzeugen.

Beim JOIN hat du auch das Problem, dass du nur Spieler bekommst, die an allen Wettbewerbstypen teilgenommen haben.

Bevor ich hier Berge von SQL raushaue, warte ich lieber auf deine Antwort 😉

Rolf

[Nico R.]

Hallo Rolf,

WILLST du oder musst du alle Daten zu einer Saison in einer Zeile haben?

Das hat sich aufgrund der Anzeige der Daten so ergeben. Zwingend ist es wohl nicht. So sieht der enstsprechende Anzeigeteil derzeit aus:

Es wäre einfacher, je Saison und Wettbewerb eine Zeile zu erzeugen.

Ich verstehs nicht ganz, fürchte ich 🤔

Beim JOIN hat du auch das Problem, dass du nur Spieler bekommst, die an allen Wettbewerbstypen teilgenommen haben.

Ja, stimmt. Das hätte ich gar nicht auf dem Schirm, lies sich aber im Test mit jeweils LEFT JOIN beheben.

Bevor ich hier Berge von SQL raushaue, warte ich lieber auf deine Antwort 😉

Das würde ich auch gar nicht erwarten. Der eine oder andere Anstoß reicht ja. Hoffentlich 😀

Schöne Grüße

Nico

[Rolf B]

Hallo Nico R.,

Nun kann es allerdings in einer Saison fünf verschiedene Wettbewerbstypen (Liga, Landespokal, DFB-Pokal, Testspiel, Relegation) geben. Ich möchte gerne pro Saison die jeweiligen Gesamtsummen für diese Wettbewerbe erhalten.

Das sieht doch schonmal so aus, als ob deine Query das falsche Format hätte. Du hast gar nicht so viele Spalten im Display wie die Query liefert, d.h. du musst bei deiner Query pro Saison erstmal rauskriegen, welche Spalten überhaupt gezeigt werden müssen. Ich reverse-engineere für Dich das folgende Datenmodell (sicherlich hast Du noch mehr Spalten in den Tabellen):

Ich hätte noch fachliche Fragen:

• Das Datenmodell gibt her, dass ein Spieler am Spieltag 1 in der Oberliga spielt und am Spieltag 2 in der Kreisliga (z.B. Ausleihe). Bist Du auf diesen Fall vorbereitet? Du bekommst dann nämlich für eine Saison mehrere Zeilen aus der Query.
• Für einen gegebenen Spieler besteht zwischen Saison und Saison_Spieler eine 1:1 Beziehung. Das suggeriert, dass ein Spieler während einer Saison in allen Wettbewerben mit der gleichen Nummer und auf der gleichen Position spielt. Ist das korrekt?
• Dein Screenshot gibt Spielernummer und -position nicht aus. Das heißt für mich, dass Du die Saison_Spieler-Tabelle gar nicht in der Query brauchst. Wieso hast Du Nr und Pos in der Query drin?

Ich habe Nr und Pos mal dringelassen… Eigentlich muss man nur die Abfrage auf den Wettbewerb entfernen und dafür die Spalte Wettbewerb in die Select-Liste und in die GROUP BY Liste übernehmen. Daraufhin bekommt man eine nach Saison und Wettbewerb gruppierte Liste der Einsätze eines Spielers.

Saisons, an denen er nicht teilgenommen hat, kommen aus diesem GROUP BY Select nicht heraus. Es ist deshalb sinnvoll, mit diesem Teil zu starten und für die gefundenen Saisons die Saison- und Spielerdaten hinzuzumischen.

SELECT Saison.Name, Saison.Jahr, 
       Saison_Spieler.Nr, Saison_Spieler_Pos,
       Stat.Wettbewerb, Stat.Einsaetze, Stat.Eingewechselt, ... Stat.Rot
FROM
       (SELECT Saison_ID as saison_id,
               Wettbewerb as wettbewerb, 
               Spielerstatistik.Spieler_ID as spieler_id,
               COUNT(Spieler_ID) as einsaetze,
               COUNT(eingew) as eingewechselt,
               ...
               COUNT(rot) as rot
        FROM
              Spieltag 
        JOIN 
              Spielerstatistik 
              ON spieltag.ID = spielerstatistik.spieltag_id
        WHERE 
              Spielerstatistik.Spieler_ID = $spieler_id
        GROUP BY
              Saison_ID, Wettbewerb
       ) Stat
       ON Stat.Saison_ID = Saison.ID
JOIN 
       Saison ON Saison.ID = Stat.Saison_ID
JOIN
       Saison_Spieler ON Saison_Spieler.Saison_ID = Stat.Saison_ID
                     AND Saison_Spieler.Spieler_ID = Stat.Spieler_ID
ORDER BY Saison.Jahr, Wettbewerb

Für das Beispiel in deinem Screenshot bekämest Du nun 4 Zeilen heraus, sortiert nach Saisonjahr und Wettbewerb.

Über diese Liste machst Du nun mit PHP eine Gruppenverarbeitung. Es gibt dazu einen Artikel in unserem Wiki, der ist für das, was hier nötig ist, aber zu komplex. Für eine einstufige Gruppenverarbeitung funktioniert es in PHP so:

/* Hier die Query ausführen und Erfolg prüfen */

/* Hier den Seitenkopf schreiben */

$row = $db->fetch();          // Einmal vorauslesen

if ($row == null) {
   /* Hier die Entschuldigung schreiben, dass keine Daten da sind */
}
WHILE ($row) {

   /* Hier <table>,<thead> etc für neue Saison-Tabelle schreiben */

   $saison = $row['saison_id']
   WHILE ($row && $row['saison_id'] == $saison) {

      /* Hier die Zeile für einen Wettbewerb schreiben */

      $row = $db->fetch();   // Nachlesen
   }
   /* Hier </table> etc für letzte Saison-Tabelle schreiben */
}
/* Hier den Seitenfuß schreiben */

Dieser Mustercode verwendet NICHT das bekannte PHP Pattern, im WHILE auch zu lesen (also WHILE ($row = $db->fetch())), statt dessen wird einmal vor der Schleife vorausgelesen, es werden zwei Schleifen geschachtelt (die äußere für die Saisons, die innere für die Saisonzeilen) und es wird am Ende der inneren Schleife nachgelesen. Auf diese Weise erhält man eine Programmstruktur, die der Ausgabedatenstruktur entspricht.

Rolf

[Nico R.]

Hallo Rolf,

vielen Dank erstmal für deine Hilfe. Ich hatte das Thema gestern abend quasi schon ad acta gelegt und mich mit meinem Riesen-Query abgefunden (Wer sonst hat sowas schon?). Deine Abfrage ist natürlich wesentlich eleganter. (Das Ergebnis aber dafür einen Ticken redundanter 😜)

• Das Datenmodell gibt her, dass ein Spieler am Spieltag 1 in der Oberliga spielt und am Spieltag 2 in der Kreisliga (z.B. Ausleihe). Bist Du auf diesen Fall vorbereitet? Du bekommst dann nämlich für eine Saison mehrere Zeilen aus der Query.

Es wird pro Jahr nur eine Saison angelegt. Ein Spieler ist über saison_spieler entweder Teil des Kaders dieser einen Saison oder nicht. Das Modell hast du übrigens 1A hergeleitet.

• Für einen gegebenen Spieler besteht zwischen Saison und Saison_Spieler eine 1:1 Beziehung. Das suggeriert, dass ein Spieler während einer Saison in allen Wettbewerben mit der gleichen Nummer und auf der gleichen Position spielt. Ist das korrekt?

Ja, genau. Die Position (Tor, Abwehr, Mittelfeld, Angriff) ist quasi ein Planwert pro Saison. Im Grunde ist es nicht unüblich, dass ein Angreifer in einem Spiel auch in der Abwehr eingesetzt wird. Das ist aber nicht wichtig und wird auch nicht erfasst.

• Dein Screenshot gibt Spielernummer und -position nicht aus. Das heißt für mich, dass Du die Saison_Spieler-Tabelle gar nicht in der Query brauchst. Wieso hast Du Nr und Pos in der Query drin?

Doch doch, die Position wurde mit angezeigt (inzwischen nicht mehr). Die, als auch die Nr. fliegen auch aus der Query. Ich habe deine Abfrage nochmal leicht angepasst, es hatten sich zwei Fehler eingeschlichen:

Zeile 2: (Saison_Spieler_Pos statt Saison_Spieler.Pos

Zeile 22: ON Stat.Saison_ID = Saison.ID nach (SELECT ...) stat

Sie funktioniert nun und liefert genau das, was ich brauche:

Dass manche leere Felder mit NULL gefüllt sind und andere mit 0, resultiert aus Unkenntnis beim Erstellen der Tabellen vor etlichen Jahren. Damals war mir der Unterschied nicht recht bewusst. Nun ists so und ich möchte ungerne daran herumrühren.

Als nächstes kommt jetzt die Gruppenverarbeitung der Daten. Sollte ich dabei auf Probleme stoßen, werd ich mich melden 😉

Einstweilen vielen Dank und einen schönen Abend

Nico