Hallo,
es kommt ja ab und zu mal die Frage auf, wie man per MySQL und PHP eine Rangliste erstellen kann.
Code und Queries sind hier zu finden: https://github.com/P-Voss/Ranglisten
Verschiedene Möglichkeiten das Problem anzugehen, die ich kenne:
Den Rang berechnen, indem andere Datensätze mit dem gleichen oder besseren Ergebnis gezählt werden
Vorteile
Nachteile
Die Liste nach dem Ergebnis sortieren und den Rang anhand der jeweiligen Zeilenzahl festlegen
src: http://fellowtuts.com/mysql/query-to-obtain-rank-function-in-mysql/
Vorteile
Nachteile
Ich habe die Abfragen noch nicht für größere Datenmengen getestet, weiß also nicht welche Version am Ende performanter sein könnte, bei meinen Testdaten hat die erste Version die Nase klar vorn. Beim deaktivierten Query-Cache verhalten sie sich in etwa gleich. *1
Im Git-Repository ist noch eine Query bei der die Punktzahl über mehrere 'Runden' pro Spieler summiert werden, sowie eine bei der diese Summierten Werte auch nochmal zum Team gruppiert werden.
Ergänzungen, Korrekturen und Ideen wie immer erwünscht.
Nachtrag:
*1)
Bei einer Datenmenge von ~300k Zeilen in der results-Tabelle ist die "berechnende" Variante immer noch schneller, sobald der Cache angelegt ist.
Dauer der ersten Abfrage - ungecached
Berechnende Query: ~1500 ms
Zählende Query: ~250 ms
Nächste Abfragen - gecached
Berechnende Query: 0.15 bis 0.3 ms
Zählende Query: ~250 ms
es kommt ja ab und zu mal die Frage auf, wie man per MySQL und PHP eine Rangliste erstellen kann.
Code und Queries sind hier zu finden: https://github.com/P-Voss/Ranglisten
Code:
-- Table structure for `persons`
DROP TABLE IF EXISTS `persons`;
CREATE TABLE `persons` (
`person_id` int(11) NOT NULL AUTO_INCREMENT,
`vorname` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`nachname` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`team_id` int(11) NOT NULL,
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
-- Table structure for `result_simple`
DROP TABLE IF EXISTS `result_simple`;
CREATE TABLE `result_simple` (
`result_id` int(11) NOT NULL AUTO_INCREMENT,
`person_id` int(11) NOT NULL,
`points` int(11) NOT NULL,
PRIMARY KEY (`result_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
Verschiedene Möglichkeiten das Problem anzugehen, die ich kenne:
Den Rang berechnen, indem andere Datensätze mit dem gleichen oder besseren Ergebnis gezählt werden
Code:
SELECT
main.person_id AS Person_ID,
persons.vorname AS Vorname,
persons.nachname AS Nachname,
main.points AS Punkte,
(SELECT
COUNT(*)+1
FROM
result_simple sub
WHERE
sub.points > main.points
) AS Rang
FROM
result_simple main
LEFT JOIN persons
ON main.person_id = persons.person_id
ORDER BY rang, persons.person_id
Vorteile
- Die Datenbank greift auf den Query-Cache zu (!)
- Die Liste muss nicht sortiert werden
Nachteile
- Die Abfrage kann schnell unübersichtlich werden
Die Liste nach dem Ergebnis sortieren und den Rang anhand der jeweiligen Zeilenzahl festlegen
src: http://fellowtuts.com/mysql/query-to-obtain-rank-function-in-mysql/
Code:
SELECT
rangliste.person_id AS Person_ID,
persons.vorname AS Vorname,
persons.nachname AS Nachname,
rangliste.points AS Punkte,
rangliste.rang AS Rang
FROM
(SELECT
points,
person_id,
@curRank := IF(@prevRank = points, @curRank, @incRank) AS rang,
@incRank := @incRank + 1,
@prevRank := points
FROM
result_simple,
(SELECT
@curRank :=0,
@prevRank := NULL,
@incRank := 1) AS count
ORDER BY
points DESC, person_id
) AS rangliste
LEFT JOIN persons
ON rangliste.person_id = persons.person_id
Vorteile
- Die Abfrage bleibt relativ übersichtlich, da der Rang-Abzählung die restliche Query ziemlich egal ist
Nachteile
- Der Query-Cache wird nicht verwendet
- Sortierung nach einem anderen Kriterium als den Rang erfordert weiteren Subselect
Ich habe die Abfragen noch nicht für größere Datenmengen getestet, weiß also nicht welche Version am Ende performanter sein könnte, bei meinen Testdaten hat die erste Version die Nase klar vorn. Beim deaktivierten Query-Cache verhalten sie sich in etwa gleich. *1
Im Git-Repository ist noch eine Query bei der die Punktzahl über mehrere 'Runden' pro Spieler summiert werden, sowie eine bei der diese Summierten Werte auch nochmal zum Team gruppiert werden.
Ergänzungen, Korrekturen und Ideen wie immer erwünscht.
Nachtrag:
*1)
Bei einer Datenmenge von ~300k Zeilen in der results-Tabelle ist die "berechnende" Variante immer noch schneller, sobald der Cache angelegt ist.
Dauer der ersten Abfrage - ungecached
Berechnende Query: ~1500 ms
Zählende Query: ~250 ms
Nächste Abfragen - gecached
Berechnende Query: 0.15 bis 0.3 ms
Zählende Query: ~250 ms