Add Missing Month Dates in Timeseries Data in Postgresql

Question

I have monthly time series data in a table where the dates are the last day of the month. Some data are missing from the data. I want to insert these dates and put a null value for other attributes. The table is as follows:

id     report_date   price
1       2015-01-31    40
1       2015-02-28    56
1       2015-04-30    34
2       2014-05-31    45
2       2014-08-31    47

I want to convert this table to

id     report_date   price
1       2015-01-31    40
1       2015-02-28    56
1       2015-03-31    0
1       2015-04-30    34
2       2014-05-31    45
2       2014-06-30    0
2       2014-07-31    0
2       2014-08-31    47

Is there a way to do this in Postgresql? We are currently doing this in Python. Because our data is growing day by day and inefficient for processing I / O for only one task.

thank

Answer 1

, generate_series(), , left join, :

with m as (
      select id, min(report_date) as minrd, max(report_date) as maxrd
      from t
      group by id
     )
select m.id, m.report_date, coalesce(t.price, 0) as price 
from (select m.*, generate_series(minrd, maxrd, interval '1' month) as report_date
      from m
     ) m left join
     t
     on m.report_date = t.report_date;

EDIT:

, , .

:

with t as (
      select 1 as id, date '2012-01-31' as report_date, 10 as price union all
      select 1 as id, date '2012-04-30', 20
     ), m as (
      select id, min(report_date) - interval '1 day' as minrd, max(report_date) - interval '1 day' as maxrd
      from t
      group by id
     )
select m.id, m.report_date, coalesce(t.price, 0) as price 
from (select m.*, generate_series(minrd, maxrd, interval '1' month) + interval '1 day' as report_date
      from m
     ) m left join
     t
     on m.report_date = t.report_date;

CTE .

Answer 2

, .

min max ( generate_series) left join , 0.

with minmax as (
      select id, min(report_date) as mindt, max(report_date)  as maxdt
      from t
      group by id
     )
select m.id, m.report_date, coalesce(t.price, 0) as price 
from (select *, 
      generate_series(date_trunc('MONTH',mindt+interval '1' day),
                      date_trunc('MONTH',maxdt+interval '1' day), 
                      interval '1' month) - interval '1 day' as report_date
      from minmax
     ) m 
left join t on m.report_date = t.report_date

Sample Demo