mysql - 返回相对于过去 7 天的 "most commented"列表的单个 SQL 查询，但该列表始终必须包含某些内容

Question

最初看起来很简单的事情现在变得有点脑筋急转弯了。

我正在构建您在新闻/博客网站右侧栏中看到的“评论最多”或“电子邮件发送最多”列表之一。

列表必须与最近的日期范围(比如过去 7 天)相关，因为您希望列表始终看起来新鲜并包含更新的内容。否则，获得大量评论的少数文章将永远位居榜首。

例如，在我的例子中，该网站有几篇文章，每篇都有数百条评论，但其余所有文章都不到 20 条。因此，如果“评论最多”列表是这样提取的:

select
  ArticleId,
  count(CommentId) as Comments
from
  Comment
group by
  ArticleId
order by
  count(CommentId) desc
limit 10;

那么那几篇几百条评论的文章就一直排在最前面。该列表永远不会改变。

所以我重写了选择以包括最近 7 天:

select
  ArticleId,
  count(CommentId) as Comments
from
  Comment
where
  Created >= '2011-06-14'
group by
  ArticleId
order by
  count(CommentId) desc
limit 10;

这看起来好多了，但它仍然行不通，因为它没有考虑过去 7 天内没有事件的情况。

如果在过去 7 天内没有发生任何事情(或只发生了一些评论)，则该列表应该包含之前的事件。底线 - 列表始终需要显示 10 篇文章。

我总是可以调用多个 SQL 语句，以 7 天为增量返回，直到我填满列表，但我不想那样做。我正在将结果缓存在进程内内存中，但我仍然希望尽可能只进行一次 SQL 调用。

想法？我觉得这很容易做到，我只是忽略了明显的地方。

Answer 1

我建议为每个结果计算一个权重并按其排序。例如，您的权重可以是操作次数(评论、电子邮件等)/天数。这样，文章越旧，其权重越低，除非它具有极高的操作率。

类似于:

select
  ArticleId,
  count(CommentId) / (1.0 * DATEDIFF(CURRENT_DATE, Created)) AS weight
from
  Comment
group by
  ArticleId
order by
  weight desc
limit 10;

您需要尝试使用您的权重公式，直到您将较新的内容与稍旧但活跃的内容正确组合。