在Postgres中嵌套或引用查询

I'm having trouble understanding nested queries. I have a comments table and a posts table and I want to count the number of comments in a given date range for a given site. This part I can do fine, but if there are no comments on one of those dates in the date range then it just skips it.

我无法理解嵌套查询。我有一个评论表和一个帖子表，我想计算给定网站的给定日期范围内的评论数量。这部分我可以做得很好，但是如果日期范围内的其中一个日期没有评论那么它只是跳过它。

So I found a query that builds the full date range and counts the number of comments against that.

所以我找到了一个构建完整日期范围的查询，并根据该查询计算注释数。

SELECT drange.date, count(comm.id)
FROM
    (
        SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
        FROM generate_series(1, 7, 1) AS offs
    ) drange
    LEFT OUTER JOIN
    comments comm ON (
        drange.date=to_char(date_trunc('day', comm.created_at), 'YYYY-MM-DD')
    ) 
GROUP BY drange.date;

Now this returns what partially what I want, but doesn't of course doesn't factor in the posts table.

现在，这将返回我想要的部分内容，但当然不会在posts表中进行考虑。

date         count
2016-01-11   2
2016-01-12   0
....
2016-01-07   1

So I know the count(comm.id) needs to reference inner join posts, but I can't get that to work while still keeping the full date ranges intact. The query below factors in the posts table, but gets rid of the rest of the dates

所以我知道count（comm.id）需要引用内部联接帖子，但是我仍然无法在保持完整日期范围完整的情况下工作。下面的查询在posts表中的因素，但摆脱了其余的日期

SELECT drange.date, count(comm.id)
FROM
    (
      SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
      FROM generate_series(1, 7, 1) AS offs
    ) drange
    LEFT OUTER JOIN comments comm ON (
        drange.date=to_char(date_trunc('day', comm.created_at), 'YYYY-MM-DD')
    )
    INNER JOIN
    posts ON (comm.post_id = posts.id) 
WHERE posts.site_id = 35
GROUP BY drange.date;

Returns:

date        count
2016-01-07  1

So is there a correct way to combine these queries? I don't understand how to reference the inner join results. Any help would be appreciated. Thanks.

那么有没有一种正确的方法来组合这些查询？我不明白如何引用内连接结果。任何帮助，将不胜感激。谢谢。

1 个解决方案

#1

First move the date range series to the right side of the table expression. Second move the where condition to the join condition. Both moves will avoid the nulls exclusion happening in your query.

首先将日期范围系列移动到表格表达式的右侧。第二步将where条件移动到连接条件。这两个步骤都将避免在查询中发生空值排除。

select g.d, count(c.id)
from
    comments c 
    inner join
    posts p on c.post_id = p.id and p.site_id = 35
    right join
    generate_series (
        current_date - 7, current_date, '1 day'
    ) g(d) on g.d = date_trunc('day', c.created_at)
group by g.d

#1

首先将日期范围系列移动到表格表达式的右侧。第二步将where条件移动到连接条件。这两个步骤都将避免在查询中发生空值排除。

select g.d, count(c.id)
from
    comments c 
    inner join
    posts p on c.post_id = p.id and p.site_id = 35
    right join
    generate_series (
        current_date - 7, current_date, '1 day'
    ) g(d) on g.d = date_trunc('day', c.created_at)
group by g.d

秒客网

在Postgres中嵌套或引用查询

1 个解决方案

#1

#1

相关文章