基於累積行的使用者排名

更新： 完整的表架構和數據：https ://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1

表結構（部分欄位被隱藏）：

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

score排名是根據使用者活動的總和計算的。我在這里和 Stackoverflow 上看到了很多問題，但我的問題更複雜。

我需要每個使用者 3 個等級值：全球、國家（針對同一國家/地區的使用者）、省（與國家/地區相同）。例如，使用者 1 的排名值可能是這樣的{"global": 9, "country": 3, "province": 1}。我不需要所有使用者的排名，只需要一個個人使用者。

我嘗試了十幾個查詢，但無法用一個查詢計算這三個。或者，至少，三個乾淨高效的查詢。

我會將結果記憶體一段時間，但效率仍然很重要，因為活動表包含許多記錄，並且會隨著時間的推移而增長。

我怎樣才能做到這一點？

伺服器版本：10.1.37-MariaDB-0+deb9u1 Debian 9.6

使用視窗排名函式。我用過RANK()，但你也可以使用DENSE_RANK()或ROW_NUMBER()取決於你想在領帶上發生什麼。

不幸的是，MariaDB 10.2 中引入了視窗函式，因此您必須從 10.1 升級才能使用它：

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 ;

在**dbfiddle.uk測試**

引用自：https://dba.stackexchange.com/questions/231777