PostgreSQL: eksik değerin enterpolasyonu

Aug 19 2020

PostgreSQL'de zaman damgası ve değer içeren bir tablom var.
Eksik değerleri "lat" altında enterpolasyon yapmak istiyorum.

"Enlem" altındaki değer, bir mevki üzerindeki gelgit yükseklikleridir. Bu amaçla, iki bilinen değer arasında doğrusal olarak eksik değerin enterpolasyonunda sorun yoktur.

PostgreSQL'de bunu yapmanın en iyi yöntemi nedir?

Düzenle 20200825

QGIS saha hesaplayıcısını kullanarak bu sorunu farklı bir şekilde çözdüm. Bu yöntemle ilgili sorun: Uzun zaman alıyor ve işlem istemci tarafında çalışıyor ve bunu doğrudan sunucuda çalıştırmak istiyorum.

Adım adım, iş akışım şöyleydi:

  1. Kaydedilen "enlem" değerleri arasındaki aralık 10 dakikadır. Kaydedilen iki değer arasındaki dakika başına artışı hesapladım ve bunu kaydedilen "lat" değerinde "tidal_step" adlı ekstra bir sütunda sakladım. (zaman damgasını da bir sütunda "dönem" olarak sakladım)

QGIS'de:

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

Örnek görüntüden sonuçlanan ilk iki değer için: (4,95 - 5,07) / 10 = -0,012

  1. Bir "lat" değerinin kaydedildiği ve bunu bir sütunda sakladığı son kaydedilen örneği geçerek "lat" değerinin ara değerinin dakika miktarını belirledim: "min_past_rec"

QGIS'de:

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

örnekteki ilk değer için: 2019-01-01 00:15:15 şunu döndürür: '5' Bu, son kaydedilen değerden 5 dakika sonrasıdır.

  1. Eksik değerleri ("min_past_rec" * "tidal_step") son kaydedilen "lat" değerine ekleyerek hesapladım ve bunu "lat_interpolated" adlı sütunda sakladım

QGIS'te

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

Örnekteki verilerle:

2019-01-01 00:17:33:

"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" = 
7*-0.012 + 4.95 = 4.866
  1. eski sütunları veritabanından sil

PostgreSQL'de aynı görevi gerçekleştirmek için hangi ifadeleri / komut dosyasını kullanmalıyım?

Yanıtlar

1 Vérace Aug 19 2020 at 09:58

(Kısmi) bir çözümüm var - yaptığım şey şuydu ( burada bulunan keman bölümüne bakın ):

Enterpolasyon için kullandığım algoritma

  • 1 dizisi varsa NULL, yukarıdaki değerin ve altındaki değerin ortalamasını alın.

  • 2 NULLsaniyelik bir sıra , en üstteki iki kaydın ortalaması ve en altta atanan değer, aşağıdaki iki kaydın ortalamasıdır.

Bunu yapmak için şunları yaptım:

Bir tablo oluşturun:

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

Bazı örnek verilerle doldurun:

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

3, 4 ve 7 numaralı kayıtların olduğunu unutmayın NULL.

Ve sonra ilk sorgumu çalıştırdım:

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;

Sonuç (boşluklar NULL- keman üzerinde çok daha nettir):

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 

LAG()Ve LEAD()Pencere işlevlerinin ( documentation) kullanımına dikkat edin . Onları aynı masada kullandım ama farklı sıraladım.

Bu ve OFFSETseçeneğin kullanılması, orijinal tek latsütunumdan, eksik NULLdeğerlere değer atamak için çok yararlı olan 6 ekstra "oluşturulmuş" veri sütununa sahip olduğum anlamına gelir . Bulmacanın son (kısmi) parçası aşağıda gösterilmiştir (tam SQL sorgusu bu yazının altında ve ayrıca keman içinde).

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;

Son sonuç:

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

Böylece, 7. kayıt için hesaplanan değerin 6. ve 8. kayıtların ortalaması olduğunu ve 3. kaydın 1 ve 2. kayıtların ortalaması olduğunu ve 4. kayıt için atanan değerin 5 & 6 nın ortalaması olduğunu görebilirsiniz. Bu, ve işlevleri için OFFSETseçeneğin kullanımı . Eğer 3 saniyelik diziler elde ederseniz , o zaman 3 ve benzeri kullanmanız gerekecek .LAG()LEAD()NULLOFFSET

Bu çözümden gerçekten memnun değilim - NULLs sayısı için kodlama gerektiriyor ve bu CASEifadeler daha da karmaşık ve korkunç hale gelecek. İdeal olarak bir tür RECURSIVE CTEçözüm gereklidir, ancak ben HTH!

============================= Tam Sorgu =================== =======

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;