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

Question

我有一个记录表，其中有一个创建日期。我想返回过去 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 函数可能是您的 friend (参见 postgres docs )。我猜你会做这样的事情:

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,

等...