Z0q February 2016

How do I combine these two queries to calculate rank change?

Introduction

I have a highscore table for my game which uses ranks. The scores table represents current highscores and player info and the recent table represents all recently posted scores by a user which may or may not have been a new top score.

The rank drop is calculated by calculating the player's current rank minus their rank they had at the time of reaching their latest top score.

The rank increase is calculated by calculating the player's rank they had at the time of reaching their latest top score minus the rank they had at the time of reaching their previous top score.

Finally, as written in code: $change = ($drop > 0 ? -$drop : $increase);


Question

I am using the following two queries combined with a bit of PHP code to calculate rank change. It works perfectly fine, but is sometimes a bit slow.

Would there be a way to optimize or combine the two queries + PHP code?

I created an SQL Fiddle of the first query: http://sqlfiddle.com/#!9/30848/1

The tables are filled with content already, so their structures should not be altered.

This is the current working code:

$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
                                           

Answers


gfunk February 2016

I spent a lot of time trying to figure out what the rank logic is and put in a comment about it. In the meantime, here is a join query that you can run on your data - I think your solution will something something to this effect:

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 |
+----------+------+

(note that last AND is just to ensure you don't account for r.score==s.score && r.time==s.time - which i guess would be a "tie" game?)


Dmitriy Dokshin February 2016

I am not a MySQL guy, but I think that using self-join for ranking is a bad practice in any RDBMS. You should consider using of ranking functions. But there are no ranking functionality in MySQL. But there are workarounds.


Michael Blood February 2016

There are some assumptions that have to be made here in order to move forward with this. I assume that the scores table has only one entry per 'username' which is somehow equivalent to a nickname.

Try this,

If I had a working db, this would be quick to figure out and test, but basically you are taking the 'sub query' you are running in the selected field and you are building a temp table with ALL of the records and filtering them out.

       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 = ?

I did not attempt to address your later logic, you can use the same theory, but it is not worth trying unless you can confirm that this method returns the correct rows.

If you would like to get deeper, you should create some data sets and fully setup the SQL Fiddle.


Arth February 2016

If you are separating out the current top score into a new table, while all the raw data is available in the recent scores.. you have effectively produced a summary table.

Why not continue to summarize and summarize all the data you need?

It's then just a case of what do you know and when you can know it:

  • Current rank - Depends on other rows
  • Rank on new top score - Can be calculated as current rank and stored at time of insert/update
  • Previous rank on top score - Can be transferred from old 'rank on new top score' when a new top score is recorded.

I'd change your scores table to include two new columns:

  • scores - id, score, username, nickname, time, rank_on_update, old_rank_on_update

And adjust these columns as you update/insert each row. Looks like you already have queries that can be used to backfit this data for your first iteration.

Now your queries become a lot simpler

To get rank from score:

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

From username:

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

And rank change becomes:

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

UPDATE

  • Comment 1 + 3 - Oops, may have messed that up.. have changed above.
  • Comment 2 - Incorrect, if you keep the scores (all the latest high-scores) up to date on the fly (every time a new high-score is recorded) and assuming there is one row per user, at the time of calculation current rank should simply be a count of scores higher than the user's score (+1). Should hopefully be able to avoid that crazy query once the dat

Post Status

Asked in February 2016
Viewed 1,260 times
Voted 10
Answered 4 times

Search




Leave an answer