php - MySQL:选择/排序得分上升最快的条目

Question

我有 2 个表。

第一个包含条目/提交:

+----+-------+------+
| id | name  | user |
+----+-------+------+
| 1  | test  |  25  |
| 2  | foo   |  3   |
| 3  | bar   |  12  |
| 4  | hello |  2   |
| 5  | world |  6   |
+----+-------+------+

第二个包含分数。每个分数的值介于 1 和 5 之间。并非每个条目都有分数。 (假设 'DATETIME' 作为标准日期时间值)

+----+-------+-------+------+---------------------+
| id | score | entry | user | scored_date         |
+----+-------+-------+------+---------------------+
| 1  |   4   |  1    |  5   | 2016-06-05 10:10:00 |
| 2  |   3   |  1    |  12  | 2016-06-05 10:20:00 |
| 3  |   5   |  3    |  6   | 2016-06-05 10:30:00 |
| 4  |   2   |  2    |  23  | 2016-06-05 10:40:00 |
| 5  |   3   |  2    |  46  | 2016-06-05 10:50:00 |
| 6  |   5   |  3    |  14  | 2016-06-05 10:60:00 |
| 7  |   6   |  3    |  32  | 2016-06-05 11:00:00 |
| 8  |   5   |  4    |  9   | 2016-06-05 11:10:00 |
| 9  |   1   |  2    |  4   | 2016-06-05 11:20:00 |
| 10 |   2   |  4    |  2   | 2016-06-05 11:30:00 |
| 11 |   4   |  2    |  18  | 2016-06-05 11:40:00 |
| 12 |   5   |  1    |  8   | 2016-06-05 11:50:00 |
| 13 |   3   |  2    |  32  | 2016-06-05 12:00:00 |
| 14 |   4   |  1    |  28  | 2016-06-05 12:10:00 |
+----+-------+-------+------+---------------------+

我正在尝试根据条目上升的“速度”对条目进行排序。一个很好的例子是像 threadless.com 这样的网站上的“上升”列表 https://www.threadless.com/threadless/designs?status=open&sort=rising

假设当前时间:12:30:00按“上升”排序的条目的预期结果。也许基于最后一个小时 (11:30:00 - 12:30:00)，然后最后 2 小时 (10:30:00 - 12:30:00) 等

Entry table results (Last 1 hour 11:30:00 - 12:30:00)
+----+-----------+
| id | score_sum |
+----+-----------+
| 1  |     9     |    
| 2  |     7     |
| 4  |     2     |
| 3  |     0     |
| 5  |     0     |
+----+-----------+


Entry table results (Last 2 hour 10:30:00 - 12:30:00)
+----+-----------+
| id | score_sum |
+----+-----------+
| 3  |    16     |
| 2  |    13     |
| 1  |     9     |    
| 4  |     7     |
| 5  |     0     |
+----+-----------+

我猜可能需要多个时间范围，因为有些情况下，比如说，过去 1 小时内没有分数，或者过去 3 小时内只有 2 个分数，但 100 个分数过去5个小时。因此，过去 1 小时的结果将不准确或意义不大。

在这个前提下，我想不通这样一条SQL语句背后的逻辑。我也不确定如何组合不同的时间范围以使结果连贯且有意义。

非常感谢任何建议!

Answer 1

如果你的第一个表叫做 foo 而第二个表是 bar - 那么

解决方案 #1:

SELECT foo.id AS id, SUM(COALESCE(bar.score,0)) AS score_sum
FROM foo
LEFT JOIN bar
  ON foo.id = bar.entry
    AND bar.scored_date >= DATE_SUB(NOW(),INTERVAL 1 HOUR)
GROUP BY foo.id
ORDER BY score_sum DESC;

解决方案#2

SELECT foo.id AS id, SUM(COALESCE(bar.score,0)) AS score_sum
FROM foo
LEFT JOIN bar
  ON foo.id = bar.entry
    AND bar.scored_date >= DATE_SUB(NOW(),INTERVAL 2 HOUR)
GROUP BY foo.id
ORDER BY score_sum DESC;

---

要组合它们，使用最长(最包含)的时间段作为 JOIN 条件。然后使用其他条件作为列定义。你可以玩这个 SQLFiddle显示以下代码示例。

SELECT foo.id AS id
    , SUM(COALESCE(bar.score,0) * 
            IF(bar.scored_date >= CAST('2016-06-05 11:00:00' AS DATETIME)
                ,1,0)) AS MereOneHOurInterval
    , SUM(COALESCE(bar.score,0)) AS BiggestInterval
FROM foo
LEFT JOIN bar
  ON foo.id = bar.entry
    AND bar.scored_date >= CAST('2016-06-05 10:00:00' AS DATETIME)
GROUP BY foo.id
ORDER BY id DESC;