在Postgresql中，如何为时间序列数据添加缺失的月份日期？

Question

我有一个时间序列数据表，其中日期是每月的最后一天。数据中有一些日期缺失。我想插入这些日期，并将其他属性的值设为零。 表格如下：

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

我想将这个表格转换为：

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

有没有办法在Postgresql中实现这个功能？ 目前我们是在Python中完成这个任务。随着我们的数据日益增长，仅为了一个任务处理I/O不够高效。 谢谢。

- liferacer

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;

编辑：

事实证明，上述方法并不完全可行，因为在月末添加月份不能保持月底最后一天。

这个问题很容易解决：

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仅用于生成示例数据。

- Gordon Linoff

Answer 2

这比Gordon的查询略有改进，后者在某些情况下无法获取月份的最后日期。

基本上，你需要针对每个 ID 生成 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

示例演示

- Vamsi Prabhala