時系列テーブルの欠損値の補完方法

Question

前提・実現したいこと

PostgreSQLにある時系列テーブルの一部カラムの欠損値を補完をする方法を探しています。
下記の例ように一部カラムに欠損値がある場合に直近に存在する有効な値で補完させたいです。
レコードに欠損値をなくすことを目的としているので、更新処理が必要になります。

補完対象のテーブル例

time_id    data1    data2
20200501   1000     2000
20200502   [null]   [null]
20200503   1200     [null]
20200504   [null]   3000

補完後の目標のテーブル

time_id    data1    data2
20200501   1000     2000
20200502   1000     2000
20200503   1200     2000
20200504   1200     3000

試したこと

検索すると値の取得のみであればウィンドウ関数を駆使すればできそうでしたが、更新が必要になるため実現方法を模索中です。
SQLに限らずアドバイス等頂ければ幸いです。

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

PostgreSQL(9.6.5)

Comments

[退会済みユーザー]

欠損値のない状態で処理することが多いのであれば、欠損値を埋める処理を別途検討の上で対応することをおすすめします。
おそらく、欠損値を埋めるのをリアルタイムにクエリー実行時に行うとサブクエリーを駆使して結構な負荷がかかりそうで、そういうリクエストがどのくらいの処理オーダーなのかによってはデータベースに掛かる負担も爆上げになりそうな気がします。処理で詰めたデータかどうかを判別するフラグも持たせればいいわけで。

[horaotank]

ご連絡ありがとうございます。
SQLで解決するのはDB負荷的に難しいということで欠損値があるレコードを作成しないようにする方向で対応しようと思います。

Answer 1

ウィンドウ関数で副問い合わせでSELECT文を作りその値でUPDATEする方法です。
PostgreSQLはIGNORE NULLSに対応してなかったようなので、
ありとなしで2パターン用意してあります。

負荷的には例に上がっている4件のデータで試しただけなので、
大量にデータがある場合に使えるかは何とも言えません。

またSAMPLE_TABLEはこちらで勝手に定義した仮のテーブル名です。

IGNORE NULLS使用

UPDATE SAMPLE_TABLE S1
SET (DATA1, DATA2) = (
SELECT PREV_DATA1, PREV_DATA2
FROM (
  SELECT
    SAMPLE_TABLE.*,
    LAST_VALUE(DATA1 IGNORE NULLS) OVER(ORDER BY TIME_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PREV_DATA1,
    LAST_VALUE(DATA2 IGNORE NULLS) OVER(ORDER BY TIME_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PREV_DATA2
  FROM SAMPLE_TABLE
  ) S2
  WHERE S1.TIME_ID = S2.TIME_ID
);

IGNORE NULLS不使用

UPDATE SAMPLE_TABLE S1
SET (DATA1, DATA2) = (
SELECT PREV_DATA1, PREV_DATA2
FROM (
  SELECT 
    S.*,
    FIRST_VALUE(DATA1) OVER (PARTITION BY GRP1) AS PREV_DATA1,
    FIRST_VALUE(DATA2) OVER (PARTITION BY GRP2) AS PREV_DATA2
  FROM (
    SELECT
      SAMPLE_TABLE.*,
      SUM(CASE WHEN DATA1 IS NOT NULL THEN 1 END) OVER (ORDER BY TIME_ID) AS GRP1,
      SUM(CASE WHEN DATA2 IS NOT NULL THEN 1 END) OVER (ORDER BY TIME_ID) AS GRP2
      FROM SAMPLE_TABLE
    ) S
  ) S2
  WHERE S1.TIME_ID = S2.TIME_ID
);

Comments

[horaotank]

ご回答ありがとうございます。
レコードが少ないテーブルでは問題なく実行できましたが、レコードの多いテーブルだと実行時間的に実用できそうにありませんでした。
レコード追加時に欠損値がある状態を作らないようにする方向で再検討してみようと思います。