简介
我的游戏有一个使用排名的高分表。分数表表示当前高分和玩家信息,最近表表示用户最近发布的所有分数,这些分数可能是也可能不是新的最高分数。
排名下降是通过计算玩家的当前排名减去他们在达到最新最高分时的排名来计算的。
排名提升是通过计算玩家在达到最新最高分时的排名减去他们达到之前最高分时的排名来计算的。
最后,正如代码中所写的:$change = ($drop > 0 ? -$drop : $increase);
问题
我使用以下两个查询结合一些 PHP 代码来计算排名变化。它工作得很好,但有时有点慢。
有没有办法优化或组合两个查询+ PHP代码?
我创建了第一个查询的 SQL 小提琴:http://sqlfiddle.com/#!9/30848/1
表格已经填充了内容,因此不应更改其结构。
这是当前的工作代码:
$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;
如果您将当前最高分分离到一个新表中,而所有原始数据都可以在最近的分数中获得......那么您实际上已经生成了一个汇总表。
为什么不继续汇总和总结您需要的所有数据?
然后这只是你知道什么以及什么时候可以知道它的情况:
- 当前排名 - 取决于其他行
- 新最高分的排名 - 可以计算为当前排名并在插入/更新时存储
- 以前的最高分排名 - 当记录新的最高分时,可以从旧的"新最高分排名"转移。
我会更改您的分数表以包含两列新列:
- 分数 - ID、分数、用户名、昵称、时间、rank_on_update、old_rank_on_update
并在更新/插入每一行时调整这些列。看起来你已经有可用于为第一次迭代回拟此数据的查询。
现在,您的查询变得更加简单
要从分数中获得排名:
SELECT COUNT(*) + 1 rank
FROM scores
WHERE score > :score
从用户名:
SELECT COUNT(*) + 1 rank
FROM scores s1
JOIN scores s2
ON s2.score > s1.score
WHERE s1.username = :username
排名变化变为:
$drop = max($current_rank - $rank_on_update, 0);
$increase = max($old_rank_on_update - $rank_on_update, 0);
$change = $drop ? -$drop : $increase;
更新
- 评论 1 + 3 - 哎呀,可能搞砸了.. 上面已经改变了。
- 注释 2 - 不正确,如果您动态保持分数(所有最新的高分(是最新的(每次记录新的高分时(,并假设每个用户有一行,则在计算时,当前排名应该只是高于用户分数(+1(的分数计数。一旦数据是最新的,应该希望能够避免这种疯狂的查询!
如果您坚持按时间分隔,则在尚未更新新行的情况下,这将适用于新行:
SELECT COUNT(*) + 1 rank
FROM scores
WHERE score >= :score
另一个查询将变为:
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
但我至少会尝试工会的性能:
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
关于(score, time)
的索引会有所帮助。
就我个人而言,我会省去头痛,并将相同的分数保持在相同的排名(我相信相当标准(。如果您希望人们能够声称首先吹牛的权利,只需确保您在任何分数表上按时间ASC排序,并在显示中包含时间。
我花了很多时间试图弄清楚排名逻辑是什么,并对此发表评论。同时,这里有一个可以对数据运行的联接查询 - 我认为您的解决方案将达到这种效果:
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 |
+----------+------+
(请注意,最后一个 AND 只是为了确保您不考虑 r.score==s.score && r.time==s.time - 我想这将是一个"平局"游戏?(
我不是MySQL的人,但我认为在任何RDBMS中使用自连接进行排名都是不好的做法。您应该考虑使用排名函数。但是MySQL中没有排名功能。但是有一些解决方法。
推进这项工作,这里必须做出一些假设。 我假设分数表的每个"用户名"只有一个条目,这在某种程度上相当于一个昵称。
试试这个,
如果我有一个工作数据库,这将很快弄清楚和测试,但基本上你正在接受你在所选字段中运行的"子查询",你正在构建一个包含所有记录的临时表并过滤掉它们。
select a.nickname
, count(distinct b.username) as rank
, t.time
, t.username
, t.score
from
(
select
a.nickname
, b.username
from (select * from scores where nickname=? ) a
left join (select * from recent where istopscore = 1) as b
on (
b.score > a.score and b.time <= a.time -- include the b record if the b score is higher
or
b.score = a.score and b.time < a.time and a.username != b.username -- include b if the score is the same, b got the score before a got the score
)
) tmp
join scores t on (t.nickname = tmp.nickname)
where t.nickname = ?
我没有尝试解决你后面的逻辑,你可以使用相同的理论,但除非你能确认这种方法返回正确的行,否则不值得尝试。
如果你想更深入,你应该创建一些数据集并完全设置SQL Fiddle。