PostgreSQL: nội suy giá trị bị thiếu

Question

Tôi có một bảng trong PostgreSQL với dấu thời gian và giá trị.
Tôi muốn nội suy các giá trị bị thiếu trong "vĩ độ".

Giá trị dưới "vĩ độ" là giá trị thủy triều trên mức dữ liệu. Vì mục đích này, có thể nội suy tuyến tính giá trị bị thiếu giữa hai giá trị đã biết.

Phương pháp tốt nhất để làm như vậy trong PostgreSQL là gì?

Chỉnh sửa 20200825

Tôi đã giải quyết vấn đề này theo một cách khác bằng cách sử dụng máy tính trường QGIS. Vấn đề với phương pháp này: mất nhiều thời gian và quá trình chạy phía máy khách và tôi muốn chạy nó trực tiếp trên máy chủ.

Trong các bước, quy trình làm việc của tôi là:

1. Khoảng thời gian giữa các giá trị "vĩ độ" được ghi là 10 phút. Tôi đã tính toán gia số mỗi phút giữa hai giá trị được ghi lại và lưu trữ giá trị này trong một cột bổ sung có tên "tidal_step" ở giá trị "vĩ độ" đã ghi. (tôi cũng đã lưu trữ dấu thời gian dưới dạng "kỷ nguyên" trong một cột)

Trong 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

cho hai giá trị đầu tiên từ hình ảnh ví dụ cho kết quả là: (4,95 - 5,07) / 10 = -0,012

1. Tôi đã xác định số phút của giá trị "vĩ độ" sẽ được nội suy, trong trường hợp được ghi cuối cùng trong đó giá trị "vĩ độ" được ghi lại và lưu trữ giá trị này trong cột: "min_past_rec"

Trong 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

cho giá trị đầu tiên trong ví dụ: 2019-01-01 00:15:15 trả về: '5' Giá trị này quá 5 phút so với giá trị được ghi cuối cùng.

1. Tôi đã nội suy các giá trị còn thiếu bằng cách thêm ("min_past_rec" * "tidal_step") vào giá trị "vĩ độ" được ghi cuối cùng và lưu trữ giá trị này trong cột có tên "lat_interpolated"

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

Với dữ liệu từ ví dụ:

2019-01-01 00:17:33:

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

1. xóa các cột lỗi thời khỏi cơ sở dữ liệu

Tôi nên sử dụng câu lệnh / tập lệnh nào trong PostgreSQL để thực hiện cùng một tác vụ?

Answer 1

Tôi có một giải pháp (một phần) - những gì tôi đã làm là như sau (xem giải pháp có sẵn tại đây ):

Thuật toán tôi sử dụng để nội suy là

• nếu có một chuỗi là 1 NULL, hãy lấy giá trị trung bình của giá trị bên trên và giá trị bên dưới.

• Một dãy gồm 2 NULLs, giá trị được gán trên cùng là giá trị trung bình của hai bản ghi ở trên nó và giá trị được gán dưới cùng là giá trị trung bình của hai bản ghi dưới đây.

Để làm điều này, tôi đã làm như sau:

Tạo bảng:

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

Điền vào nó với một số dữ liệu mẫu:

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

Lưu ý rằng các bản ghi 3 & 4 và 7 là NULL.

Và sau đó tôi chạy truy vấn đầu tiên của mình:

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;

Kết quả (dấu cách có nghĩa là NULL- nó rõ ràng hơn nhiều trên fiddle):

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 

Lưu ý việc sử dụng các hàm LAG()và LEAD()Window ( documentation). Tôi đã sử dụng chúng trên cùng một bảng, nhưng được sắp xếp khác nhau.

Điều này và sử dụng OFFSETtùy chọn có nghĩa là từ một latcột ban đầu của tôi , giờ đây tôi có thêm 6 cột dữ liệu "được tạo", rất hữu ích để gán giá trị cho các NULLgiá trị bị thiếu . Phần cuối cùng (một phần) của câu đố được hiển thị bên dưới (truy vấn SQL đầy đủ nằm ở cuối bài đăng này và cả trong câu đố).

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;

Kết quả cuối cùng:

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

Vì vậy, bạn có thể thấy rằng giá trị được tính toán cho bản ghi 7 là giá trị trung bình của bản ghi 6 & 8 và bản ghi 3 là giá trị trung bình của bản ghi 1 & 2 và giá trị được gán cho bản ghi 4 là giá trị trung bình của 5 & 6. Điều này được kích hoạt bởi việc sử dụng OFFSETtùy chọn cho LAG()và các LEAD()chức năng. Nếu bạn nhận được chuỗi 3 NULLs, thì bạn sẽ phải sử dụng OFFSET3, v.v.

Tôi không thực sự hài lòng với giải pháp này - nó liên quan đến việc mã hóa cứng cho số lượng NULLs và những CASEcâu lệnh đó sẽ trở nên phức tạp và khủng khiếp hơn. Tốt nhất RECURSIVE CTElà cần phải có một số loại giải pháp, nhưng tôi HTH!

=============================== Truy vấn đầy đủ ================= =======

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;