Problem mit doppelten Einträgen in Bestenliste

[Skipy]

Hallo,

ich habe eine MySQ-Tabelle mit einer Bestenliste. Bei einem Online-Quiz-Spiel kann jeder hier einen Eintrag mit seinem Namen und dem Highscore hinterlassen.

Die Tabelle sieht vereinfacht so aus:
|| ID_highscore | spielername | letzterUpdate | startzeit | id_schwierigkeitsstufe | joker1_verwendet | joker2_verwendet ||


ID_highscore enthält die ID als Integer
spielername ist ein String
letzterUpdate, startzeit sind UNIXTIMESTAMP
id_schwierigkeitsstufe ein Integer zwischen 1 ... 15
joker1/2_verwendet sind boolean-Werte

So viel zur Struktur. Meine Abfrage soll nun die 20 besten Spieler auflisten und dazu angeben, wie lange sie gebraucht haben und welche Joker alles verwendet wurden.
Sortiert wird die Liste nach

1. Größte ereichte Schwierigkeitsstufe
2. Kürzeste benötigte Zeit für das Spiel
3. Letzter Spieler als als erstes auflisten

Meine bisherige Abfrage sieht so aus:

SELECT id_highscore, spielerName, letzterUpdate AS zeitpunkt, (letzterUpdate-startzeit) AS spieldauer, id_maxSchwierigkeitsttufe AS maxFrage, joker1_verwendet, joker2_verwendet 
FROM highscore h1
WHERE id_maxSchwierigkeitsttufe > 0
ORDER BY maxFrage DESC, spieldauer ASC, zeitpunkt 
DESC LIMIT 20;

Das funktioniert super. Jetzt kommt es allerdings häufig vor, dass ein bestimmter Spieler (immer mit dem gleichen Spielernamen) alle ersten 10 Plätze belegt. Ich will ihn aber nur EINMAL in der Liste haben, und zwar an dem höchsten Platz, den er sich erarbeitet hat.

Ich verzweifle also daran, dass ich den vierten Punkt nicht in meine Abfrage reinbekomme:

• Jeder Spielername nur einmal anzeigen

Ach ja - mittlerweile sind über 40.000 Einträge in der Datenbank, darum sollte die Abfrage auch noch möglichst performant sein ;-)

Hat irgend wer eine gute Idee?

Danke und Grüße,
Skipy

[frauwue]

Schau hier

http://entwickler-forum.de/showthread.php?t=62548

gruß

[Skipy]

Hallo Frauwue,

danke für Deine Antwort! Ja - dieser Gedanke ist super. Den hatte ich auch schon.

Leider ist es bei dieser Abfrage nicht so einfach wie es ausschaut. Denn es wird nicht nach dem neusten Datum gesucht, sondern nach dem Datum mit "dem höchsten Ranking" UND "mit der schnellsten Spielzeit" UND "als letztes Gespielt".
Die angegebene Lösung mit Aussortierung über einen Subselect mit MAX(Datum) fällt darum leider weg. Oder übersehe ich da was?

Grüße,
Skipy

[Falk Prüfer]

Hallo,

...Die angegebene Lösung mit Aussortierung über einen Subselect mit MAX(Datum) fällt darum leider weg. Oder übersehe ich da was?

Was hindert dich daran, statt EINEM "Aussortierkriterium", ZWEI oder DREI oder ... zu verwenden?

Gruß Falk

[Skipy]

Hallo Falk Prüfer,

danke für Deine Antwort.

Was hindert dich daran, statt EINEM "Aussortierkriterium", ZWEI oder DREI oder ... zu verwenden?

Wenn ich das richtig verstanden habe, empfielst Du mir eine Abfrage mit Subselect mit mehreren MAX()? Die SUBSELECT könnte dann so aussehen:

SELECT id_highscore, MAX(id_maxSchwierigkeitsttufe), MAX(letzterUpdate-startzeit), MAX(letzterUpdate) FROM highscore h2 WHERE h2.spielerName=h1.spielername

Wenn ich dass dann Teste, scheint es so, als hätte nur die erste MAX()-Klausel eine Einwirkung auf die Abfrage. Ob ich die anderen beiden jetzt dazu schreibe oder nicht, hat keine Auswirkung.
Ich vermute dass es daran liegt, dass die Abfrage

SELECT id_highscore, MAX(id_maxSchwierigkeitsttufe) FROM highscore;

bereits als Ergebnis NUR noch EINE ZEILE zurückliefert. Die beiden nächsten MAX()-Funkionen werden also nur auf die einzige Zeile angewendet => und haben damit keine Auswirkung.


Ich könnte mit mehreren MAX jetzt die Funktion Stückchenweise aufdrößeln. Das hieße aber, dass ich für jeden "Sortier-Ausdruck" zwei SUBSELECTS benötige. Was nicht gerade performant ist, da sich die Anzahl der Subselect quadratisch erhöht. (Siehe Anhang)


Vielleicht habe ich aber deine Aussage auch ganz falsch interpretiert. Ich bin auf weitere Anregungen gespannt.

Herzliche Grüße,
Skpiy


Anhang:
Das könnte dann so aussehen:

SELECT id_highscore, id_maxSchwierigkeitsttufe FROM highscore WHERE id_maxSchwierigkeitsttufe = (SELECT MAX(id_maxSchwierigkeitsttufe) FROM highscore);

