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

MySQLrank

I am writing an MVP system based on votes from users. The table will look like

vote     | game | year
--------- ------ ------
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

An entry here equates to a vote. So out of this we would group and get a count of

Player 1 = 3
Player 2 = 3
Player 3 = 2
Player 4 = 1

The MVP system is a "3-2-1" score system. So points are awarded to the players in reverse order. So it should only apply winning scores to the top 3 positions. i.e.

1st gets 3 points
2nd gets 2 points
3rd gets 1 point

This gets really complicated when there is a tie but top 3 should get MVP points.

In the case above the point spread would be

Player 1 = 2.5 points
Player 2 = 2.5 points
Player 3 = 1 point

This is worked out because the top 2 players are effectively 1st and 2nd, there is no second so the next person gets 1 point for placing 3rd.

Effectively (points = points_available / players_in_rank)

however that equation is wrong. It gets extra complicated if we have a tie for 3rd, where you could possibly end up with lots of people in 3rd spot and therefore they get 1 point divided by them.

It is really complicated when you have ties in 1st or second. I have worked around this poorly in PHP code but would like to be able to do this in SQL and do away with PHP.

Just wondering if anyone has any simple methods for this?

These scores points are then later added to an overall standings table

Regards
Dan

Best Answer

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

Related Solutions

Mysql – Get the rank of a user in a score table

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.

MYSQL score by rank

Using the techniques described here to rank items in MySQL, you can calculate the scores you need using a derived table and some hackery with user variables.

Here's your table with the sample data you provided:

CREATE TABLE blah (
    id INT
  , value INT
);

INSERT blah (id, value)
VALUES 
    (1, 3)
  , (2, 5)
  , (3, 2)
  , (4, 5)
;

And here's the query you need:

-- find the min and max ranks for later use
SELECT
    @min_rank := 1 AS min_rank
  , @max_rank := (SELECT COUNT(DISTINCT value) FROM blah) AS max_rank
;

-- use the calculated min and max ranks to calculate score
SELECT
    id
  , value
  , rank
  , 10.0 * (@max_rank - rank) / (@max_rank - @min_rank) AS score
FROM (
  SELECT
      id
    , value
    , @curr_rank := IF(@prev_rank = value, @curr_rank, @curr_rank + 1) AS rank
    , @prev_rank := value
  FROM blah, (SELECT @curr_rank := 0) r, (SELECT @prev_rank := NULL) p
  ORDER BY value DESC
) ranked_blah;

The output I get for the second query on SQL Fiddle is as follows:

| ID | VALUE | RANK | SCORE |
-----------------------------
|  2 |     5 |    1 |    10 |
|  4 |     5 |    1 |    10 |
|  1 |     3 |    2 |     5 |
|  3 |     2 |    3 |     0 |

Note that this approach will work best if you have an index on value. This will let you find @max_rank quickly using the COUNT technique @ypercube described.

Best Answer

Related Solutions

Mysql – Get the rank of a user in a score table

MYSQL score by rank

Related Question