Moyen efficace de SÉLECTIONNER une plage fixe de scores élevés à partir de l'identifiant d'un utilisateur.

Question

J'ai un tableau qui contient les enregistrements de tous les matchs joués par chaque joueur. J'ai pris une bonne longueur d'avance grâce à la première réponse à cette question : Classement avec des millions d'entrées et j'utilise actuellement cette requête pour récupérer le meilleur score de chaque joueur et son classement :

SET @rank=0;

SELECT user_id, score, rank FROM (
    SELECT *, @rank := @rank + 1 as rank
    FROM high_scores
    JOIN (
        SELECT user_id as user_id2, MAX(score) AS max_score         
        FROM high_scores
        GROUP BY user_id2
        ORDER BY max_score DESC) AS max_score_table                
    ON max_score = high_scores.score AND high_scores.user_id = user_id2) derived_table
ORDER BY rank;

Encore une fois, cela me donne une belle liste ordonnée du meilleur score de chaque joueur et de son rang ; Cependant, j'aimerais également pouvoir fournir un numéro d'identification spécifique. user_id et filtrer les résultats en fonction du score de cet utilisateur ainsi que de X scores environnants supérieurs et inférieurs.

Je pense qu'il me faudrait effectuer une SELECT ... WHERE sur "derived_table" pour le user id et utiliser la valeur de la ligne retournée 'rank' pour filtrer le niveau supérieur SELECT mais, outre le fait que la requête n'est même pas acceptée ("table_dérivée n'existe pas"), la façon dont je procédais m'aurait obligé à interroger deux fois la table_dérivée (pour un test plus grand que et moins grand que), ce qui aurait rendu la requête encore moins efficace qu'elle ne devrait probablement l'être.

Comment puis-je filtrer la liste complète des meilleurs scores et des meilleurs classements pour n'en retenir qu'un seul ? user_id et X entrées environnantes ? Tout commentaire sur le code que j'essaie de mettre au point (ou sur le code que j'ai actuellement) serait grandement apprécié.

Answer 1

SELECT ranks.rank, ranks.user_id, ranks.maxscore FROM (

  -- find the rank of each user by counting the number of
  -- users who have a score greater than that user's highest
  SELECT   currentuser.user_id,
           currentuser.maxscore,
           IFNULL(COUNT(DISTINCT high_scores.user_id),0) rank
  FROM     high_scores JOIN (
    -- current user's highest score
    SELECT user_id, MAX(score) maxscore FROM high_scores GROUP BY user_id
  ) currentuser ON high_scores.score > currentuser.maxscore
  GROUP BY currentuser.user_id

) ranks JOIN (

  -- find the rank of the user we want similarly
  SELECT IFNULL(COUNT(DISTINCT user_id),0) rank
  FROM   high_scores JOIN (
    -- our user's highest score
    SELECT MAX(score) maxscore FROM high_scores WHERE user_id = ?
  ) userscore ON high_scores.score > userscore.maxscore

-- filter for only those who are within given range from our user's rank
) userrank ON ranks.rank BETWEEN userrank.rank - ? AND userrank.rank + ?

-- sort the results
ORDER BY ranks.rank