PostgreSQL:不足している値を補間する
タイムスタンプと値を含むテーブルがPostgreSQLにあります。
「lat」の下の欠落値を補間したいと思います。
「lat」の下の値は、データムより上の潮汐の高さです。この目的のために、2つの既知の値の間で欠落している値を線形に補間することは問題ありません。
PostgreSQLでこれを行うための最良の方法は何ですか?

20200825を編集
私はQGISフィールド計算機を使用して別の方法でこの問題を解決しました。この方法の問題点:時間がかかり、プロセスがクライアント側で実行されるため、サーバー上で直接実行したいと思います。
ステップで、私のワークフローは次のとおりでした。
- 記録された「lat」値の間隔は10分です。2つの記録された値の間の1分あたりの増分を計算し、これを「tidal_step」と呼ばれる追加の列の記録された「lat」値に格納しました。(タイムスタンプも「エポック」として列に保存しました)
QGISの場合:
tidal_step =
-- the lat value @ the epoch, 10 minutes or 600000 miliseconds from the current epoch:
(attribute(get_feature('werkset','epoch',("epoch"+'600000')),'lat') -
-- the lat value @ the current
attribute(get_feature('werkset','epoch',"epoch"),'lat'))
/10
結果として得られるサンプル画像の最初の2つの値:(4.95-5.07)/ 10 = -0.012
- 「lat」値が記録された最後に記録されたインスタンスを過ぎて、補間される「lat」値の分数を決定し、これを「min_past_rec」列に格納しました。
QGISの場合:
left(
right("timestamp",8) --this takes the timestamp and goes 8 charakters from the right
,1) -- this takes the string from the previous right( and goes 1 character left
例の最初の値の場合:2019-01-01 00:15:15戻り値:「5」これは、最後に記録された値の5分後です。
- 最後に記録された「lat」値に( "min_past_rec" * "tidal_step")を追加して欠落値を補間し、これを「lat_interpolated」という列に格納しました。
QGISで
CASE
WHEN "lat" = NULL
THEN
-- minutes pas the last recorded instance:
("min_past_rec" *
-- the "tidal_step" at the last recorded "lat"-value:
(attribute(get_feature('werkset','epoch',
("epoch" - --the epoch of the "lat" value to be interpolated minus:
left(right("timestamp",8),1) * 600000 -- = the amount of minutes after the last recorded instance.
+ left(right("timestamp",6),2) * 1000) -- and the amount of seconds after the last recorded instance.
),'tidal_step')) +
-- the last recorded "lat"-value
(attribute(get_feature('werkset','epoch',("epoch" - left(right("timestamp",8),1) * 600000 + left(right("timestamp",6),2) * 1000)),'lat'))
例のデータを使用して:
2019-01-01 00:17:33:
"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" =
7*-0.012 + 4.95 = 4.866
- データベースから廃止された列を削除する
同じタスクを実行するには、PostgreSQLでどのステートメント/スクリプトを使用する必要がありますか?
回答
私には(部分的な)解決策があります-私がしたことは次のとおりでした(ここで利用可能なフィドルを参照してください):
補間に使用したアルゴリズムは
1のシーケンスがある場合
NULL
は、上の値と下の値の平均を取ります。2
NULL
秒のシーケンスで、一番上に割り当てられた値はその上の2つのレコードの平均であり、一番下に割り当てられた値は下の2つのレコードの平均です。
これを行うために、私は次のことを行いました。
テーブルを作成します。
CREATE TABLE data
(
s SERIAL PRIMARY KEY,
t TIMESTAMP,
lat NUMERIC
);
いくつかのサンプルデータを入力します。
INSERT INTO data (t, lat)
VALUES
('2019-01-01 00:00:00', 5.07),
('2019-01-01 01:00:00', 4.60),
('2019-01-01 02:00:00', NULL),
('2019-01-01 03:00:00', NULL),
('2019-01-01 04:00:00', 4.7),
('2019-01-01 05:00:00', 4.20),
('2019-01-01 06:00:00', NULL),
('2019-01-01 07:00:00', 4.98),
('2019-01-01 08:00:00', 4.50);
レコード3と4および7はNULL
。であることに注意してください。
そして、最初のクエリを実行しました。
WITH cte1 AS
(
SELECT d1.s,
d1.t AS t1, d1.lat AS l1,
LAG(d1.lat, 2) OVER (ORDER BY t ASC) AS lag_t1_2,
LAG(d1.lat, 1) OVER (ORDER BY t ASC) AS lag_t1,
LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
FROM data d1
),
cte2 AS
(
SELECT
d2.t AS t2, d2.lat AS l2,
LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
FROM data d2
),
cte3 AS
(
SELECT t1.s,
t1.t1, t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2,
t1.lead_t1, t2.lead_t2, t1.lead_t1_2
FROM cte1 t1
JOIN cte2 t2
ON t1.t1 = t2.t2
)
SELECT * FROM cte3;
結果(スペースは意味しますNULL
-それはフィドルではるかに明確です):
s t1 lag_t1_2 lag_t1 lag_t2 l1 l2 lead_t1 lead_t2 lead_t1_2
1 2019-01-01 00:00:00 4.60 5.07 5.07 4.60
2 2019-01-01 01:00:00 5.07 4.60 4.60 5.07
3 2019-01-01 02:00:00 5.07 4.60 4.60 4.7
4 2019-01-01 03:00:00 4.60 4.7 4.7 4.20
5 2019-01-01 04:00:00 4.20 4.7 4.7 4.20
6 2019-01-01 05:00:00 4.7 4.20 4.20 4.7 4.98
7 2019-01-01 06:00:00 4.7 4.20 4.98 4.98 4.20 4.50
8 2019-01-01 07:00:00 4.20 4.50 4.98 4.98 4.50
9 2019-01-01 08:00:00 4.98 4.50 4.50 4.98
LAG()およびLEAD()ウィンドウ関数(documentation)の使用に注意してください。同じテーブルで使用しましたが、並べ替えが異なります。
これとOFFSET
オプションの使用は、元の単一のlat
列から、欠落しているNULL
値に値を割り当てるのに非常に役立つ「生成された」データの6つの追加列があることを意味します。パズルの最後の(部分的な)ピースを以下に示します(完全なSQLクエリは、この投稿の下部とフィドルにあります)。
cte4 AS
(
SELECT t1.s,
t1.l1 AS lat,
CASE
WHEN (t1.l1 IS NOT NULL) THEN t1.l1
WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2)
WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL)
AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL) AND (t1.lag_t1 IS NULL)
AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
ELSE 0
END AS final_val
FROM cte3 t1
)
SELECT s, lat, final_val FROM cte4;
最終結果:
s lat final_val
1 5.07 5.07
2 4.60 4.60
3 NULL 4.84
4 NULL 4.45
5 4.7 4.7
6 4.20 4.20
7 NULL 4.59
8 4.98 4.98
9 4.50 4.50
したがって、レコード7の計算値はレコード6と8の平均であり、レコード3はレコード1と2の平均であり、レコード4に割り当てられた値は5と6の平均であることがわかります。および関数のOFFSET
オプションの使用。3秒のシーケンスを取得する場合は、3のシーケンスを使用する必要があります。LAG()
LEAD()
NULL
OFFSET
私はこのソリューションに本当に満足していません-それはNULL
sの数のハードコーディングを含み、それらのCASE
ステートメントはさらに複雑で恐ろしいものになります。理想的には何らかのRECURSIVE CTE
解決策が必要ですが、私はHTHです!
===============================完全なクエリ================= =======
WITH cte1 AS
(
SELECT d1.s,
d1.t AS t1, d1.lat AS l1,
LAG(d1.lat, 2) OVER (ORDER BY t ASC) AS lag_t1_2,
LAG(d1.lat, 1) OVER (ORDER BY t ASC) AS lag_t1,
LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
FROM data d1
),
cte2 AS
(
SELECT
d2.t AS t2, d2.lat AS l2,
LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
FROM data d2
),
cte3 AS
(
SELECT t1.s,
t1.t1, t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2,
t1.lead_t1, t2.lead_t2, t1.lead_t1_2
FROM cte1 t1
JOIN cte2 t2
ON t1.t1 = t2.t2
),
cte4 AS
(
SELECT t1.s,
t1.l1 AS lat,
CASE
WHEN (t1.l1 IS NOT NULL) THEN t1.l1
WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2)
WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL)
AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL) AND (t1.lag_t1 IS NULL)
AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
ELSE 0
END AS final_val,
t1.lead_t1_2
FROM cte3 t1
)
SELECT s, lat, final_val, lead_t1_2 FROM cte4;