MySQL ranking one user based on cell value

MySQL

I got to figure out how to optimally check for a users rank in all the stages he has passed in a game. The rank is based on the amount of time (float) it took for the player to pass the stage.

What I've got is this schema: http://www.sqlfiddle.com/#!9/3764e/1

id| fTime | stage |
-------------------
1 | 11.123| first |
1 | 12.561| second|
1 | 13.333| third |
2 | 11.642| first |
2 | 12.231| second|
2 | 13.999| third |
3 | 11.000| first |
3 | 12.842| second|
3 | 13.849| third |
4 | 11.535| first |
4 | 12.653| second|
4 | 13.145| third |
5 | 11.653| first |
5 | 12.134| second|
5 | 13.678| third |
6 | 11.546| first |
6 | 12.325| second|
6 | 13.532| third |

The result that I need is the players rank in each stage he has passed, based on his time in it (fTime).

|     id|     stage| rank|
|      1|   'first'|    1|
|      1|  'second'|    2|
|      1|   'third'|    1|

The way I've been doing this thus far, is by querying the stages the player has finished, then the time he has achieved in each stage, and then getting his rank on that stage. This is obviously not optimal, as I got to do a lot of trips to the mysql server and back, when it could be done in way less queries.

I've been looking around the net for issues like this, but haven't found anything that quite matches, and this goes beyond my SQL skills..

Best Answer

There are two basic ways to do it:

A join (usually preferred in MySQL)

SELECT a.id, a.stage, count(b.id)+1 AS rank
FROM playertimes a 
LEFT JOIN playertimes b on (a.stage=b.stage and a.fTime > b.fTime)
group by a.id, a.stage;

And a dependent subquery which may be a lot slower in some cases but might work OK for you

SELECT id, stage, 
  (
    select count(1)+1
    from playertimes b
    where a.stage=b.stage and a.fTime > b.fTime
  ) AS rank
FROM playertimes a
order by id, stage;

You can check it at http://www.sqlfiddle.com/#!9/2d9580/3 - I added an index on (stage, fTime) which can make it faster in both cases.

Both solutions will assign the same rank to players with the same time (if it can happen at all) and then skip the "unused" ranks - like 1, 2, 2, 2, 5, 6, 6, 8.

Related Solutions

MySQL: sysbench test – InnoDB vs Memory tables

I have addressed InnoDB vs MEMORY before

Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jan 17, 2012 : Mysql Memory table getting many locks

I also addressed the one headache MEMORY tables have: There is still disk I/O because of the .frm file (See my post I am using the MEMORY storage engine but MySQL still writes to my disk...Why?).

Also worth remembering is the fact that MEMORY tables do table-level locks.

You should just increase the InnoDB buffer pool and leave MEMORY tables behind

Mysql – Selecting, ranking and applying a point score based on rank

Sample data:

CREATE TABLE v
    (`vote` varchar(8), `game` int, `year` int)
;

INSERT INTO v
    (`vote`, `game`, `year`)
VALUES
    ('player 1', 2, 2015),
    ('player 1', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 3', 2, 2015),
    ('player 1', 2, 2015),
    ('player 3', 2, 2015),
    ('player 4', 2, 2015)
;

Query:

SELECT 
vote,
no_votes,
rank,
3 - rank + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points
FROM (

    SELECT 
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC

) q

Result:

|     VOTE | NO_VOTES | RANK | POINTS |
|----------|----------|------|--------|
| player 1 |        3 |    1 |    2.5 |
| player 2 |        3 |    1 |    2.5 |
| player 3 |        2 |    2 |      2 |
| player 4 |        1 |    3 |      1 |

see it working live in an sqlfiddle

Explanation:

This

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

is simple enough, I hope :)

This

, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery

is just a subquery to initialize some variables.

Here

    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes

in the first row @prevNoVotes holds the value of the previous row (or the value from the initialization subquery). @rc1, @rc2 and @rc3 are just rank counters. They count for each rank, how many people are on the same rank.
In the last row, the value of the current row is assigned to the variable. When the next row is processed, the variable will hold the value of the previous row in the first line of this part of the code.

And finally in the most outer part of the query we calculate the points distribution.

Feel free to ask any questions :)

UPDATE:

Added this table:

CREATE TABLE points
    (`r` int, `p` int)
;

INSERT INTO points
    (`r`, `p`)
VALUES
    (1, 3),
    (2, 2),
    (3, 1)
;

Added more logic :)

SELECT 
vote,
no_votes,
rank,
@rc := @rc + 1 AS rowcounter,
((SELECT p FROM points WHERE r = IF(rank = @prevRank, rank, @rc)) - 1) + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points,
@prevRank := rank
FROM (
    SELECT 
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC
)q
, (SELECT @rc := 0, @prevRank := 1) row_counter_init
ORDER BY rank

see it working live in this sqlfiddle

Best Answer

Related Solutions

MySQL: sysbench test – InnoDB vs Memory tables

Mysql – Selecting, ranking and applying a point score based on rank

Related Question