简介

我有一个使用排名的游戏高分表。分数表表示当前的高分和玩家信息，最近的表表示用户最近发布的所有分数，这些分数可能是也可能不是新的最高分。

排名下降的计算方法是计算玩家的当前排名减去他们达到最新最高分时的排名。

排名提升的计算方法是计算玩家达到最新最高分时的排名减去他们达到之前最高分时的排名。

最后，如代码所示:$change = ($drop > 0 ? -$drop : $increase);

问题

我使用以下两个查询结合一些 PHP 代码来计算排名变化。它工作得很好，但有时有点慢。

有没有办法优化或组合这两个查询 + PHP 代码？

我创建了第一个查询的 SQL Fiddle: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;

最佳答案

如果您将当前的最高分分离到一个新表中，而所有原始数据都在最近的分数中可用..您就有效地生成了一个汇总表。

为什么不把自己需要的所有数据继续总结总结呢？

这只是一个你知道什么以及什么时候可以知道的例子:

• 当前排名 - 取决于其他行
• 新最高分的排名 - 可以计算为当前排名并在插入/更新时存储
• 以前的最高分排名 - 当记录新的最高分时，可以从旧的“新最高分排名”转移。

我会更改您的分数表以包含两个新列:

• scores - id, score, username, nickname, time, 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

但我至少会尝试 union 来提高性能:

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 排序图表并在显示中包含时间。

关于php - 我如何结合这两个查询来计算排名变化？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35280090/