MySQL 按最近一小时递归计数排序

Question

我有一个 SQL 问题。首先，我想知道仅使用 SQL 是否可能，如果不能，是否有人知道一个好的解决方法。

我们正在建立一个网站，用户可以在其中为视频投票。
用户可以通过短信投票或通过Facebook认证后直接现场投票。
我们必须制作所有视频的热门列表，并计算每个视频在列表中的“位置”。

到目前为止，我们已经通过一个简单的子查询完成了这一点，如下所示:

SELECT v.video_id AS id, 
    (SELECT (COUNT(*)+1) FROM videos AS v2 
        WHERE (v2.SMS_votes + v2.facebook_votes) > (v.SMS_votes + v.facebook_votes)) AS total_position 
FROM videos AS v

SMS_votes 和 facebook_votes 是聚合字段。每种投票都有单独的表格，记录每次投票，包括投票设置的时间。

这工作正常，位置是计算出来的...如果 2 个或更多视频具有相同的票数，则它们“共享”位置。

不幸的是，无法进行位置共享，我们必须通过以下规则来解决它:

• 如果两个视频得票数相同，则短信得票数多的视频拥有优势
• 如果他们也有相同数量的短信投票，则在过去一小时内短信投票数较多的一方具有优势
• 如果他们在过去一小时内也有相同数量的短信投票，则将它们与前一小时进行比较，并以此类推，直到两者之间存在差异

是否可以仅在 SQL 中执行这种递归排序，还是必须在代码中手动解决此问题？欢迎所有想法。请注意，性能在这里很重要，因为整个网站都使用顶部列表。

Answer 1

我认为通过递归计算(可能是无界的)来执行这种排序是不可行的，但是如果您愿意限制回顾的时间，可以通过一些方法来完成.

这是一种可能性。

SELECT video_id,
  SMS_votes + facebook_votes AS total_votes,
  SMS_votes,
  COUNT(CASE WHEN time > NOW() - INTERVAL 1 HOUR THEN 1 END) AS h1,
  COUNT(CASE WHEN time > NOW() - INTERVAL 2 HOUR THEN 1 END) AS h2,
  COUNT(CASE WHEN time > NOW() - INTERVAL 3 HOUR THEN 1 END) AS h3
FROM videos
JOIN SMS_votes USING(video_id)
GROUP BY video_id
ORDER BY total_votes DESC, SMS_votes DESC, h1 DESC, h2 DESC, h3 DESC;

这假设您有一个名为 SMS_votes 的表来跟踪每个投票，其中包含 video_id 字段和时间 字段。

对于每个视频，它会计算总票数、短信票数、过去一小时、过去两小时和过去三小时的短信票数。然后，它对所有这些值执行 ORDER BY 以获得正确的位置。

将其扩展为包括更广泛的时间范围相当容易，但您可能还需要考虑在时间倒流时使用增加的时间范围。例如，您首先查看过去一小时的投票，然后查看过去一天的投票，然后查看过去一周的投票，等等。我怀疑这会降低视频获得相同投票的机会，而无需添加那么多额外的计算。

SQL Fiddle example