PostgreSQL: eksik değerin enterpolasyonu
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:
- 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
- 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.
- 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
- 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
(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
NULL
saniyelik 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 OFFSET
seçeneğin kullanılması, orijinal tek lat
sütunumdan, eksik NULL
değ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 OFFSET
seçeneğin kullanımı . Eğer 3 saniyelik diziler elde ederseniz , o zaman 3 ve benzeri kullanmanız gerekecek .LAG()
LEAD()
NULL
OFFSET
Bu çözümden gerçekten memnun değilim - NULL
s sayısı için kodlama gerektiriyor ve bu CASE
ifadeler 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;