Wie kombiniere ich diese beiden Abfragen, um die Rangänderung zu berechnen?

Einführung

Ich habe einen Highscore-Tisch für mein Spiel, der Ränge verwendet. Die Punktetabelle repräsentiert aktuelle Highscores und Spielerinformationen und die letzte Tabelle repräsentiert alle kürzlich geposteten Punktzahlen durch einen Benutzer, der ein neuer Top-Punktzahl sein kann oder nicht.

Der Rangabschlag wird berechnet, indem der aktuelle Rang des Spielers minus dem Rang berechnet wird, den sie zum timepunkt des Erreichens ihrer letzten Höchstpunktzahl hatten.

Die Rangsteigerung wird berechnet, indem der Rang des Spielers berechnet wird, den sie zum timepunkt des Erreichens ihrer letzten Höchstpunktzahl hatten, abzüglich des Rangs, den sie zum timepunkt des Erreichens ihrer vorherigen Höchstpunktzahl hatten.

Schließlich, wie im Code geschrieben: $change = ($drop > 0 ? -$drop : $increase);

Frage

Ich verwende die folgenden zwei Abfragen kombiniert mit etwas PHP-Code, um die Rangänderung zu berechnen. Es funktioniert einwandfrei, ist aber manchmal etwas langsam.

Wäre es möglich, die beiden Abfragen + PHP-Code zu optimieren oder zu kombinieren?

Ich habe eine SQL-Fiddle der ersten Abfrage erstellt: http://sqlfiddle.com/#!9/30848/1

Die Tabellen sind bereits mit Inhalt gefüllt, daher sollten ihre Strukturen nicht verändert werden.

Dies ist der aktuelle Arbeitscode:

 $q = " select ( select coalesce( ( select count(distinct b.username) from recent b where b.istopscore = 1 AND ( ( b.score > a.score AND b.time <= a.time ) OR ( b.score = a.score AND b.username != a.username AND b.time < a.time ) ) ), 0) + 1 Rank from scores a where a.nickname = ?) as Rank, t.time, t.username, t.score from scores t WHERE t.nickname = ? "; $r_time = 0; if( $stmt = $mysqli->prepare( $q ) ) { $stmt->bind_param( 'ss', $nick, $nick ); $stmt->execute(); $stmt->store_result(); $stmt->bind_result( $r_rank, $r_time, $r_username, $r_score ); $stmt->fetch(); if( intval($r_rank) > 99999 ) $r_rank = 99999; $stmt->close(); } // Previous Rank $r_prevrank = -1; if( $r_rank > -1 ) { $q = " select coalesce( ( select count(distinct b.username) from recent b where b.istopscore = 1 AND ( ( b.score > a.score AND b.time <= a.time ) OR ( b.score = a.score AND b.username != a.username AND b.time < a.time ) ) ), 0) + 1 Rank from recent a where a.username = ? and a.time < ? and a.score < ? order by score desc limit 1"; if( $stmt = $mysqli->prepare( $q ) ) { $time_minus_one = ( $r_time - 1 ); $stmt->bind_param( 'sii', $r_username, $time_minus_one, $r_score ); $stmt->execute(); $stmt->store_result(); $stmt->bind_result( $r_prevrank ); $stmt->fetch(); if( intval($r_prevrank) > 99999 ) $r_prevrank = 99999; $stmt->close(); } $drop = ($current_rank - $r_rank); $drop = ($drop > 0 ? $drop : 0 ); $increase = $r_prevrank - $r_rank; $increase = ($increase > 0 ? $increase : 0 ); //$change = $increase - $drop; $change = ($drop > 0 ? -$drop : $increase); } return $change; 

Solutions Collecting From Web of "Wie kombiniere ich diese beiden Abfragen, um die Rangänderung zu berechnen?"

Wenn Sie den aktuellen Top-Punktestand in eine neue Tabelle aufteilen, während alle Rohdaten in den aktuellen Punkten verfügbar sind, haben Sie effektiv eine Übersichtstabelle erstellt.

Warum fassen Sie nicht alle benötigten data zusammen und fassen sie zusammen?

Es ist nur ein Fall dessen, was du weißt und wann du es wissen kannst:

  • Aktueller Rang – Hängt von anderen Zeilen ab
  • Rang bei neuer Bestnote – Kann als aktueller Rang berechnet und zum timepunkt der Einfügung / Aktualisierung gespeichert werden
  • Vorheriger Rang in der höchsten Punktzahl – Kann von der alten Position in die neue Top-Punktzahl übertragen werden, wenn eine neue Top-Punktzahl aufgezeichnet wird.

Ich würde Ihre Scores-Tabelle so ändern, dass sie zwei neue Spalten enthält:

  • Scores – ID, Score, Benutzername, Nickname, time, rank_on_update, old_rank_on_update

Passen Sie diese Spalten beim Aktualisieren / Einfügen jeder Zeile an. Offenbar haben Sie bereits Abfragen, mit denen Sie diese data für Ihre erste Iteration anpassen können.

Jetzt werden Ihre Anfragen viel einfacher

Um Rang von der Punktzahl zu bekommen:

 SELECT COUNT(*) + 1 rank FROM scores WHERE score > :score 

