Mysql – User rank based on cumulative rows

mariadbmariadb-10.1MySQLrank

UPDATE:
Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1

Table structure (some fields are hidden):

+---------+---------+------------+-------------+-------+-------+
| user_id | kind_id | country_id | province_id | value | score |
+---------+---------+------------+-------------+-------+-------+
|       1 |     201 |          1 |           1 |     1 |     5 |
|       1 |     102 |          1 |           1 |    31 |   155 |
|       1 |     201 |          1 |           1 |     6 |    30 |
|       1 |     109 |          1 |           1 |    33 |   165 |
|       2 |     202 |          1 |           4 |    76 |   380 |
|       2 |     201 |          1 |           4 |     5 |    25 |
|       2 |     209 |          1 |           4 |     9 |    45 |
|       2 |     302 |          1 |           4 |     8 |    40 |
|       2 |     201 |          1 |           4 |    14 |    70 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|       3 |     201 |          1 |           9 |     9 |    45 |
|       3 |     201 |          1 |           9 |     2 |    10 |
|       3 |     201 |          1 |           9 |     5 |    25 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|     ... |     ... |        ... |         ... |   ... |   ... |
+---------+---------+------------+-------------+-------+-------+

Rank is calculated based on sum of score of user's activities.
I saw many questions here and on Stackoverflow but my problem is more complex.

I need 3 rank values per user: global, country (for users in same country), province (same as country). For example a rank value for user 1 may be like this {"global": 9, "country": 3, "province": 1}. I do not need rank of all users but for one individual user.

I tried a dozen queries but can't calculate this three with one query. or, at least, three clean and efficient queries.

I will cache the result for some time, but still efficiency is important since the activities table contains many records and will grow as time passes.

How can I do this?

Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Best Answer

Using window ranking functions. I used RANK() but you could as well use DENSE_RANK() or ROW_NUMBER() depending on what you want to happen on ties.

Unfortunately, window functions have been introduced in MariaDB 10.2, so you'll have to upgrade from 10.1, in order to use this:

SELECT
    user_id, country_id, province_id,
    SUM(score) AS total_score,
    RANK() OVER (ORDER BY SUM(score) DESC)
      AS rank_total,
    RANK() OVER (PARTITION BY country_id
                 ORDER BY SUM(score) DESC)
      AS rank_country,
    RANK() OVER (PARTITION BY country_id, province_id
                 ORDER BY SUM(score) DESC)
      AS rank_province
FROM activities 
GROUP BY 
    country_id, province_id, user_id
ORDER BY 
    rank_total ;

Tested at dbfiddle.uk

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 – 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 – Get the rank of a user in a score table

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

Related Question