在SQL Server中使用线性回归实现"Trending Item“

Question

我正在建立一个MVC .NET网站，人们可以在那里对电影进行评分和评论(类似于IMDb)。我用SQL Server做后端。

我将实现一个功能，主页显示趋势电影，即在特定时间内被正面评价的电影(假设持续7天)。

我将使用一个实现线性回归的存储过程来实现这一点。我的相关SQL表如下：

• Movie - PK Movie_ID，存储电影信息
• User - PK User_ID，存储用户信息
• Rating -以以下形式存储分级实例：

PK Rating_ID Movie_ID FK Movie_ID User_ID RatingNo RatingNo Updated_Dt 1 5 10 7 15/12 2014年16:04:24

上面的行将转换为“用户ID为10，而ID为5的电影在2014年12月15日(16:04:24)的评分为7/10”。

我也有一个函数来计算AVG_Rate = sum of all ratings (for a given movie) / count of ratings (for a given movie)形式的电影的平均分数。

我的问题是:考虑到上面提供的表格结构，实现“本周流行电影”的最佳方式是什么？我现在能想到两种方法：

• 创建一个永久表，以便在以下方面运行线性回归：

Movie_ID _ AVG_Rate _ Updated_Dt

每当在Rating中添加新的评等行时，也要在新表中插入新行，但不要存储实际的评等，而是存储新计算的AVG_Rate：

Movie_ID AVG_Rate Updated_Dt 1 7.8 15/12/14 17:00 1 7.6 15/12/14 17:01:00 1 7.9 15/12/14 17:00:00

...And等。然后，我可以使用新的表来检查给定电影的线性回归。如果表在一段时间内变得太大，我可以让该表在任何特定时间只保存7天的数据(例如每7天备份一次，删除一周以上的数据)。

• 创建一个临时表，以便在以下方面运行线性回归：

与上面的逻辑相同，只是每当调用获取趋势电影的存储过程时，表都是临时创建的。每个电影的AVG_Rate更改可以直接从Rating表中计算。

我怀疑哪种方法最有效(我在SQL中不是高级的)，也不确定这两种解决方案中的任何一种都是可行的。

如有任何帮助和指导，将不胜感激。谢谢!

Answer 1

我可能会这样做的第三种方式，这是通过添加一列到您的评等表，其值是平均评级后的评级被应用。这意味着计算的这一阶段只需要预置一次，而不是每次执行存储过程时都需要预格式化，从而带来了更好的性能。从技术上讲，这会导致数据库没有完全规范化，但由于只有历史数据不应该改变，所以我不会考虑这个大问题。

当然，这类似于您的第一个建议，但避免了信息重复到单独的表中。

Answer 2

我认为这是视图的完美用例，换句话说，是由数据库中真实表上的SQL查询定义的“虚拟表”。

默认情况下，每次需要在查询上执行查询时，都会从实际数据中生成视图，但有些RDMS支持缓存/预计算/等视图。Oracle将这些视图称为“物化视图”，而SQL Server则称它们为“索引视图”。

为了更具体一点，使用Server 2008索引视图提高性能说：

索引视图可以通过以下方式提高查询性能：

• 聚合可以预先计算并存储在索引中，以减少查询执行期间的昂贵计算。
• 表可以预连接，结果数据集可以存储。
• 可以存储联接或聚合的组合。

您可能不需要他们声称的任何大的性能改进，但这使我们对Server的索引视图的功能有了具体的了解，因此我认为可以断言索引视图在功能上与创建额外表的选项1相当，而非索引视图在功能上相当于每次创建临时表的选项2。不过，在常规视图和索引视图之间切换可能要比在永久表和临时表之间切换要容易得多，因此至少您可能会节省一些时间，以这种方式试验您的选项。

这就是答案在这个主题上有一些更有用的链接。