mysql - 每小时发布平均评论

Question

SELECT p.*,
    u.user_id,
    u.user_name,
    count(c.comment_post_id) AS comments
FROM posts AS p
LEFT JOIN comments AS c
    ON (p.post_id = c.comment_post_id)
LEFT JOIN users AS u
    ON (p.postedby_id = u.user_id)
WHERE c.comment_added > NOW() - INTERVAL 1 HOUR
GROUP BY p.post_id    
ORDER BY count(c.comment_post_id) DESC

这会生成一个按最近一小时评论最多的帖子排序的列表。现在的问题是，最后一小时评论为 0 的帖子不在列表中。

那么有没有办法让它按照评论创建后每小时的平均评论排序？这样所有的帖子都会出现在列表中，并且讨论最多的帖子将始终排在最前面。

http://sqlfiddle.com/#!9/820044/1/0

编辑:

SELECT p.*, u.user_id, u.user_name, COUNT(c.comment_post_id) / (TIMESTAMPDIFF(HOUR, p.post_timestamp, SYSDATE()) + 1) AS rate FROM posts AS p LEFT JOIN comments AS c ON (p.post_id = c.comment_post_id) LEFT JOIN users AS u ON (p.postedby_id = u.user_id) GROUP BY p.post_id ORDER BY COUNT(c.comment_post_id) / (TIMESTAMPDIFF(HOUR, p.post_timestamp, SYSDATE()) + 1) DESC

这就是解决问题的原因。

Answer 1

由于 WHERE 子句，您只选择在一小时内有评论的帖子。您可以尝试的是:

SELECT p.*,
    u.user_id,
    u.user_name,
    SUM(c.comment_added > NOW() - INTERVAL 1 HOUR) AS comments
FROM posts AS p
LEFT JOIN comments AS c
    ON (p.post_id = c.comment_post_id)
LEFT JOIN users AS u
    ON (p.postedby_id = u.user_id)
GROUP BY p.post_id    
ORDER BY 
    COUNT(c.comment_post_id) / 
    (TIMESTAMPDIFF(HOUR, p.post_timestamp, SYSDATE()) + 1) DESC

对于在一小时内的每个评论， bool 表达式 c.comment_added > NOW() - INTERVAL 1 HOUR 将为 SUM(...) 贡献一个.

对于第二部分，我们可以找到使用 TIMESTAMPDIFF(HOUR, p.post_timestamp, SYSDATE()) 发布帖子后经过了多少小时。因此，将评论数除以小时数，即可得出排序标准。但是我们在小时数上加一以避免被零除。