时间戳

首页 > 解决方案 > 查找客人最后评论并在昨天离开的所有帖子

sql - 查找客人最后评论并在昨天离开的所有帖子

问题描述

各位早安

我正在考虑如何轻松有效地编写它

所以我有帖子表（id，user_id，created_at）和评论表（id，user_id，post_id，created_at）。我需要找到来宾用户（user_id 为 NULL）和昨天留下他们最后评论的所有帖子。所以这些帖子不应该有今天的评论，但应该至少有昨天的评论。昨天的最后一条评论应该由来宾用户添加（此后没有其他评论）。

我能够得到我需要的东西，但是很难编写一个智能查询来有效地执行。结果中的估计记录数超过 1k（我们每天有数千条新帖子和一万条新评论）。

在 created_at 字段上添加索引是否有意义，也许我必须以某种方式非规范化我的数据才能获得良好的结果（最坏情况下最多 500 毫秒）

欢迎任何想法。

谢谢你。

标签： sqlpostgresqlquery-optimizationwhere-clausegreatest-n-per-group

解决方案

您没有显示当前查询。一种选择是使用横向连接来编写它，该连接检索每个 的最后一条评论post_id，然后在where子句中使用过滤器：

select *
from posts p
cross join lateral (
    select c.*
    from comments c
    where c.post_id = p.id
    order by c.created_at desc limit 1
) c
where c.user_id is null

此查询应利用comments(post_id, created_at desc, user_id)（或可能comments(post_id, created_at desc, user_id)）上的索引。

我们还可以尝试使用以下命令预过滤评论表distinct on：

select *
from posts p
inner join (
    select distinct on (post_id) *
    from comments 
    order by post_id, created_at desc
) c on c.post_id = p.id
where c.user_id is null

推荐阅读