时间戳

首页 > 解决方案 > 如何在 mysql 中优化我的排名查询？

mysql - 如何在 mysql 中优化我的排名查询？

问题描述

针对每个订阅 ID 为每个日期保存多条记录。有硬币栏，所有订阅者获得少量硬币。它基本上是一个奖励系统。我想获得当前订阅者的排名。有 2 个表正在使用中。

  1. user_rewards（用硬币保存所有记录）
  2. coin_history （如果使用硬币，那么我将使用过的硬币添加到硬币历史表中）

例如，如果用户总共获得了 1000 个硬币，它在用户奖励表中仍然是 1000，如果他在某处使用了 200 个硬币，我在硬币历史表中添加一个条目。所以目前他还剩下800个硬币。我将不得不找到当前硬币的排名。

例如，我的用户奖励中有 5 条记录存在于 3 个订阅者 ID 中。我想根据他的订阅 ID 获得一个订阅者的排名。

+-----------------+--------------+-------+
| subscription_id | date_awarded | coins |
+-----------------+--------------+-------+
| 525252 | 2020-09-11 | 10 |
| 454545 | 2020-09-11 | 20 |
| 989898 | 2020-09-11 | 10 |
| 525252 | 2020-09-10 | 50 |
| 454545 | 2020-09-10 | 30 |
+-----------------+--------------+-------+

订阅id525252有60币，989898有10币，454545有50币。但是订阅 id525252已经使用40了他的硬币，40硬币被添加到硬币历史表中。所以硬币454545排名第一，50硬币525252排名第二，20硬币989898排名第三10。

所以为了获得用户的排名。我从用户奖励表中获得硬币总和，然后用硬币历史表减去该订阅 ID。然后根据他当前未使用的硬币找到排名。下面是我的查询，查询耗时 34.0794 秒。

SELECT COUNT(*)+1 AS aboveRank 
FROM 
(
   SELECT subscription_id  
   FROM user_rewards 
   GROUP BY subscription_id 
   HAVING SUM(coins) > 
    (Select (SELECT IFNULL(sum(coins), 0)  
             FROM user_rewards 
             WHERE subscription_id = 525252) - 
            (SELECT IFNULL(sum(coins), 0) as coins 
             FROM `coins_history` 
             where subscription_id = 525252) as z
    ) 
) t

注意：我在一个月内有大约 800000 条记录，并且每天都会不断增加，所以需要优化我当前的查询，我正在使用 MySQL 和 PHP。

标签： mysqlsql

解决方案

union all您可以使用和计算每个订阅者的总硬币数group by。然后使用窗口函数进行排名：

select c.*
from (select subscription_id, sum(coins) as coins,
             rank() over (order by coins desc) as seqnum
      from ((select subscription_id, - sum(coins) as coins
             from user_rewards ur
             group by subscription_id
            ) union all
            (select subscription_id, sum(coins)
             from coins_history ch
             group by subscription_id
            ) 
           ) c
       group by subscription_id
      ) c
where subscription_id = ?;

推荐阅读