PostgreSQL: interpolar valor perdido

Aug 19 2020

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:

  1. 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

  1. 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.

  1. 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
  1. eliminar columnas obsoletas de la base de datos

¿Qué declaraciones / script debo usar en PostgreSQL para realizar la misma tarea?

Respuestas

1 Vérace Aug 19 2020 at 09:58

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 NULLs, 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 OFFSETopción significa que de mi única latcolumna original , ahora tengo 6 columnas adicionales de datos "generados" que son muy útiles para asignar valores a los NULLvalores 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 OFFSETopción para las funciones LAG()y LEAD(). Si obtienes secuencias de 3 NULLs, tendrás que usar una OFFSETde 3 y así sucesivamente.

No estoy muy contento con esta solución: implica codificar la cantidad de NULLs y esas CASEdeclaraciones 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;