PostgreSQL: interpolasi nilai yang hilang

Question

Saya memiliki tabel di PostgreSQL dengan stempel waktu dan nilai.
Saya ingin menginterpolasi nilai yang hilang di bawah "lat".

Nilai di bawah "lat" adalah tinggi pasang surut di atas datum. Untuk tujuan ini, boleh saja untuk menginterpolasi linier nilai yang hilang antara dua nilai yang diketahui.

Apa metode terbaik untuk melakukannya di PostgreSQL?

Edit 20200825

Saya telah memecahkan masalah ini dengan cara yang berbeda menggunakan kalkulator lapangan QGIS. Masalah dengan metode ini: ini membutuhkan waktu lama dan prosesnya berjalan di sisi klien dan saya ingin menjalankannya langsung di server.

Dalam langkah-langkahnya, alur kerja saya adalah:

1. Interval antara nilai "lat" yang tercatat adalah 10 menit. Saya menghitung kenaikan per menit antara dua nilai yang tercatat dan menyimpannya di kolom tambahan yang disebut "tidal_step" pada nilai "lat" yang tercatat. (Saya menyimpan stempel waktu juga sebagai "masa" di kolom)

Di 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

untuk dua nilai pertama dari gambar contoh yang menghasilkan: (4.95 - 5.07) / 10 = -0.012

1. Saya menentukan jumlah menit nilai "lat" yang akan diinterpolasi, setelah kejadian terakhir yang tercatat di mana nilai "lat" dicatat dan disimpan di kolom: "min_past_rec"

Di 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

untuk nilai pertama dalam contoh: 2019-01-01 00:15:15 mengembalikan: '5' Ini adalah 5 menit setelah nilai terakhir yang direkam.

1. Saya menginterpolasi nilai yang hilang dengan menambahkan ("min_past_rec" * "tidal_step") ke nilai "lat" yang terakhir direkam dan menyimpannya di kolom yang disebut "lat_interpolated"

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

Dengan data dari contoh:

2019-01-01 00:17:33:

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

1. hapus kolom usang dari database

Pernyataan / skrip mana yang harus saya gunakan di PostgreSQL untuk melakukan tugas yang sama?

Answer 1

Saya memiliki solusi (sebagian) - yang saya lakukan adalah sebagai berikut (lihat biola yang tersedia di sini ):

Algoritme yang saya gunakan untuk interpolasi adalah

• jika ada urutan 1 NULL, ambil rata-rata dari nilai di atas dan nilai di bawah.

• Urutan 2 NULLdetik, nilai yang ditetapkan teratas adalah rata-rata dari dua catatan di atasnya dan yang paling bawah adalah rata-rata dari dua catatan di bawahnya.

Untuk melakukan ini, saya melakukan hal berikut:

Buat tabel:

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

Isi dengan beberapa data sampel:

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

Perhatikan bahwa record 3 & 4 dan 7 adalah NULL.

Dan kemudian saya menjalankan kueri pertama saya:

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;

Hasil (berarti spasi NULL- biola jauh lebih jelas):

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 

Perhatikan penggunaan fungsi LAG()dan LEAD()Window ( documentation). Saya telah menggunakannya di tabel yang sama, tetapi diurutkan secara berbeda.

Ini dan menggunakan OFFSETopsi berarti bahwa dari latkolom tunggal asli saya , saya sekarang memiliki 6 kolom tambahan dari data "yang dihasilkan" yang sangat berguna untuk menetapkan nilai ke nilai yang hilang NULL. Bagian terakhir (sebagian) dari teka-teki ditampilkan di bawah ini (kueri SQL lengkap ada di bagian bawah posting ini dan juga di biola).

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;

Hasil akhir:

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

Jadi, Anda dapat melihat bahwa nilai yang dihitung untuk catatan 7 adalah rata-rata dari catatan 6 & 8 dan catatan 3 adalah rata-rata catatan 1 & 2 dan nilai yang ditetapkan untuk catatan 4 adalah rata-rata dari 5 & 6. Hal ini dimungkinkan oleh penggunaan OFFSETopsi LAG()dan LEAD()fungsi. Jika Anda mendapatkan urutan 3 NULLs, maka Anda harus menggunakan OFFSETdari 3 dan seterusnya.

Saya tidak terlalu senang dengan solusi ini - ini melibatkan pengkodean keras untuk jumlah NULLs dan CASEpernyataan itu akan menjadi lebih kompleks dan mengerikan. Idealnya beberapa jenis RECURSIVE CTEsolusi diperlukan, tetapi saya HTH!

=============================== Kueri Lengkap ================= =======

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;