在PostgreSQL中查找过去n个月的每个新记录

Question

我有一个记录表，其中包含createddate。我想返回过去6个日历月中每个月的新记录，包括本月的部分记录。我很了解SQL Server，但对PostgreSQL不太熟悉。

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

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等

有什么建议吗？我还怀疑我可以在循环中完成这项工作，但对语法还不够熟悉。现在，我正在PostgreSQL Maestro的备份文件中测试这个。

谢谢。

Answer 1

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

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,

等等。。。