1

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

  • Which version of MariaDB or MYSQL do you use? – ypercubeᵀᴹ Mar 10 at 13:28
  • @ypercubeᵀᴹ Its Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6 – ma3x Mar 10 at 17:58
  • @ypercubeᵀᴹ I can also change that to MySQL 5.7.x if needed. But not 8.x. – ma3x Mar 10 at 18:40
  • @ypercubeᵀᴹ do you have any suggestions? – ma3x Mar 10 at 20:39
  • 1
    I Updated my question with a link to the fiddle. Your fiddle contains DDL and sample data. Your fiddle do not contain (in comment /* */ form) desured output for this sample data. And (because CTE and window functions are preferred) specify your server version. – Akina Mar 11 at 18:13
0

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

0

I don't think I understand your question, but you may benefit from a rollup. MySQL have there own version of grouping sets that look like:

SELECT country_id
     , province_id
     , user_id
     , SUM(score) AS total_score 
FROM activities 
GROUP BY country_id, province_id, user_id
WITH ROLLUP;

null in user_id column means an aggregation, dito för province_id. I.e.

1   null    null    64390

is the total for country_id = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.