PostgreSQL: interpola il valore mancante
Ho una tabella in PostgreSQL con un timestamp e un valore.
Vorrei interpolare i valori mancanti in "lat".
Il valore sotto "lat" sono le altezze di marea sopra un dato. Allo scopo va bene interpolare il valore mancante lineare tra i due valori noti.
Qual è il metodo migliore per farlo in PostgreSQL?

Modifica 20200825
Ho risolto questo problema in un modo diverso utilizzando il calcolatore di campo QGIS. Problema con questo metodo: richiede molto tempo e il processo viene eseguito sul lato client e vorrei eseguirlo direttamente sul server.
Nei passaggi, il mio flusso di lavoro era:
- L'intervallo tra i valori "lat" registrati è di 10 minuti. Ho calcolato l'incremento al minuto tra due valori registrati e l'ho memorizzato in una colonna aggiuntiva chiamata "tidal_step" al valore "lat" registrato. (ho memorizzato il timestamp anche come "epoca" in una colonna)
In 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
per i primi due valori dall'immagine di esempio che risulta in: (4.95 - 5.07) / 10 = -0.012
- Ho determinato la quantità di minuti del valore "lat" da interpolare, dopo l'ultima istanza registrata in cui è stato registrato un valore "lat" e l'ho memorizzato in una colonna: "min_past_rec"
In 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
per il primo valore nell'esempio: 2019-01-01 00:15:15 restituisce: '5' Questo è 5 minuti dopo l'ultimo valore registrato.
- Ho interpolato i valori mancanti aggiungendo ("min_past_rec" * "tidal_step") all'ultimo valore "lat" registrato e l'ho memorizzato nella colonna "lat_interpolated"
In 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'))
Con i dati dell'esempio:
01-01-2019 00:17:33:
"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" =
7*-0.012 + 4.95 = 4.866
- eliminare le colonne obsolete dal database
Quali istruzioni / script dovrei usare in PostgreSQL per eseguire la stessa operazione?
Risposte
Ho una soluzione (parziale) - quello che ho fatto è stato il seguente (vedi il violino disponibile qui ):
L'algoritmo che ho usato per l'interpolazione era
se c'è una sequenza di 1
NULL
, prendi la media del valore sopra e il valore sotto.Una sequenza di 2
NULL
s, il valore assegnato in alto è la media dei due record sopra di esso e quello assegnato in basso è la media dei due record sotto.
Per fare ciò, ho fatto quanto segue:
Crea una tabella:
CREATE TABLE data
(
s SERIAL PRIMARY KEY,
t TIMESTAMP,
lat NUMERIC
);
Popolarlo con alcuni dati di esempio:
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);
Nota che i record 3 e 4 e 7 sono NULL
.
E poi ho eseguito la mia prima query:
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;
Risultato (significa spazi NULL
- è molto più chiaro sul violino):
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
Notare l'uso delle funzioni LAG()e LEAD()Window ( documentation). Li ho usati sullo stesso tavolo, ma ordinati in modo diverso.
Questo e l'utilizzo OFFSET
dell'opzione significa che dalla mia singola lat
colonna originale , ora ho 6 colonne extra di dati "generati" che sono molto utili per assegnare valori ai NULL
valori mancanti . L'ultimo (parziale) pezzo del puzzle è mostrato di seguito (la query SQL completa è in fondo a questo post e anche nel violino).
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;
Risultato finale:
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
Quindi, puoi vedere che il valore calcolato per il record 7 è la media dei record 6 e 8 e il record 3 è la media dei record 1 e 2 e il valore assegnato per il record 4 è la media di 5 e 6. Ciò è stato abilitato da l'uso OFFSET
dell'opzione per le funzioni LAG()
e LEAD()
. Se ottieni sequenze di 3 NULL
s, dovrai usare una OFFSET
di 3 e così via.
Non sono molto soddisfatto di questa soluzione: implica l'hard-coding del numero di se NULL
quelle CASE
dichiarazioni diventeranno ancora più complesse e orribili. Idealmente RECURSIVE CTE
è necessaria una sorta di soluzione, ma io HTH!
=============================== Query completa ================= =======
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;