[MySQL] Bitte mal Konzept anschauen

[alpman]

Hallo zusammen,

Ich habe eine Datenbank mit folgenden Tabellen:

game: id, name, ...
round: id, round, game_id ...
placing: id, round_id, player_id
player: id, name, ...

Es kann also i Spiele geben, die jeweils n Runden haben, die jeweils mit m Platzierungen verknüpft sind. Pro Platzierung gibt es einen Spieler.

Diese Struktur habe ich mir ausgedacht, um folgendes abzubilden:

Spiel 1
Runde 1
1. Stefan
2. Christian
m. ...
Runde 2
1. Falko
2. Christian
m ...
Runde n
...

Spiel i
...

Es soll nun auch eine Bestenliste geben. Dazu haben wir einen Punkteverteilungschlüssel, der abhängig von der Anzahl der Spieler pro Runde ist, entwickelt.

Die Bestenliste soll diese Überschriften haben:

Platz|Spieler|Punkte|Spiele|Punkte/Spiel|Runden|Punkte/Runde

(Die Platzierung richtet sich nach der Anzahl der Punkte, die ein Spieler in den Spielen gesammelt hat).

Mit der Anweisung

  
SELECT player.name AS Spieler, COUNT(DISTINCT game.id) AS Spiele, COUNT(DISTINCT round.id) AS Runden FROM game, round, placing, player  
    -> WHERE game.id = round.game_id  
    -> AND round.id = placing.round_id  
    -> AND placing.player_id = player.id GROUP BY player.name;  

bekomme ich schon mal Name|Spiele|Runden.

+-----------+--------+--------+
| Spieler   | Spiele | Runden |
+-----------+--------+--------+
| Alexander | 2      | 7      |
| Benno     | 2      | 7      |
| Christian | 2      | 7      |
| David     | 2      | 7      |
| Falko     | 2      | 7      |
| Stefan    | 2      | 7      |
+-----------+--------+--------+

Ich könnte nun die Berechnung der Punkte, Punkte/Spiel und Punkte/Runde mittels PHP durchführen. Ich weiß nun aber nicht, ob dieser Weg einfacher (unkomplizierter) ist, als die Berechnung mit SQL (eigentlich möchte ich wissen, ob und wie es mit SQL geht :)).

Ich habe mir überlegt, eine Tabelle point_allocation mit folgenden Feldern anzulegen: id, nr_players, place, points_for_place.

Pro Spiel muss ich die Anzahl der Runden ermitteln und dann innerhalb der einzelnen Runden die Platzierungen der einzelnen Spieler. Mit diesen Informationen müsste ich auf die Tabelle point_allocation zugreifen und die Punkte zusammenzählen.

Weiß jemand Rat? Da ich mit SQL noch nicht soviel Erfahrung habe, erwarte ich eine sehr komplizierte bzw. umfangreiche Anweisung.

Ich möchte ungern die Punkte bei den Spieler speichern (eventuell ändert sich der Schlüssel; wenn Runden gelöscht werden, müssten auch die Spieler verändert werden).

Viele Grüße,

Stefan

[Yinan]

Also, wenn ich das richtig verstehe wurden die Berechnungen die du alle brauchst nicht in die Tabelle kopiert, oder? Von wegen du solltest mal sagen nach was sich jetzt die Punkte richten ^^

Weil berechnungen kannste soweit ich weil mit SQL nicht durchführen, SQL ist nur dafür gedacht, mit einer Datenbank zu interargieren, weshalb du die berechnungen gezwungenermaßen mit php machen musst. Also einfach alle nötigen Daten für den Spieler in variablen speichern (bzw. Arrays) und dann die jeweiligen Punkte berechnen und daraus dann die "highscore" aufbauen (sollt ich irgendwas collkommen falsch verstanden haben jetzt tuts mir leid -.-).

mfg

Yinan

[alpman]

Hallo Yannick,

Also, wenn ich das richtig verstehe wurden die Berechnungen die du alle brauchst nicht in die Tabelle kopiert, oder? Von wegen du solltest mal sagen nach was sich jetzt die Punkte richten ^^

