PostgreSQL: แก้ไขค่าที่หายไป
ฉันมีตารางใน PostgreSQL พร้อมเวลาประทับและค่า
ฉันต้องการแก้ไขค่าที่หายไปใน "lat"
ค่าใต้ "lat" คือค่า tidalheights ที่อยู่เหนือข้อมูล สำหรับวัตถุประสงค์คุณสามารถแก้ไขค่าที่ขาดหายไปเป็นเส้นตรงระหว่างค่าที่ทราบสองค่า
วิธีที่ดีที่สุดในการทำ PostgreSQL คืออะไร?

แก้ไข 20200825
ฉันได้แก้ไขปัญหานี้ด้วยวิธีอื่นโดยใช้เครื่องคำนวณสนาม QGIS ปัญหาเกี่ยวกับวิธีนี้: ใช้เวลานานและกระบวนการนี้จะรันฝั่งไคลเอ็นต์และฉันต้องการเรียกใช้โดยตรงบนเซิร์ฟเวอร์
ในขั้นตอนขั้นตอนการทำงานของฉันคือ:
- ช่วงเวลาระหว่างค่า "lat" ที่บันทึกไว้คือ 10 นาที ฉันคำนวณการเพิ่มขึ้นต่อนาทีระหว่างค่าที่บันทึกไว้สองค่าและเก็บไว้ในคอลัมน์พิเศษที่เรียกว่า "tidal_step" ที่ค่า "lat" ที่บันทึกไว้ (ฉันเก็บการประทับเวลาไว้เป็น "ยุค" ในคอลัมน์ด้วย)
ใน 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
สำหรับสองค่าแรกจากรูปภาพตัวอย่างที่ให้ผลลัพธ์: (4.95 - 5.07) / 10 = -0.012
- ฉันกำหนดจำนวนนาทีของ "lat" -value ที่จะแก้ไขผ่านอินสแตนซ์ที่บันทึกล่าสุดซึ่งมีการบันทึกค่า "lat" และจัดเก็บไว้ในคอลัมน์: "min_past_rec"
ใน 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
สำหรับค่าแรกในตัวอย่าง: 2019-01-01 00:15:15 ส่งกลับ: '5' ซึ่งเป็นเวลา 5 นาทีที่ผ่านมาจากค่าที่บันทึกล่าสุด
- ฉันแก้ไขค่าที่หายไปโดยเพิ่ม ("min_past_rec" * "tidal_step") ไปยังค่า "lat" ที่บันทึกล่าสุดและเก็บไว้ในคอลัมน์ที่เรียกว่า "lat_interpolated"
ใน 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'))
ด้วยข้อมูลจากตัวอย่าง:
2019-01-01 00:17:33:
"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" =
7*-0.012 + 4.95 = 4.866
- ลบคอลัมน์ที่ล้าสมัยออกจากฐานข้อมูล
ฉันควรใช้คำสั่ง / สคริปต์ใดใน PostgreSQL เพื่อทำงานเดียวกัน
คำตอบ
ฉันมีวิธีแก้ปัญหา (บางส่วน) - สิ่งที่ฉันทำมีดังต่อไปนี้ (ดูซอที่มีอยู่ที่นี่ ):
อัลกอริทึมที่ฉันใช้ในการแก้ไขคือ
หากมีลำดับ 1
NULL
ให้หาค่าเฉลี่ยของค่าด้านบนและค่าด้านล่างลำดับของ 2
NULL
วินาทีค่าที่กำหนดสูงสุดคือค่าเฉลี่ยของสองระเบียนที่อยู่ด้านบนและค่าที่กำหนดด้านล่างคือค่าเฉลี่ยของสองระเบียนด้านล่าง
ในการดำเนินการนี้ฉันได้ทำสิ่งต่อไปนี้:
สร้างตาราง:
CREATE TABLE data
(
s SERIAL PRIMARY KEY,
t TIMESTAMP,
lat NUMERIC
);
เติมข้อมูลด้วยข้อมูลตัวอย่าง:
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 และ NULL
7
จากนั้นฉันก็เรียกใช้แบบสอบถามแรกของฉัน:
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;
ผลลัพธ์ (ช่องว่างหมายถึงNULL
- ชัดเจนกว่ามากสำหรับซอ):
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()และLEAD()หน้าต่าง ( documentation) ฉันเคยใช้บนโต๊ะเดียวกัน แต่เรียงลำดับต่างกัน
สิ่งนี้และการใช้OFFSET
ตัวเลือกหมายความว่าจากlat
คอลัมน์เดียวเดิมของฉันตอนนี้ฉันมีข้อมูล "สร้าง" เพิ่มเติม 6 คอลัมน์ซึ่งมีประโยชน์มากสำหรับการกำหนดค่าให้กับNULL
ค่าที่ขาดหายไป ชิ้นส่วนสุดท้าย (บางส่วน) ของปริศนาแสดงอยู่ด้านล่าง (แบบสอบถาม SQL แบบเต็มอยู่ที่ด้านล่างของโพสต์นี้และในซอ)
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;
ผลลัพธ์สุดท้าย:
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
ดังนั้นคุณจะเห็นว่าค่าที่คำนวณได้สำหรับระเบียน 7 คือค่าเฉลี่ยของระเบียน 6 & 8 และระเบียน 3 คือค่าเฉลี่ยของระเบียน 1 & 2 และค่าที่กำหนดสำหรับระเบียน 4 คือค่าเฉลี่ย 5 & 6 ซึ่งเปิดใช้งานโดย การใช้OFFSET
ตัวเลือกสำหรับฟังก์ชันLAG()
และ LEAD()
ถ้าคุณได้ลำดับ 3 NULL
วินาทีคุณจะต้องใช้OFFSET
3 ไปเรื่อย ๆ
ฉันไม่ค่อยพอใจกับโซลูชันนี้ - มันเกี่ยวข้องกับการเข้ารหัสจำนวนNULL
s อย่างหนักและCASE
ข้อความเหล่านั้นจะซับซ้อนและน่ากลัวยิ่งขึ้น ตามหลักการแล้วRECURSIVE CTE
จำเป็นต้องมีวิธีแก้ปัญหาบางอย่างแต่ฉัน HTH!
=============================== แบบสอบถามแบบเต็ม ================= =======
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;