PostgreSQL: interpoler la valeur manquante

Aug 19 2020

J'ai une table dans PostgreSQL avec un horodatage et une valeur.
Je voudrais interpoler les valeurs manquantes sous "lat".

La valeur sous «lat» correspond aux hauteurs de marée au-dessus d'une donnée. Dans ce but, il est possible d'interpoler la valeur manquante linéaire entre les deux valeurs connues.

Quelle est la meilleure méthode pour le faire dans PostgreSQL?

Modifier 20200825

J'ai résolu ce problème d'une manière différente en utilisant le calculateur de champ QGIS. Problème avec cette méthode: cela prend beaucoup de temps et le processus s'exécute côté client et je voudrais l'exécuter directement sur le serveur.

Par étapes, mon workflow était:

  1. L'intervalle entre les valeurs "lat" enregistrées est de 10 minutes. J'ai calculé l'incrément par minute entre deux valeurs enregistrées et je l'ai stocké dans une colonne supplémentaire appelée "tidal_step" à la valeur "lat" enregistrée. (j'ai stocké l'horodatage également comme une "époque" dans une colonne)

Dans 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

pour les deux premières valeurs de l'image d'exemple qui donne: (4,95 - 5,07) / 10 = -0,012

  1. J'ai déterminé le nombre de minutes de la valeur "lat" à interpoler, après la dernière instance enregistrée où une valeur "lat" a été enregistrée et je l'ai stockée dans une colonne: "min_past_rec"

Dans 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

pour la première valeur de l'exemple: 2019-01-01 00:15:15 renvoie: '5' C'est 5 minutes après la dernière valeur enregistrée.

  1. J'ai interpolé les valeurs manquantes en ajoutant le ("min_past_rec" * "tidal_step") à la dernière valeur "lat" enregistrée et je l'ai stocké dans la colonne appelée "lat_interpolated"

Dans 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'))

Avec les données de l'exemple:

01/01/2019 00:17:33:

"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" = 
7*-0.012 + 4.95 = 4.866
  1. supprimer les colonnes obsolètes de la base de données

Quelles instructions / script dois-je utiliser dans PostgreSQL pour effectuer la même tâche?

Réponses

1 Vérace Aug 19 2020 at 09:58

J'ai une solution (partielle) - ce que j'ai fait était la suivante (voir le violon disponible ici ):

L'algorithme que j'ai utilisé pour l'interpolation était

  • s'il y a une séquence de 1 NULL, prenez la moyenne de la valeur ci-dessus et de la valeur ci-dessous.

  • Une séquence de 2 NULLs, la valeur assignée supérieure est la moyenne des deux enregistrements au-dessus et celle assignée inférieure est la moyenne des deux enregistrements ci-dessous.

Pour ce faire, j'ai fait ce qui suit:

Créez un tableau:

CREATE TABLE data 
(
  s SERIAL PRIMARY KEY, 
  t TIMESTAMP, 
  lat NUMERIC
);

Remplissez-le avec quelques exemples de données:

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);

Notez que les enregistrements 3, 4 et 7 le sont NULL.

Et puis j'ai lancé ma première requête:

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;

Résultat (les espaces signifient NULL- c'est beaucoup plus clair sur le violon):

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 

Notez l'utilisation des fonctions LAG()et LEAD()Window ( documentation). Je les ai utilisés sur la même table, mais triés différemment.

Ceci et l'utilisation de l' OFFSEToption signifie qu'à partir de ma latcolonne unique d'origine , j'ai maintenant 6 colonnes supplémentaires de données "générées" qui sont très utiles pour attribuer des valeurs aux NULLvaleurs manquantes . La dernière pièce (partielle) du puzzle est montrée ci-dessous (la requête SQL complète est au bas de cet article et également dans le violon).

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;

Résultat 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

Ainsi, vous pouvez voir que la valeur calculée pour l'enregistrement 7 est la moyenne des enregistrements 6 et 8 et que l'enregistrement 3 est la moyenne des enregistrements 1 et 2 et que la valeur attribuée pour l'enregistrement 4 est la moyenne de 5 et 6. Cela a été activé par l'utilisation de l' OFFSEToption pour les fonctions LAG()et LEAD(). Si vous obtenez des séquences de 3 NULLs, vous devrez alors utiliser un OFFSETde 3 et ainsi de suite.

Je ne suis pas vraiment satisfait de cette solution - elle implique un codage en dur pour le nombre de NULLs et ces CASEdéclarations deviendront encore plus complexes et horribles. Idéalement, une sorte de RECURSIVE CTEsolution est requise, mais je HTH!

=============================== Requête complète ================= =======

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;