sql - 如何在postgres中对月份的日期记录进行分组后填补时间间隙

Question

我的表记录为 -

date                n_count
2020-02-19 00:00:00  4
2020-07-14 00:00:00  1
2020-07-17 00:00:00  1
2020-07-30 00:00:00  2
2020-08-03 00:00:00  1
2020-08-04 00:00:00  2
2020-08-25 00:00:00  2
2020-09-23 00:00:00  2
2020-09-30 00:00:00  3
2020-10-01 00:00:00  11
2020-10-05 00:00:00  12
2020-10-19 00:00:00  1
2020-10-20 00:00:00  1
2020-10-22 00:00:00  1
2020-11-02 00:00:00  376
2020-11-04 00:00:00  72
2020-11-11 00:00:00  1

我想将所有记录分组为月份，以查找有效的月份总数，但缺少月份。如何填补这个空白。

time           month_count
"2020-02-01"    4
"2020-07-01"    4
"2020-08-01"    5
"2020-09-01"    5
"2020-10-01"    26
"2020-11-01"    449

这是我尝试过的。

SELECT (date_trunc('month', date))::date AS time,
       sum(n_count) as month_count      
FROM table1
group by time
order by time asc

Answer 1

您可以使用generate_series()生成表中可用的最早日期和最晚日期之间的所有月份开始时间，然后使用左连接引入该表:

select d.dt, coalesce(sum(t.n_count), 0) as month_count      
from (
    select generate_series(date_trunc('month', min(date)), date_trunc('month', max(date)), '1 month') as dt 
    from table1
) as d(dt)
left join table1 t on t.date >= d.dt and t.date < d.dt + interval '1 month'
group by d.dt
order by d.dt