PostgreSQL: интерполировать отсутствующее значение
У меня есть таблица в PostgreSQL с меткой времени и значением.
Я хотел бы интерполировать недостающие значения под "широтой".
Значение "lat" - это высота прилива над точкой отсчета. Для этой цели допустимо линейно интерполировать отсутствующее значение между двумя известными значениями.
Как лучше всего это сделать в PostgreSQL?

Изменить 20200825
Я решил эту проблему другим способом с помощью калькулятора поля QGIS. Проблема с этим методом: он занимает много времени, процесс выполняется на стороне клиента, и я хотел бы запустить его непосредственно на сервере.
Поэтапно мой рабочий процесс был:
- Интервал между записанными значениями «широты» составляет 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" после последнего записанного экземпляра, в котором было записано значение "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 и 7 являются NULL
.
Затем я выполнил свой первый запрос:
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()Window ( 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;