在PostgreSQL中查找过去n个月中每个月的新记录 [英] Find new records for each of the past n months in PostgreSQL

问题描述

我有一个带有创建日期的记录表。我想返回过去6个日历月中每个月的新记录，包括本月的部分记录。我非常了解SQL Server，但对PostgreSQL不太熟悉。

I have a table of records with a createddate in them. I would like to return the new records for each of the last 6 calendar months, including the partial this month. I know SQL Server well but am not as familiar with PostgreSQL.

我已经可以通过以下查询获得滚动月份的数据：

I have been able to get data for rolling months with this query:

select 
COUNT(ID) as Total,
COUNT(CASE WHEN createddate between (now() - '1 month'::interval)::timestamp AND now() THEN AG.ID END) as ThisMonth,
COUNT(CASE WHEN createddate between (now() - '2 month'::interval)::timestamp AND (now() - '1 month'::interval)::timestamp THEN AG.ID END) as LastMonth,
COUNT(CASE WHEN createddate between (now() - '3 month'::interval)::timestamp AND (now() - '2 month'::interval)::timestamp THEN AG.ID END) as PrevMonth,
COUNT(CASE WHEN createddate between (now() - '4 month'::interval)::timestamp AND (now() - '3 month'::interval)::timestamp THEN AG.ID END) as PrevMonth2,
COUNT(CASE WHEN createddate between (now() - '5 month'::interval)::timestamp AND (now() - '4 month'::interval)::timestamp THEN AG.ID END) as PrevMonth3,
COUNT(CASE WHEN createddate between (now() - '6 month'::interval)::timestamp AND (now() - '5 month'::interval)::timestamp THEN AG.ID END) as PrevMonth4
FROM a_group AG

但是在6/21上，它将返回5 / 22-6 / 21、4 / 22-5 / 21等数据。

我想按以下方式存储数据：6/1 -6/21（部分月份），5 / 1-5 / 31等。

But on 6/21, this will return data from 5/22-6/21, 4/22-5/21, etc.
I would like the data to bucket as follows: 6/1-6/21 (partial current month), 5/1-5/31, etc.

有什么建议吗？我也怀疑我可以循环执行此操作，但对语法还不熟悉。目前，我正在PostgreSQL Maestro上针对备份文件进行测试。

Any suggestions? I also suspect I could do this in a loop but am not familiar enough with the syntax yet. For now, I am testing this from PostgreSQL Maestro against a backup file.

谢谢。

推荐答案

我认为 date_trunc 函数可能是您的朋友（请参见 postgres文档）。我猜你会做这样的事情：

I think the date_trunc function may be your friend (see postgres docs). You would do something like this I guess:

select 
COUNT(ID) as Total,
COUNT(CASE WHEN createddate between date_trunc('month', now()) AND now() THEN AG.ID END) as ThisMonth,
COUNT(CASE WHEN createddate between date_trunc('month', now()) - interval '1 month' AND date_trunc('month', now()) - interval '1 day' THEN AG.ID END) as LastMonth,

等...

这篇关于在PostgreSQL中查找过去n个月中每个月的新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！