2

I have a table consisting of records of every match that every player has played. I've gotten a pretty good head start from the top answer of this question: Ranking with millions of entries, and am currently using this query to retrieve each player's highest score and their rank:

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;

Again, this gives me a nice ordered list of each player's top score and its rank; However, I'd also like to be able to supply a specific user_id and filter the results down to this user's score as well as X amount of surrounding higher and lower scores.

I would think I'd need to perform a SELECT ... WHERE on "derived_table" for the user id and use the returned row's 'rank' to filter the top-level SELECT statement, but in addition to the query not even being accepted ('derived_table doesn't exist'), the way I was doing it would have required me to re-query derived_table twice (for a greater-than and less-than test), making the query even less efficient than it probably should be.

How can I filter the full list of high scores and ranks down to a single user_id's entry and X amount of surrounding entries? Any insight on the code I'm trying to come up with (or the code I currently have) would be greatly appreciated.

Community
  • 1
  • 1
Johnny Cage
  • 37
  • 4

1 Answers1

0
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
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Thanks for your quick and thorough reply, eggyal. I've spent some time trying to get this implemented, but it seems that `@r := IF(score <=> @l, @r, @c) rank` is returning a `BLOB` rather than a number, despite the fact that I would think that on the first row it would return either `NULL` or `@c` (which is properly returning a number and not a string). Aside from `@r` assigning itself to its returned `BLOB` value after the first iteration, I'm not sure where any non-numerals are coming into play. Does this problem seem familiar to you? – Johnny Cage Oct 22 '12 at 09:26
  • @JohnnyCage: You could explicitly convert the result to an integer with `@r := CAST(IF(score <=> @l, @r, @c) AS SIGNED) rank`. – eggyal Oct 22 '12 at 09:30
  • Thanks again! I've got another one for you -- I do see that query orders the results by `ranks.rank`, and it does output each user's highest score, but which part of the query actually sorts the list by the highest score? The rank returned by the `userrank` subquery appears to be accurate, but the full list of high scores seems to be ranked in no particular order. My first instinct was to add an `ORDER BY maxscore DESC` into the `ranks` subquery, but all this does is sort the incorrectly ranked list of scores before it is re-sorted by rank at the end. – Johnny Cage Oct 22 '12 at 10:02
  • @JohnnyCage: Good spot! Suggest you rank all users as per the `userrank` subquery (else you *could* order `maxscores` in a subquery, as you had in your original, but I don't think MySQL guarantees that such behaviour will work as intended). See update, above. – eggyal Oct 22 '12 at 16:33
  • @JohnnyCage: In terms of efficiency, I think you will want to index *both* `(user_id, score)` *and* `(score, user_id)`. If you're still not getting adequate performance, you might consider cacheing the ranked usertable (and automatically updating it with triggers whenever `high_score` is updated). – eggyal Oct 22 '12 at 16:38
  • Thanks a ton, eggyal. After making minimal changes (it may have been me mis-implementing it, but it seemed like a couple of the `ranks.*` values weren't making it up to the main query result) I seem to be getting the results I want! – Johnny Cage Oct 23 '12 at 02:23
  • @JohnnyCage: No, you're absolutely right. I just hastily copy-pasted the query from below. – eggyal Oct 23 '12 at 06:37