MySQL数据库设计用于存储用户得分随时间变化的情况。

Question

我正在创建一个网站，所有用户都有每天更新的得分。我可以很容易地从这个得分创建排名，但我想能够创建一周、一个月等的“热门”列表。

我的暴力设计是每天为每个用户计算他们的得分并将其放入“得分”表中。因此，每天得分表会增加与用户数量相同的条目。我可以通过他们的得分变化在任何时间段内对用户进行排名。

虽然我认为这在技术上可以实现，但我觉得一定有更复杂的方法，对吗？还是没有？我感觉一个每天根据用户数量增加的分数表不可能是其他网站所使用的方法。

- maplater

---

一个词：规范化。你的数据库设计必须得到适当的规范化才能正常工作。问问自己：为什么每次用户登录时都需要存储用户数据呢？如果只存储一次用户数据并将每个用户的分数与用户ID一起存储，这不是更好吗？这样更不容易出错，你觉得呢？ - Barranka

我不太确定你的意思，他们的分数每天都会更新，那我怎么只存储一次呢？ - maplater

那些不在线但曾经玩过的用户怎么办？您想将分数记录为历史吗？以数字为基础，您的目标在线用户群体是什么？ - जलजनक

我认为我需要保留已过期用户的分数记录，因为他们的分数对于排名仍然很重要，而且他们的分数可能会在未登录的情况下发生变化。我预计在启动时只有大约10K个用户，但谁知道它会变得多大。我只是觉得我的方法相当粗暴，如果现在有更好的实现方法，我想采用它。 - maplater

Answer 1

您可以通过不存储任何得分快照来获得最大的灵活性。相反，应记录增量分数，因为它们发生了变化。

如果您有这样的表：

用户

- 用户ID - 姓名 - 个人最高分 - {每个用户存储的其他任何内容}

得分日志

- 得分日志ID - 用户ID（参照用户） - 日期时间 - 得分点数

现在，您可以通过像这样的简单查询获取用户的累积分数：

select sum(scored_points)
from SCORE_LOG
where user_id = @UserID
  and date_time <= @PointInTime

您还可以轻松地获取某段时间内的最高排名得分者，类似于以下内容：

select
  user_id
, sum(scored_points)
from SCORE_LOG
group by
  user_id
where date_time >= @StartOfPeriod
  and date_time <= @EndOfPeriod
order by
  sum(scored_points) desc
limit 5

如果你在实际生产中发现性能有问题，那么可以考虑对有意义的统计数据进行快照去规范化。但是这些快照统计数据可能与源数据不同步，因此需要制定定期重新计算快照的策略。

拥有两个真相来源会最终导致两个“真相”，这几乎成为真理（把它看作墨菲定律的推论）。

- Joel Brown

Answer 2

Barranka的评论是正确的，您需要确保在可能的情况下不重复任何数据。

然而，如果您想要恢复一些旧用户的分数或者可能挑选出某一天并查看在某个时间点谁排名第一，例如动态报告，那么您需要在每个记录旁边单独记录日期。为此单独创建一张表格会很有用，因为您可以通过SQL从现有的用户数据中推断出每日得分，并在需要时将其输入到表格中。

您需要做出的决定是要维护多少用户记录以及多长时间。我写下面的内容的想法是“热门列表”将是前五位用户，您可以每天/每月运行CRON作业或计划任务来运行插入操作，并清除非常旧的数据。

用户

• id
• 用户名
• 分数

得分排名

• id
• user_id（我们使用id而不是所有用户信息来规范化）
• 当时的分数
• 排名日期

因此，要生成单个数据排名，您可以插入到此表中。例如：

INSERT INTO
  `score_ranking` (`user_id`, `score_at_the_time`, `date_of_ranking`)
SELECT
  `id`, `score`, CURDATE()
FROM
  `users`
ORDER BY
  `score` DESC
LIMIT
 5

为了读取特定日期（或日期范围）的数据，您可以执行以下操作：

SELECT * FROM score_ranking 
WHERE date_of_ranking = 'somedate' 
ORDER BY score_at_the_time DESC

- AlexP

---

所谓的“热门列表”，是指在某个时间段内得分增长最快的那些人，有点像YouTube每日或每周最受欢迎的视频。那么，要想构建这个列表，难道不必每天在表格中存储每个用户的分数吗？ - maplater

那些在任何时间段内分数增长最多的人。这意味着您需要每次记录所有用户的分数。 - AlexP