postgreSQL時系列データの欠損値補完について

Question

前提・実現したいこと

PostgreSQLで時系列データを取り扱っているのですが、一部欠損値のあるデータが存在します。
この欠損値を補完するために、直近で欠損値がないレコードの値を補完させたいのですが、
どのようにすれば実現できるのかが分かりません・・・

どなたかわかる方いらっしゃいますでしょうか。
よろしくお願いいたします。

発生している問題・エラーメッセージ

--現在の出力結果
survey_date  cig_code_curr    
"201904"	 8888
"201905"	 [null]
"201906"	 [null]
"201907"	 1833
"201908"	 [null]
"201909"	 [null]
"201910"	 [null]
"201911"	 [null]
"201912"	 [null]
"202001"	 [null]

--求めている出力結果
survey_date  cig_code_curr    
"201904"	 8888
"201905"	 8888
"201906"	 8888
"201907"	 1833
"201908"	 1833
"201909"	 1833
"201910"	 1833
"201911"	 1833
"201912"	 1833
"202001"	 1833

該当のソースコード

SELECT survey_date, cig_code_curr
FROM single_source.test_table2;

試したこと

以下のコードの様にlag関数をうまく利用すれば値が更新されるかなと考えたのですが、うまくいかず・・・

SELECT 
survey_date,

--lag関数を使い、cig_code_currがNULLの場合、一つ上のレコード値を参照し更新
CASE
WHEN cig_code_curr IS NULL THEN lag(cig_code_curr, 1, 0) OVER (ORDER BY (survey_date))
END

FROM single_source.test_table2;

↓出力結果
survey_date  cig_code_curr    
"201904"	 8888
"201905"	 [null]
"201906"	 [null]
"201907"	 1833
"201908"	 [null]
"201909"	 [null]
"201910"	 [null]
"201911"	 [null]
"201912"	 [null]
"202001"	 [null]

補足情報（FW/ツールのバージョンなど）

OS : windows10 64bit
PostgreSQL : "PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit"

Comments

[sazi]

survey_dateが一意キーなのでしょうか？

[tosshu33]

ご連絡ありがとうございます。
私が持っている実際のテーブルでは本当は複数のキーがあるのですが、
今回は内容簡略化のため、質問ではsurvey_dateを一意のキーとしています。

Answer 1

lag()ではoffsetが指定できますが、値が設定されているのが幾つ前か判別できないと意図通りにはなりません。

直近の有効な値を求める対象のデータを判別しておく必要があります。
結果lag()は使用しません。
※with部分はテーブルの疑似データです。実際のデータを使用する場合は不要です。

with test_table2 as (
  select * from (values
     ('201904',8888)
    ,('201905',null)
    ,('201906',null)
    ,('201907',1833)
    ,('201908',null)
    ,('201909',null)
    ,('201910',null)
    ,('201911',null)
    ,('201912',null)
    ,('202001',null)
  ) w(survey_date, cig_code_curr)    
)
select main.survey_date, coalesce(main.cig_code_curr, sub.cig_code_curr)
from (
    select t1.*
         ,(select max(survey_date)
           from test_table2
           where cig_code_curr is not null and survey_date <= t1.survey_date
         ) as valid_survey_date
    from test_table2 t1
) main 
left join test_table2 sub
on  main.valid_survey_date=sub.survey_date

Comments

[tosshu33]

ありがとうございました。大変助かりました。