PostgreSQL: interpolar valor perdido
Tengo una tabla en PostgreSQL con una marca de tiempo y un valor.
Me gustaría interpolar los valores faltantes en "lat".
El valor bajo "lat" son alturas de marea por encima de un datum. Para este propósito, está bien interpolar el valor faltante lineal entre los dos valores conocidos.
¿Cuál es el mejor método para hacerlo en PostgreSQL?

Editar 20200825
He resuelto este problema de una manera diferente usando la calculadora de campo QGIS. Problema con este método: lleva mucho tiempo y el proceso se ejecuta en el lado del cliente y me gustaría ejecutarlo directamente en el servidor.
En pasos, mi flujo de trabajo fue:
- El intervalo entre los valores "lat" registrados es de 10 minutos. Calculé el incremento por minuto entre dos valores registrados y lo guardé en una columna adicional llamada "tidal_step" en el valor "lat" registrado. (Guardé la marca de tiempo también como una "época" en una columna)
En 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
para los dos primeros valores de la imagen de ejemplo que dan como resultado: (4,95 - 5,07) / 10 = -0,012
- Determiné la cantidad de minutos del valor "lat" a interpolar, después de la última instancia registrada donde se registró un valor "lat" y lo almacené en una columna: "min_past_rec"
En 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
para el primer valor en el ejemplo: 2019-01-01 00:15:15 devuelve: '5' Esto es 5 minutos después del último valor registrado.
- Interpolé los valores faltantes agregando el ("min_past_rec" * "tidal_step") al último valor "lat" registrado y lo guardé en la columna llamada "lat_interpolated"
En 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 datos del ejemplo:
2019-01-01 00:17:33:
"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" =
7*-0.012 + 4.95 = 4.866
- eliminar columnas obsoletas de la base de datos
¿Qué declaraciones / script debo usar en PostgreSQL para realizar la misma tarea?
Respuestas
Tengo una solución (parcial): lo que hice fue lo siguiente (vea el violín disponible aquí ):
El algoritmo que usé para interpolar fue
si hay una secuencia de 1
NULL
, tome el promedio del valor anterior y el valor inferior.En una secuencia de 2
NULL
s, el valor superior asignado es el promedio de los dos registros superiores y el inferior asignado es el promedio de los dos registros siguientes.
Para hacer esto, hice lo siguiente:
Crea una tabla:
CREATE TABLE data
(
s SERIAL PRIMARY KEY,
t TIMESTAMP,
lat NUMERIC
);
Rellénelo con algunos datos de muestra:
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);
Tenga en cuenta que los registros 3, 4 y 7 son NULL
.
Y luego ejecuté mi primera consulta:
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;
Resultado (los espacios significan NULL
, es mucho más claro en el violín):
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
Tenga en cuenta el uso de las funciones de ventana LAG()y LEAD()( documentation). Los he usado en la misma mesa, pero ordenados de manera diferente.
Esto y usar la OFFSET
opción significa que de mi única lat
columna original , ahora tengo 6 columnas adicionales de datos "generados" que son muy útiles para asignar valores a los NULL
valores faltantes . La última pieza (parcial) del rompecabezas se muestra a continuación (la consulta SQL completa se encuentra al final de esta publicación y también en el violín).
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;
Resultado final:
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
Entonces, puede ver que el valor calculado para el registro 7 es el promedio de los registros 6 y 8 y el registro 3 es el promedio de los registros 1 y 2 y el valor asignado para el registro 4 es el promedio de 5 y 6. Esto fue habilitado por el uso de la OFFSET
opción para las funciones LAG()
y LEAD()
. Si obtienes secuencias de 3 NULL
s, tendrás que usar una OFFSET
de 3 y así sucesivamente.
No estoy muy contento con esta solución: implica codificar la cantidad de NULL
s y esas CASE
declaraciones se volverán aún más complejas y horribles. Idealmente RECURSIVE CTE
, se requiere algún tipo de solución, pero ¡HTH!
=============================== Consulta 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;