How do I combine these two queries to calculate rank change?
Introduction
I have a highscore table for my game which uses ranks. The scores table represents current highscores and player info and the recent table represents all recently posted scores by a user which may or may not have been a new top score.
The rank drop is calculated by calculating the player's current rank minus their rank they had at the time of reaching their latest top score.
The rank increase is calculated by calculating the player's rank they had at the time of reaching their latest top score minus the rank they had at the time of reaching their previous top score.
Finally, as written in code: $change = ($drop > 0 ? -$drop : $increase);
Question
I am using the following two queries combined with a bit of PHP code to calculate rank change. It works perfectly fine, but is sometimes a bit slow.
Would there be a way to optimize or combine the two queries + PHP code?
I created an SQL Fiddle of the first query: http://sqlfiddle.com/#!9/30848/1
The tables are filled with content already, so their structures should not be altered.
This is the current working code:
$q = "
select
(
select
coalesce(
(
select count(distinct b.username)
from recent b
where
b.istopscore = 1 AND
(
(
b.score > a.score AND
b.time <= a.time
) OR
(
b.score = a.score AND
b.username != a.username AND
b.time < a.time
)
)
), 0) + 1 Rank
from scores a
where a.nickname = ?) as Rank,
t.time,
t.username,
t.score
from
scores t
WHERE t.nickname = ?
";
$r_time = 0;
if( $stmt = $mysqli->prepare( $q ) )
{
$stmt->bind_param( 'ss', $nick, $nick );
$stmt->execute();
$stmt->store_result();
$stmt->bind_result( $r_rank, $r_time, $r_username, $r_score );
$stmt->fetch();
if( intval($r_rank) > 99999 )
$r_rank = 99999;
$stmt->close();
}
// Previous Rank
$r_prevrank = -1;
if( $r_rank > -1 )
{
$q = "
select
coalesce(
(
select count(distinct b.username)
from recent b
where
b.istopscore = 1 AND
(
(
b.score > a.score AND
b.time <= a.time
) OR
(
b.score = a.score AND
b.username != a.username AND
b.time < a.time
)
)
), 0) + 1 Rank
from recent a
where a.username = ? and a.time < ? and a.score < ?
order by score desc limit 1";
if( $stmt = $mysqli->prepare( $q ) )
{
$time_minus_one = ( $r_time - 1 );
$stmt->bind_param( 'sii', $r_username, $time_minus_one, $r_score );
$stmt->execute();
$stmt->store_result();
$stmt->bind_result( $r_prevrank );
$stmt->fetch();
if( intval($r_prevrank) > 99999 )
$r_prevrank = 99999;
$stmt->close();
}
$drop = ($current_rank - $r_rank);
$drop = ($drop > 0 ? $drop : 0 );
$increase = $r_prevrank - $r_rank;
$increase = ($increase > 0 ? $increase : 0 );
//$change = $increase - $drop;
$change = ($drop > 0 ? -$drop : $increase);
}
return $change;
If you are separating out the current top score into a new table, while all the raw data is available in the recent scores.. you have effectively produced a summary table.
Why not continue to summarize and summarize all the data you need?
It's then just a case of what do you know and when you can know it:
I'd change your scores table to include two new columns:
And adjust these columns as you update/insert each row. Looks like you already have queries that can be used to backfit this data for your first iteration.
Now your queries become a lot simpler
To get rank from score:
SELECT COUNT(*) + 1 rank
FROM scores
WHERE score > :score
From username:
SELECT COUNT(*) + 1 rank
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
WHERE s1.username = :username
And rank change becomes:
$drop = max($current_rank - $rank_on_update, 0);
$increase = max($old_rank_on_update - $rank_on_update, 0);
$change = $drop ? -$drop : $increase;
UPDATE
If you insist on separating by time, this will work for a new row if you haven't updated the row yet:
SELECT COUNT(*) + 1 rank
FROM scores
WHERE score >= :score
The other query would become:
SELECT COUNT(*) + 1 rank
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
OR (s2.score = s1.score AND s2.time < s1.time)
WHERE s1.username = :username
But I'd at least try union for performance:
SELECT SUM(count) + 1 rank
FROM (
SELECT COUNT(*) count
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
WHERE s1.username = :username
UNION ALL
SELECT COUNT(*) count
FROM scores s1
JOIN scores s2
ON s2.score = s1.score
AND s2.time < s1.time
WHERE s1.username = :username
) counts
An index on (score, time) would help here.
Personally I'd save yourself a headache and keep same scores at the same rank (pretty standard I believe).. If you want people to be able to claim first bragging rights just make sure you order by time ASC on any score charts and include the time in the display.
I spent a lot of time trying to figure out what the rank logic is and put in a comment about it. In the meantime, here is a join query that you can run on your data - I think your solution will something something to this effect:
SELECT s.username, count(*) rank
FROM scores s LEFT JOIN recent r ON s.username != r.username
WHERE r.istopscore
AND r.score >= s.score
AND r.time <= s.time
AND (r.score-s.score + s.time-r.time)
GROUP BY s.username
ORDER BY rank ASC;
+----------+------+
| username | rank |
+----------+------+
| Beta | 1 |
| Alpha | 2 |
| Echo | 3 |
+----------+------+
(note that last AND is just to ensure you don't account for r.score==s.score && r.time==s.time - which i guess would be a "tie" game?)
I am not a MySQL guy, but I think that using self-join for ranking is a bad practice in any RDBMS. You should consider using of ranking functions. But there are no ranking functionality in MySQL. But there are workarounds.
链接地址: http://www.djcxy.com/p/31926.html下一篇: 我如何结合这两个查询来计算排名变化？