... liefert mir die Liste aller mit MAX(id_highscore)

SELECT id_highscore, spieldauer FROM (SELECT id_highscore, (letzterUpdate-startzeit) AS spieldauer, letzterUpdate FROM highscore WHERE id_maxSchwierigkeitsttufe = (SELECT MAX(id_maxSchwierigkeitsttufe) FROM highscore)) h1 WHERE h1.spieldauer = (SELECT MAX(spieldauer) FROM (SELECT id_highscore, (letzterUpdate-startzeit) AS spieldauer FROM highscore WHERE id_maxSchwierigkeitsttufe = (SELECT MAX(id_maxSchwierigkeitsttufe) FROM highscore)) h2) ;

... liefert mir dann das vorherige Ergebnis zusätzlich gefiltert nach MAX(spieldauer)
... usw.

Das steigert sich aber quadratisch

[dibo33]

Wie sieht denn die Ergebnistabelle deiner bish. Abfrage aus?

Evtl. würde ich hieraus eine View gestalten und von da aus die Bestenliste generieren.

[Skipy]

Aktuell sieht meine Ergebnistabelle so aus:


id_highscore spielerName zeitpunkt spieldauer maxFrage fiftyJoker statistikJoker tippJoker
36473 Tschak 1311765915 98 15 0 0 0
37634 Tschak 1311853060 100 15 0 0 0
36888 Tschak 1311783588 100 15 0 0 0
36481 Tschak 1311766203 100 15 0 0 0
38237 Tschak 1311886027 101 15 0 0 0
36139 Tschak 1311755616 102 15 0 0 0
38013 Tschak 1311869588 103 15 0 0 0
36463 Tschak 1311765627 103 15 0 0 0
38483 Tschak 1311934729 104 15 0 0 0
36857 Tschak 1311781569 104 15 0 0 0
36649 Tschak 1311773495 104 15 0 0 0
36429 Tschak 1311764810 105 15 0 0 0
38081 Tschak 1311874260 106 15 0 0 0
36756 Tschak 1311777312 106 15 0 0 0
36603 Tschak 1311771513 106 15 0 0 0
36542 Tschak 1311769545 106 15 0 0 0
36502 Tschak 1311766984 106 15 0 0 0
36287 Tschak 1311758663 106 15 0 0 0
36280 Tschak 1311758498 106 15 0 0 0
38533 Tschak 1311936968 107 15 0 0 0
37816 Tschak 1311859937 107 15 0 0 0
37762 Tschak 1311857857 107 15 0 0 0
36550 Tschak 1311769752 107 15 0 0 0
36511 Tschak 1311767851 107 15 0 0 0
38651 Tschak 1311941674 108 15 0 0 0

Und wie man schon sieht, möchte ich das "Tschak" nur einmal als oberster Eintrag zu sehen ist :-)

Grüße,
Skipy


Ps.: Ach ja, die Abfrage dazu sieht so aus:

SELECT id_highscore, spielerName, letzterUpdate AS zeitpunkt, (letzterUpdate-startzeit) AS spieldauer, id_maxSchwierigkeitsttufe AS maxFrage, fiftyJoker, statistikJoker, tippJoker
FROM highscore h1
WHERE id_maxSchwierigkeitsttufe > 0 and letzterUpdate >= 1308393679
ORDER BY maxFrage DESC, spieldauer ASC, zeitpunkt
DESC LIMIT 25

[dibo33]

Ich würde dazu noch ein berechnetes Feld aus "spieldauer maxFrage fiftyJoker statistikJoker tippJoker" anlegen in welchen du dir eine entspr. Formel ausdenkst um hier einen eindeutigen Wert zu ermitteln.

Also bspw: spieldauer/maxFrage*fiftyJoker ... , das dann als Punkte speichern.

Daraus eine View erstellen (ohne LIMIT), dann kannst du nach diesem Feld entsprechend die Abfrage machen und nach spielerName gruppieren.

[Skipy]

Ich würde dazu noch ein berechnetes Feld aus "spieldauer maxFrage fiftyJoker statistikJoker tippJoker" anlegen in welchen du dir eine entspr. Formel ausdenkst um hier einen eindeutigen Wert zu ermitteln.

Also bspw: spieldauer/maxFrage*fiftyJoker ... , das dann als Punkte speichern.

Ja das stimmt. So könnte es gehen. Aber das wäre dann nicht so schön sortiert wie ich es mir erhofft hatte. Ich habe gehofft ich würde ohne zusätzlichen Felder auskommen

Es würde dann Sinn machen, wenn ich davon ausgehe, dass es jeweils maximal 1000 unterschiedliche mögliche Werte pro Feld gibt:

Wert1 * 100.000 + Wert 2*1000 + Wert3 * 1 zu nehmen ....

[dibo33]

Wieso, sortieren kannst du doch weiterhin nach deinen Kriterien, die Hilfsspalte dient nur zum ermitteln des besten Wertes eines Spielers.

[Skipy]

Wieso, sortieren kannst du doch weiterhin nach deinen Kriterien, die Hilfsspalte dient nur zum ermitteln des besten Wertes eines Spielers.

Ja, das ist schon richtig. Ich werde es jetzt wohl auch so machen.

Ich habe nur gehofft, dass ich ohne Hilfsspalte auskomme.