使用 SQL Server 设计可扩展的积分排行榜系统

Question

我正在寻找有关扩展积分排行榜系统的建议。我已经有了一个使用非常标准化策略的工作版本。第一个版本本质上是一个看起来像这样的表格。

UserPoints - PK: (UserId,Date)
+------------+--------+---------------------+  
| UserId     | Points | Date                |  
+------------+--------+---------------------+  
| 1          | 10     | 2011-03-17 07:16:36 |  
| 2          | 35     | 2011-03-17 08:09:26 |  
| 3          | 40     | 2011-03-17 08:05:36 |  
| 1          | 65     | 2011-03-17 09:01:37 |  
| 2          | 16     | 2011-03-17 10:12:35 |  
| 3          | 64     | 2011-03-17 12:51:33 |  
| 1          | 300    | 2011-03-17 12:19:21 |  
| 2          | 1200   | 2011-03-17 13:24:13 |  
| 3          | 510    | 2011-03-17 17:29:32 |  
+------------+--------+---------------------+  

然后我有一个存储过程，它基本上执行 GroupBy UserID 并对点求和。我还可以传递 @StartDate 和 @EndDate 参数来创建特定时间段的排行榜。例如，日/周/月/生命周期中顶级用户的时间窗口。

这似乎适用于中等数量的数据，但当点记录数量超过一百万左右时，速度明显变慢。我正在使用的测试数据是大约 500 个用户在 3 个月的时间内创建的超过 100 万点记录。

有没有不同的方法来解决这个问题？我尝试过通过将点预先分组到小时日期时间桶中来对数据进行非规范化，以减少行数。但我开始认为我需要担心的真正问题是排行榜中需要考虑的用户数量不断增加。时间窗口大小通常很小，但越来越多的用户将开始在任何给定窗口内生成点。

不幸的是，我无法访问“作业”，因为我使用的是 SQL Azure 并且代理尚不可用。但是，如果您足够有说服力，我愿意使用不同的存储系统来扩展它。

我过去的工作经验告诉我我应该研究数据仓库，因为这几乎是一个报告问题。但同时我需要它尽可能实时。

更新

最后，我希望支持每周周一上午 8 点至周五下午 6 点的自定义排行榜。但这是未来的事情，也是为什么我尽量不要对聚合太过着迷。我现在愿意接受基本的日/周/月/年/所有时间窗口。

棘手的部分是我真的无法存储它们非规范化，因为我需要这些窗口是时区可转换的。该系统是多租户的，因此所有数据都以 UTC 形式存储。问题是对于不同的客户来说，一周的开始时间不同。将总和汇总在一起会导致某些点落入错误的桶中。

Answer 1

这里有一些想法：

1. 坚持使用 SQL Azure：您可以有另一个表，PointsTotals。每次向 UserPoints 表中添加一行时，也会增加 PointsTotals 中给定 UserId 的 TotalPoints 值（如果没有要增加的行，则插入新行）。现在，您始终可以计算每个 UserId 的总数。
2. 使用Azure表存储：创建一个UserPoints表，分区键为userId。这会将用户的所有积分行放在一起，您可以轻松地对它们进行求和。而且...您可以借用建议#1 中的想法，创建一个单独的 PointsTotals 表，其中 PartitionKey 是 UserId，RowKey 可能是总分。

Answer 2

如果这是我的问题，我会忽略时间戳并按天存储用户和积分总数

Answer 3

我决定采用存储点以及本地化到客户当前时区设置的时间跨度（StartDate 和 EndDate 列）的想法。我意识到这样做的一个额外好处是，我可以在几个月后“清除”旧的排行榜回合数据，而不会影响一生的总积分。