在Postgresql中的时间序列数据中添加缺少的每月日期

发布时间：2020-12-13 15:53:15 所属栏目：百科来源：网络整理

导读：我在表格中有月度时间序列数据,其中日期是月份的最后一天.数据中缺少某些日期.我想插入这些日期,并为其他属性添加零值. 表如下： id report_date price1 2015-01-31 401 2015-02-28 561 2015-04-30 342 2014-05-31 452 2014-08-31 47 我想将此表转换为 id re

我在表格中有月度时间序列数据,其中日期是月份的最后一天.数据中缺少某些日期.我想插入这些日期,并为其他属性添加零值.
表如下：

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效率不高.

谢谢

解决方法

您可以使用generate_series()生成日期,然后离开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,interval '1' month) + interval '1 day' as report_date
      from m
     ) m left join
     t
     on m.report_date = t.report_date;

第一个CTE只是生成样本数据.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!