在每个月开始之前获取所有条目的col计数

Question

给定一个具有两个特性的数据集：ID和created_at(date(。对于数据集中包含的所有月份，获取每个月开始前创建的所有ID的总和的最佳方法是什么？例如，2021年5月的值必须是2021年4月或更早创建的所有ID的计数

类似于：

Year | Month | Count
--------------------
2019 | 3     | 5
2019 | 4     | 8

Answer 1

您可以在计算月份合计后使用窗口函数，如下所示：示例表：

create table test (id int, created_at date);
insert into test values(1,'2021-06-13');
insert into test values(2,'2021-01-13');
insert into test values(3,'2021-02-13');
insert into test values(4,'2021-02-13');

步骤1：计算按月计数：

select date_trunc('month', created_at) "month_", count(*) "count_" from test

第二步：使用汇总窗口功能，对上述查询结果进行前几个月的汇总计算。

查询

select 
extract(year from month_) "Year",
extract(month from month_) "Month", 
coalesce(sum(count_) over (order by month_ rows between UNBOUNDED preceding and 1 preceding), 0) "Count" 
from (
select date_trunc('month', created_at) "month_", count(*) "count_" from test
group by 1) t1

输出：

Year    Month   Count
2021    1        0
2021    2        1
2021    6        3

演示