Jeder Spieler belegt in einer Runde einen Platz. Für diese Platzierung bekommt er Punkte (6 Spieler: Platz 1 50 Punkte, Platz 2 30 Punkte, ..., 7 Spieler: Platz 1 58 Punkte, Platz 2 35 Punkte, ...).

Weil berechnungen kannste soweit ich weil mit SQL nicht durchführen, ...

MySQL hat die Operatoren +, -, *, / und %.

Es ist die Frage, wie kompliziert die Berechnungen werden und ob und wieviele Zwischenergebnisse man benötigt.

Viele Grüße,

Stefan

[Yinan]

Hoi!

Also wenn du sowieso ne veriable punktevergabe hast, dann musst du dich auch noch ne Tabelle haben, in der die Punkte drin stehen oda?
---------------------------------------------------------------------
| Anzahl Spieler | Punkteplatz1 | Punkteplatz2 | ... | Punkteplatzn |
---------------------------------------------------------------------
|       5        |      50      |      40      | ... |       n      |
---------------------------------------------------------------------
usw.

Weil ohne die musst du doch sowieso mit php arbeiten oder? Oder gibbet da ne bestimmte Formel?
`
Also ich würde auf jeden Fall sagen dasses relativ umständlich ist, das ganze mit einer MySQL abrage zu gestalten, das ist mit PHP im Endeffekt doch leichter würd ich sagen... kann mich aber auch irren...

mfg

Yinan

[alpman]

Hallo Yinan,

Also wenn du sowieso ne veriable punktevergabe hast, dann musst du dich auch noch ne Tabelle haben, in der die Punkte drin stehen oda?

Bisher habe ich das in einem Array in meinem PHP-Skript. Da das jedoch Daten sind, habe ich mir eine entsprechende Tabelle angelegt. Und dann kam ich auf die Idee, die Berechnungen in SQL zu machen.

Viele Grüße,

Stefan

[Yinan]

Hoi!

Ok, also da ich mit dem SQL-Syntax nicht soo gut vertraut bin (weiß soviel das ich sachen aus der datenbank auswählen und in php auswerten kann) kann ich dir diesbezüglich dann wohl nicht weiterhelfen. Wie gesagt, ich würde es in php weiter machen :D
Viel erfolg noch damit :D

mfg

Yinan

[Ilja]

yo,

game: id, name, ...
round: id, round, game_id ...
placing: id, round_id, player_id
player: id, name, ...

deine gesuchten abfragen sollten machbar sein, ohne sie mir jetzt im detail angeschauft zu haben. was dein design betrifft, so kann man daran vielleicht noch feilen.

gehen wir mal von den zwei entitäten aus. zum einen haben wir die spieler und zum anderen die games. sie stehen in einer m:n beziehung, sprich jeder spieler kann an mehreren games teilnhemen und ein game hat mehrere spieler. die anzahl der runden an einem spiel gehört eigentlich auch in die games tabelle.

um beide tabellen miteinander abbzubilden, brauchen wir also sowieso eine weitere tabelle, nämlich welcher spieler an welche games teilgenommen hat. und diese tabellen könnte auch gleich die runden repräsentieren. dort könnte auch das ergebnis der runden abgebildet werden.

damit hätten wir eine tabelle weniger, schau mal, ob das für dich ein gehbarer weg ist, dann geht es im nächsten schritt an die abfragen.

Ilja

[alpman]

Hallo Ilja,

gehen wir mal von den zwei entitäten aus. zum einen haben wir die spieler und zum anderen die games. sie stehen in einer m:n beziehung, sprich jeder spieler kann an mehreren games teilnhemen und ein game hat mehrere spieler. die anzahl der runden an einem spiel gehört eigentlich auch in die games tabelle.

Ich habe die Runden in eine eigene Tabelle ausgelagert, da es schon vorgekommen ist, dass die Anzahl der Spieler in einem Spiel variierte (es ist jemand später dazu gekommen oder es musste jemand früher gehen). Ich muss also exakt bestimmen können, welcher Spieler an welcher Runde teilgenommmen hat.

dort könnte auch das ergebnis der runden abgebildet werden.

Meinst Du etwas in der Art:

game: id, name, ...
player: id, name, ...

round: id, round, place, game_id, player_id

? Ist doch im Prinzip nichts anderes als mein Entwurf, nur das meiner "normalisierter" ist, oder?

Viele Grüße,

Stefan

[Ilja]

yo,

Ich habe die Runden in eine eigene Tabelle ausgelagert, da es schon vorgekommen ist, dass die Anzahl der Spieler in einem Spiel variierte (es ist jemand später dazu gekommen oder es musste jemand früher gehen). Ich muss also exakt bestimmen können, welcher Spieler an welcher Runde teilgenommmen hat.

das kommt drauf an, ob es um beispiel eine vorgebene anzahl von runden pro spieler gibt. die gehört dann in die games. wer welchen runden dann letztlich zu einem game belegt hat, das hälst du sowieso in der beziehungstabelle fest.

Meinst Du etwas in der Art:

game: id, name, ...
player: id, name, ...

round: id, round, place, game_id, player_id

ja so in etwa meinte ich es.

? Ist doch im Prinzip nichts anderes als mein Entwurf, nur das meiner "normalisierter" ist, oder?

normalisiert ist so ein schönes wort, allerdings gibt es in aller regel dabei missverstädnisse. auf den ersten blick würde ich sagen, du hast selbst nach der dritten normalform eine tabelle zuviel. das hängt aber letztlich von deiner umgebung ab, um das genau beurteilen zu können, sprich von den abhängigkeiten der daten. ich sehe erst einmal keinen grunde, deine beiden tabellen placing und round nicht als beziehungstabelle der games und spieler zusammenzufassen.

welche gründe sprechen dagegen ?

Ilja

Ilja

[alpman]

Hallo Ilja,

welche gründe sprechen dagegen ?

Gar keine. Wenn es der Vereinfachung der Datenhaltung -und abfrage dient, werde ich es so umsetzen.

Dann können wir ja jetzt zu den SQL-Anweisungen übergehen :).

Viele Grüße,

Stefan

[Ilja]

yo,

Dann können wir ja jetzt zu den SQL-Anweisungen übergehen :).

game: id, name, ...
round: id, round, placing, game_id, player_id
player: id, name, ...

so würden dann in etwa die drei tabellen aussehen. darauf nun die abfrage der bestenliste, wobei ich deinen punkteverteilungsschlüssel nicht kenne:

SELECT Platz, -- einfach nur stumpf von 1 bis n hochzählen
       p.name AS Spieler,
      (hier_unterabfrage_mit_verteilungsschluessel) AS Punkte,
       (SELECT COUNT(DISTINCT r2.game_id) FROM round r2 WHERE p1.id = r2.player_id) AS Spieleanzahl ,
       (hier_wieder eine_unterabfragen_,it_den_punkten / anzahl_runden),
       (SELECT COUNT(*) FROM round r3 WHERE p1.id = r3.player_id) AS Rundenanzahl,
       (hier_noch_eine_unterabfrage_mit_punkte_runden) AS Punkte/Runde
FROM player p
LEFT JOIN round r ON p.id = r.player_id
LEFT JOIN game g ON g.id = r.game_id
ORDER BY Punkte DESC

die LEFT JOINS sind notwendig, damit auch Spieler aufgenommen werden, die noch an keinen spiel teilgenommen haben, aber trotzdem gelistet werden.

btw. unterabfragen geher erst ab 4.1+ bei mysql.

Ilja

[alpman]

Uiuiuiui, da habe ich heute nacht ja was zum Nachdenken.

Danke und viele Grüße,

Stefan

[alpman]

Hallo Ilja,

Ich taste mich langsam heran.

Meine Tabellen habe ich doch so gelassen, wie ich sie entworfen habe:

game: id, ...
round: id, game_id, ...
placing: id, round_id, player_id
player: id

Außerdem noch die Tabelle point_allocation:
id, nr_players, place, points

Die hat im Moment folgenden Inhalt:
+------------+-------+--------+
| nr_players | place | points |
+------------+-------+--------+
| 5          | 1     | 42     |
| 5          | 2     | 25     |
| 5          | 3     | 12     |
| 5          | 4     | 4      |
| 5          | 5     | 0      |
| 6          | 1     | 50     |
| 6          | 2     | 30     |
| 6          | 3     | 15     |
| 6          | 4     | 4      |
| 6          | 5     | 1      |
| 6          | 6     | 0      |
| 7          | 1     | 58     |
| 7          | 2     | 35     |
| 7          | 3     | 18     |
| 7          | 4     | 5      |
| 7          | 5     | 1      |
| 7          | 6     | 0      |
| 7          | 7     | 0      |
+------------+-------+--------+

Mit der Anweisung

  
SELECT p.name AS Spieler,  
 COUNT(DISTINCT g.id) AS Spiele,  
 COUNT(DISTINCT r1.id) AS Runden,  
 SUM((SELECT pa.points FROM point_allocation pa  
 WHERE pa.nr_players = ANY  
  (SELECT COUNT(DISTINCT pg.id) FROM round r2  
  LEFT JOIN placing pg ON pg.round_id = r2.id  
  LEFT JOIN game g ON r2.game_id = g.id  
  WHERE r2.id = r1.id  
  GROUP BY r1.id)  
 AND pa.place = pg.place)) AS Punkte  
FROM player p  
LEFT JOIN placing pg ON pg.player_id = p.id  
LEFT JOIN round r1 ON pg.round_id = r1.id  
LEFT JOIN game g ON g.id = r1.game_id  
GROUP BY p.name ORDER BY Punkte DESC, Spiele DESC, Runden DESC;  

bekomme ich schon mal

+-----------+--------+--------+--------+
| Spieler   | Spiele | Runden | Punkte |
+-----------+--------+--------+--------+
| Christian | 2      | 7      | 280    |
| Stefan    | 2      | 7      | 148    |
| Alexander | 2      | 7      | 84     |
| Benno     | 2      | 7      | 76     |
| David     | 2      | 7      | 72     |
| Falko     | 2      | 7      | 40     |
| Stephan   | 0      | 0      | NULL   |
| Gregor    | 0      | 0      | NULL   |
| Jago      | 0      | 0      | NULL   |
+-----------+--------+--------+--------+

Vielen Dank für Deine Tipps. Nun habe ich mal probiert, die Spiele, Runden und Punkten Variablen zuzuweisen, und mit diesen weiter zu rechnen

  
...  
@nrSpiele := COUNT(DISTINCT g.id) AS Spiele,  
@nrRunden := COUNT(DISTINCT r1.id) AS Runden,  
@Punkte := SUM((SELECT pa.points FROM point_allocation pa  
...  
@Punkte/@nrSpiele AS "Punkte/Spiel"  

bekomme dann jedoch nur NULL in der Spalte "Punkte/Spiel". Ist das der richtige Weg?

Viele Grüße,

Stefan

[King Lully]

Wir haben hier die Entitäten 'Players' und 'Games', wobei die 'Rounds' die "n:m" zwischen beiden ist, also vermutlich drei Tabellen benötigt werden.

Unseres Erachtens spricht wenig gegen eine vierte Tabelle 'Ranks' oder 'Placings', das Datendesign käme dann "physikalisch" ganz gut rüber, d.h. man könnte auf der Tabelle 'Rankings' bei Bedarf herumhühnern, falls erforderlich.

Zusammengefasst ist das zur Diskussion freigegebene Datendesign also u.E. OK.

[alpman]

Euer Majestät,

Zusammengefasst ist das zur Diskussion freigegebene Datendesign also u.E. OK.

Hocherfreut habe ich Eure gnädige Mitteilung gelesen. Würdet Ihr in Eurer unendlichen Güte noch die Freundlichkeit haben, ein paar Minütchen Eurer gewiss kostbaren Zeit zu opfern und die Frage beantworten, ob die Nutzung von Variablen der richtige Weg sind?

Euer ergebenster Diener,

Stefan

[King Lully]

Variablen sind oft eine gute Idee, manchmal sind sie sogar zwingend erforderlich. Wir haben uns also einen weiter oben stehenden Beitrag durchgelesen, um die Fragestellung verstehen zu können, leider mit mässigem Erfolg.

Die zurückerhaltene Datensatzmenge soll weiterverarbeitet werden, die Matrix also Arrays mit mehreren Datenfeldern zugeordnet werden, also übersetzt werden. Ja, das ginge, das wäre nicht schlecht. Was genau ist die Frage? Was sollen die "@-Variablen" (sind das MySQL-Variablen?)? Braucht man die?

Bitte immer möglichst präzise beschreiben, was erreicht werden soll, "dynamische" Anforderungslagen sind schlecht.

[alpman]

Eure Exzellenz,

Was sollen die "@-Variablen" (sind das MySQL-Variablen?)? Braucht man die?

Siehe Benutzerdefinierte Variablen

Mit der Anweisung

  
SELECT p.name AS Spieler,  
 @nrSpiele := COUNT(DISTINCT g.id) AS Spiele, --Variable neu hinzugefügt  
 @nrRunden := COUNT(DISTINCT r1.id) AS Runden, --Variable neu hinzugefügt  
 @Punkte := SUM((SELECT pa.points FROM point_allocation pa --Variable neu hinzugefügt  
 WHERE pa.nr_players = ANY  
  (SELECT COUNT(DISTINCT pg.id) FROM round r2  
  LEFT JOIN placing pg ON pg.round_id = r2.id  
  LEFT JOIN game g ON r2.game_id = g.id  
  WHERE r2.id = r1.id  
  GROUP BY r1.id)  
 AND pa.place = pg.place)) AS Punkte,  
 @Punkte/@nrSpiele AS "Punkte/Spiel" --aus oben eingeführten Variablen neuen Wert berechnen  
FROM player p  
LEFT JOIN placing pg ON pg.player_id = p.id  
LEFT JOIN round r1 ON pg.round_id = r1.id  
LEFT JOIN game g ON g.id = r1.game_id  
GROUP BY p.name ORDER BY Punkte DESC, Spiele DESC, Runden DESC;  

erhoffte ich mir folgendes Ergebnis:

+-----------+--------+--------+--------+--------------+
| Spieler   | Spiele | Runden | Punkte | Punkte/Spiel |
+-----------+--------+--------+--------+--------------+
| Christian | 2      | 7      | 280    | 140          |
| Stefan    | 2      | 7      | 148    | 74           |
+-----------+--------+--------+--------+--------------+

Ich bekomme jedoch:

+-----------+--------+--------+--------+--------------+
| Spieler   | Spiele | Runden | Punkte | Punkte/Spiel |
+-----------+--------+--------+--------+--------------+
| Christian | 2      | 7      | 280    | NULL         |
| Stefan    | 2      | 7      | 148    | NULL         |
+-----------+--------+--------+--------+--------------+

Hochachtungsvoll,

Stefan

[King Lully]

SELECT p.name AS Spieler,
COUNT(DISTINCT g.id) AS Spiele, --Variable neu hinzugefügt
COUNT(DISTINCT r1.id) AS Runden, --Variable neu hinzugefügt
SUM((SELECT pa.points FROM point_allocation pa --Variable neu hinzugefügt
WHERE pa.nr_players = ANY
  (SELECT COUNT(DISTINCT pg.id) FROM round r2
  LEFT JOIN placing pg ON pg.round_id = r2.id
  LEFT JOIN game g ON r2.game_id = g.id
  WHERE r2.id = r1.id
  GROUP BY r1.id)
AND pa.place = pg.place)) AS Punkte,
Punkte/Spiele AS "Punkte/Spiel" --aus oben eingeführten Variablen neuen Wert berechnen
FROM player p
LEFT JOIN placing pg ON pg.player_id = p.id
LEFT JOIN round r1 ON pg.round_id = r1.id
LEFT JOIN game g ON g.id = r1.game_id
GROUP BY p.name ORDER BY Punkte DESC, Spiele DESC, Runden DESC;

  
Nur als Idee, geht das?  
Eventuell "Punkte/Spiele" noch in eine Funktion (math. Art oder/und Stringfunktion) einpacken...