I'm trying to get a users ranking getting his highest performances in every beatmap.
I get the user highest performance in every beatmap (only taking the top 5 performances) and adding them together, but it fails when the highest performance in one beatmap is repeated... because it counts twice
I'm based in this solution, but it doesn't works well for me...
Using MySQL 5.7
What i'm doing wrong?
Using this code:
SET group_concat_max_len := 1000000;
SELECT @i:=@i+1 rank, x.userID, x.totalperformance FROM (SELECT r.userID, SUM(r.performance) as totalperformance
FROM
(SELECT Rankings.*
FROM Rankings INNER JOIN (
SELECT userID, GROUP_CONCAT(performance ORDER BY performance DESC) grouped_performance
FROM Rankings
GROUP BY userID) group_max
ON Rankings.userID = group_max.userID
AND FIND_IN_SET(performance, grouped_performance) <= 5
ORDER BY
Rankings.userID, Rankings.performance DESC) as r
GROUP BY userID) x
JOIN
(SELECT @i:=0) vars
ORDER BY x.totalperformance DESC
Expected result:
+------+--------+------------------+
| rank | userID | totalperformance |
+------+--------+------------------+
| 1 | 1 | 450 |
+------+--------+------------------+
| 2 | 2 | 250 |
+------+--------+------------------+
| 3 | 5 | 140 |
+------+--------+------------------+
| 4 | 3 | 50 |
+------+--------+------------------+
| 5 | 75 | 10 |
+------+--------+------------------+
| 6 | 45 | 0 | --
+------+--------+------------------+
| 7 | 70 | 0 | ----> This order is not relevant
+------+--------+------------------+
| 8 | 76 | 0 | --
+------+--------+------------------+
Actual Result:
+------+--------+------------------+
| rank | userID | totalperformance |
+------+--------+------------------+
| 1 | 1 | 520 |
+------+--------+------------------+
| 2 | 2 | 350 |
+------+--------+------------------+
| 3 | 5 | 220 |
+------+--------+------------------+
| 4 | 3 | 100 |
+------+--------+------------------+
| 5 | 75 | 10 |
+------+--------+------------------+
| 6 | 45 | 0 | --
+------+--------+------------------+
| 7 | 70 | 0 | ----> This order is not relevant
+------+--------+------------------+
| 8 | 76 | 0 | --
+------+--------+------------------+
As you have mentioned that you are picking only top 5 performances per user across beatmaps then you can try this way:
select @i:=@i+1, userid,performance from (
select userid,sum(performance) as performance from (
select
@row_number := CASE WHEN @last_category <> t1.userID THEN 1 ELSE @row_number + 1 END AS row_number,
@last_category :=t1.userid,
t1.userid,
t1.beatmapid,
t1.performance
from (
select
userid, beatmapid,
max(performance) as performance
from Rankings
group by userid, beatmapid
) t1
CROSS JOIN (SELECT @row_number := 0, @last_category := null) t2
ORDER BY t1.userID , t1.performance desc
) t3
where row_number<=5
group by userid
)
t4 join (SELECT @i := 0 ) t5
order by performance desc
Above query will not consider duplicate Performance Score and pick only top 5 performance values.