Postgresqlの時系列データに欠落している月の日付を追加します

debugcn 投稿 Dev

ライフレーサー

日付が月末日である表に月次時系列データがあります。一部の日付がデータにありません。それらの日付を挿入し、他の属性にゼロ値を設定したいと思います。表は次のとおりです。

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でこれを行っています。データは日々増加しており、1つのタスクだけでI / Oを処理するのは効率的ではありません。

ありがとうございました

ゴードン・リノフ

これを使用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は、サンプルデータを生成することだけです。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]