PostgreSQL: Interpolieren Sie den fehlenden Wert

Question

Ich habe eine Tabelle in PostgreSQL mit einem Zeitstempel und einem Wert.
Ich möchte die fehlenden Werte unter "lat" interpolieren.

Der Wert unter "lat" sind Gezeitenhöhen über einem Bezugspunkt. Zu diesem Zweck ist es in Ordnung, den fehlenden Wert linear zwischen den beiden bekannten Werten zu interpolieren.

Was ist die beste Methode, um dies in PostgreSQL zu tun?

Bearbeiten Sie 20200825

Ich habe dieses Problem auf andere Weise mit dem QGIS-Feldrechner gelöst. Problem mit dieser Methode: Es dauert lange und der Prozess wird clientseitig ausgeführt und ich möchte ihn direkt auf dem Server ausführen.

In Schritten war mein Workflow:

1. Das Intervall zwischen den aufgezeichneten "lat" -Werten beträgt 10 Minuten. Ich habe das Inkrement pro Minute zwischen zwei aufgezeichneten Werten berechnet und dieses in einer zusätzlichen Spalte namens "tidal_step" mit dem aufgezeichneten "lat" -Wert gespeichert. (Ich habe den Zeitstempel auch als "Epoche" in einer Spalte gespeichert)

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

für die ersten beiden Werte aus dem Beispielbild, die ergeben: (4,95 - 5,07) / 10 = -0,012

1. Ich habe die Anzahl der Minuten des zu interpolierenden "lat" -Werts nach der letzten aufgezeichneten Instanz, in der ein "lat" -Wert aufgezeichnet wurde, bestimmt und in einer Spalte gespeichert: "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

Für den ersten Wert im Beispiel: 2019-01-01 00:15:15 gibt Folgendes zurück: '5' Dies ist 5 Minuten nach dem zuletzt aufgezeichneten Wert.

1. Ich habe die fehlenden Werte interpoliert, indem ich ("min_past_rec" * "tidal_step") zum zuletzt aufgezeichneten "lat" -Wert hinzugefügt und in der Spalte "lat_interpolated" gespeichert habe.

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

Mit Daten aus dem Beispiel:

2019-01-01 00:17:33:

"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" = 
7*-0.012 + 4.95 = 4.866

1. veraltete Spalten aus der Datenbank löschen

Welche Anweisungen / Skripte sollte ich in PostgreSQL verwenden, um dieselbe Aufgabe auszuführen?

Answer 1

Ich habe eine (teilweise) Lösung - ich habe Folgendes getan (siehe die hier verfügbare Geige ):

Der Algorithmus, den ich zum Interpolieren verwendet habe, war

• Wenn es eine Folge von 1 gibt NULL, nehmen Sie den Durchschnitt aus dem Wert oben und dem Wert unten.

• Bei einer Folge von 2 NULLs ist der oben zugewiesene Wert der Durchschnitt der beiden darüber liegenden Datensätze und der unterste zugewiesene Wert der Durchschnitt der beiden darunter liegenden Datensätze.

Um dies zu tun, habe ich Folgendes getan:

Erstellen Sie eine Tabelle:

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

Füllen Sie es mit einigen Beispieldaten:

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

Beachten Sie, dass die Datensätze 3, 4 und 7 sind NULL.

Und dann habe ich meine erste Abfrage ausgeführt:

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;

Ergebnis (Leerzeichen bedeuten NULL- es ist viel klarer auf der Geige):

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 

Beachten Sie die Verwendung der Funktionen LAG()und LEAD()Fenster ( documentation). Ich habe sie am selben Tisch verwendet, aber anders sortiert.

Dies und die Verwendung der OFFSETOption bedeutet, dass ich aus meiner ursprünglichen einzelnen latSpalte jetzt 6 zusätzliche Spalten mit "generierten" Daten habe, die sehr nützlich sind, um den fehlenden NULLWerten Werte zuzuweisen. Das letzte (teilweise) Teil des Puzzles wird unten gezeigt (die vollständige SQL-Abfrage befindet sich am Ende dieses Beitrags und auch in der Geige).

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;

Endergebnis:

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

Sie können also sehen, dass der berechnete Wert für Datensatz 7 der Durchschnitt der Datensätze 6 und 8 ist und Datensatz 3 der Durchschnitt der Datensätze 1 und 2 ist und der zugewiesene Wert für Datensatz 4 der Durchschnitt von 5 und 6 ist. Dies wurde aktiviert durch die Verwendung der OFFSETOption für die LAG()und LEAD()Funktionen. Wenn Sie Sequenzen von 3 NULLs erhalten, müssen Sie eine OFFSETvon 3 verwenden und so weiter.

Ich bin mit dieser Lösung nicht wirklich zufrieden - sie beinhaltet eine harte Codierung für die Anzahl der NULLs und diese CASEAussagen werden noch komplexer und schrecklicher. Idealerweise RECURSIVE CTEist eine Lösung erforderlich, aber ich HTH!

============================== Vollständige Abfrage ================= =======

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;