按月份分组，并对当前月份和以前所有月份的行进行计数

Question

PostgreSQL 13

假设简化表plans如下，则可以假设每个月至少有一行，有时在同一天有多行：

id	first_published_at
1234678910	2022-10-01 03:58:55.118
abcd1234efg	2022-10-03 03:42:55.118
jhsdf894hld	2022-10-03 17:34:55.118
aslb83nfys5	2022-09-12 08:17:55.118

我会将您的查询用作CTE，并运行一个使用累积sum作为窗口函数的select。

with t as
(
SELECT TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date, 
COUNT(*) AS cnt
FROM plans
WHERE plans.first_published_at IS NOT NULL
GROUP BY publication_date
)
select publication_date, 
sum(cnt) over (order by publication_date) as "count"
from t
order by publication_date desc;

DB fiddle 演示