PostgreSQL: interpoluj brakującą wartość

Question

Mam tabelę w PostgreSQL ze znacznikiem czasu i wartością.
Chciałbym interpolować brakujące wartości pod „lat”.

Wartości pod „lat” to wysokości pływów powyżej punktu odniesienia. W tym celu dobrze jest interpolować brakującą wartość liniową między dwiema znanymi wartościami.

Jaka jest najlepsza metoda, aby to zrobić w PostgreSQL?

Edytuj 20200825

Rozwiązałem ten problem w inny sposób za pomocą kalkulatora pola QGIS. Problem z tą metodą: zajmuje dużo czasu, a proces działa po stronie klienta i chciałbym uruchomić go bezpośrednio na serwerze.

W krokach mój przepływ pracy wyglądał następująco:

1. Odstęp między zarejestrowanymi wartościami „lat” wynosi 10 minut. Obliczyłem przyrost na minutę między dwiema zarejestrowanymi wartościami i zapisałem go w dodatkowej kolumnie o nazwie „tidal_step” przy zarejestrowanej wartości „lat”. (Znacznik czasu zapisałem również jako „epokę” w kolumnie)

W 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

dla pierwszych dwóch wartości z przykładowego obrazu, co daje: (4,95 - 5,07) / 10 = -0,012

1. Określiłem liczbę minut wartości „lat” do interpolacji, poza ostatnią zarejestrowaną instancją, w której zarejestrowano wartość „lat” i zapisałem ją w kolumnie: „min_past_rec”

W 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

dla pierwszej wartości w przykładzie: 2019-01-01 00:15:15 zwraca: „5” Jest to 5 minut po ostatniej zapisanej wartości.

1. Interpolowałem brakujące wartości, dodając („min_past_rec” * „tidal_step”) do ostatnio zapisanej wartości „lat” i zapisałem w kolumnie o nazwie „lat_interpolated”

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

Dane z przykładu:

2019-01-01 00:17:33:

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

1. usuń przestarzałe kolumny z bazy danych

Których instrukcji / skryptu powinienem użyć w PostgreSQL, aby wykonać to samo zadanie?

Answer 1

Mam (częściowe) rozwiązanie - oto co zrobiłem (zobacz skrzypce dostępne tutaj ):

Algorytm, którego użyłem do interpolacji, to

• jeśli występuje sekwencja 1 NULL, weź średnią z wartości powyżej i wartości poniżej.

• Sekwencja 2 NULLs, najwyższa przypisana wartość to średnia z dwóch rekordów powyżej, a dolna to średnia z dwóch poniższych rekordów.

Aby to zrobić, wykonałem następujące czynności:

Utwórz tabelę:

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

Wypełnij go przykładowymi danymi:

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

Zauważ, że rekordy 3, 4 i 7 to NULL.

Następnie wykonałem pierwsze zapytanie:

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;

Wynik (spacje oznaczają NULL- jest znacznie wyraźniejszy na skrzypcach):

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 

Zwróć uwagę na użycie funkcji LAG()i LEAD()Window ( documentation). Użyłem ich na tym samym stole, ale posortowałem inaczej.

To i użycie tej OFFSETopcji oznacza, że ​​z mojej oryginalnej pojedynczej latkolumny mam teraz 6 dodatkowych kolumn "wygenerowanych" danych, które są bardzo przydatne do przypisywania wartości brakującym NULLwartościom. Ostatni (częściowy) element układanki pokazano poniżej (pełne zapytanie SQL znajduje się na dole tego wpisu, a także na skrzypcach).

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;

Ostateczny wynik:

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

Więc możesz zobaczyć, że obliczona wartość dla rekordu 7 to średnia z rekordów 6 i 8, a rekord 3 to średnia z rekordów 1 i 2, a wartość przypisana dla rekordu 4 to średnia z 5 i 6. Zostało to włączone przez użycie OFFSETopcji dla funkcji LAG()i LEAD(). Jeśli otrzymasz sekwencje 3- NULLsekundowe, będziesz musiał użyć OFFSET3 i tak dalej.

Nie jestem zadowolony z tego rozwiązania - wymaga zakodowania liczby NULLs, a te CASEstwierdzenia staną się jeszcze bardziej złożone i okropne. W idealnym przypadku RECURSIVE CTEpotrzebne jest jakieś rozwiązanie, ale HTH!

=============================== Pełne zapytanie ================= =======

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;