自上个月以来获得最多评论的 SQL 查询

Question

我有两个表，一个包含帖子，另一个包含评论，有数百万个帖子和 100 多个评论，评论表还包含帖子的 ID。评论会在一段时间后停用，我想知道在停用前的过去 30 天内哪些帖子的评论最多。

我要做的是从评论表中为每个帖子找到 max(comment_date) 并计算每个帖子从该日期开始 1 个月后的所有评论。

所以基本上我想按 post_id 分组, 找到 max(comment_date)并计算来自 max(comment_date) - 1 month 的所有评论对于每个帖子。我正在努力创建查询来获取这些数据？

数据库是postgres 9.4.1。

Answer 1

在该数据量上，查询将需要时间。一种方法是使用窗口函数:

select post_id, count(*)
from (select c.*, max(comment_date) over (partition by post_id) as maxcd
      from comments c
     ) c
where comment_date >= maxcd - interval '1 month'
group by post_id;