Von Benutzername:

 SELECT COUNT(*) + 1 rank FROM scores s1 JOIN scores s2 ON s2.score > s1.score WHERE s1.username = :username 

Und Rangänderung wird:

  $drop = max($current_rank - $rank_on_update, 0); $increase = max($old_rank_on_update - $rank_on_update, 0); $change = $drop ? -$drop : $increase; 

AKTUALISIEREN

  • Kommentar 1 + 3 – Hoppla, das hat vielleicht schon versaut .. hab mich oben geändert.
  • Kommentar 2 – Falsch, wenn Sie die Punktzahlen (die neuesten High-Scores) stets aktuell halten (jedes Mal, wenn ein neuer Highscore aufgezeichnet wird) und davon ausgehen, dass pro Benutzer eine Zeile zum timepunkt der Berechnung aktuell ist Rang sollte einfach eine Punktzahl sein, die höher ist als die Punktzahl des Benutzers (+1). Sollte hoffentlich in der Lage sein, diese verrückte Abfrage zu vermeiden, sobald die data aktuell sind!

Wenn Sie darauf bestehen, nach time zu trennen, funktioniert dies für eine neue Zeile, wenn Sie die Zeile noch nicht aktualisiert haben:

 SELECT COUNT(*) + 1 rank FROM scores WHERE score >= :score 

Die andere Abfrage würde lauten:

 SELECT COUNT(*) + 1 rank FROM scores s1 JOIN scores s2 ON s2.score > s1.score OR (s2.score = s1.score AND s2.time < s1.time) WHERE s1.username = :username 

Aber ich würde zumindest Union für performance versuchen:

 SELECT SUM(count) + 1 rank FROM ( SELECT COUNT(*) count FROM scores s1 JOIN scores s2 ON s2.score > s1.score WHERE s1.username = :username UNION ALL SELECT COUNT(*) count FROM scores s1 JOIN scores s2 ON s2.score = s1.score AND s2.time < s1.time WHERE s1.username = :username ) counts 

Ein Index über (score, time) würde hier helfen.

Persönlich würde ich mir Kopfschmerzen ersparen und die gleichen Noten auf dem gleichen Rang halten (ziemlich Standard, glaube ich). Wenn Sie möchten, dass die Leute erste Prahlereirechte beanspruchen können, stellen Sie sicher, dass Sie nach time ASC auf irgendwelchen Score-Diagrammen bestellen und einschließen die time im Display.

Ich habe viel time damit verbracht herauszufinden, was die Ranglogik ist und habe einen Kommentar dazu gemacht. In der Zwischenzeit finden Sie hier eine Join-Abfrage, die Sie auf Ihren data ausführen können. Ich denke, Ihre Lösung wird etwas in dieser Richtung bewirken:

 SELECT s.username, count(*) rank FROM scores s LEFT JOIN recent r ON s.username != r.username WHERE r.istopscore AND r.score >= s.score AND r.time <= s.time AND (r.score-s.score + s.time-r.time) GROUP BY s.username ORDER BY rank ASC; +----------+------+ | username | rank | +----------+------+ | Beta | 1 | | Alpha | 2 | | Echo | 3 | +----------+------+ 

(Beachten Sie, dass das letzte AND nur dafür sorgt, dass Sie r.score == s.score && r.time == s.time nicht berücksichtigen – was würde ich für ein "tie" Spiel halten?)

Ich bin kein MySQL-Typ, aber ich denke, dass das Verwenden von Self-Join für das Ranking eine schlechte Übung in jedem RDBMS ist. Sie sollten die Verwendung von Ranking-functionen in Betracht ziehen. Aber es gibt keine Ranking-functionalität in MySQL. Aber es gibt Workarounds .

Hier müssen einige Annahmen getroffen werden, um dies voranzutreiben. Ich nehme an, dass die Scores-Tabelle nur einen Eintrag pro 'Benutzername' hat, der irgendwie einem Nickname entspricht.

Versuche dies,

Wenn ich eine funktionierende db hätte, wäre das schnell herauszufinden und zu testing, aber im Grunde nehmen Sie die "Unterabfrage", die Sie im ausgewählten Feld ausführen, und Sie erstellen eine temporäre Tabelle mit ALLEN datasätzen und filtern sie heraus .

  select a.nickname , count(distinct b.username) as rank , t.time , t.username , t.score from ( select a.nickname , b.username from (select * from scores where nickname=? ) a left join (select * from recent where istopscore = 1) as b on ( b.score > a.score and b.time <= a.time -- include the b record if the b score is higher or b.score = a.score and b.time < a.time and a.username != b.username -- include b if the score is the same, b got the score before a got the score ) ) tmp join scores t on (t.nickname = tmp.nickname) where t.nickname = ? 

Ich habe nicht versucht, Ihre spätere Logik zu behandeln, Sie können die gleiche Theorie verwenden, aber es ist keinen Versuch wert, es sei denn, Sie können bestätigen, dass diese Methode die richtigen Zeilen zurückgibt.

Wenn Sie tiefer einsteigen möchten, sollten Sie einige datasätze erstellen und die SQL-Fiddle vollständig einrichten.