PHP rocks! wünscht allen Mitgliedern einen guten Rutsch ins neue Jahr 2017 !!!
Hinweis: Das Forum zieht um! Um keine Datenverluste zu haben, schalten wir zwecks Übernahme der Daten das Forum am Sonntag, den 24.04.2016 um ca. 21:00 Uhr offline und passen anschliessend die DNS-Einträge an.
www.php-rocks.de wird euch dann nach den Aktualisierungen der DNS-Server wieder wie gewohnt uneingeschränkt zur Verfügung stehen.
Danke für euer Verständnis!

Themabewertung:
  • 0 Bewertung(en) - 0 im Durchschnitt
  • 1
  • 2
  • 3
  • 4
  • 5
Ranglisten per MySQL
#1
Information 
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

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
Antworten


Gehe zu: