By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE elbat
(id integer,
time integer,
value integer);
INSERT INTO elbat
VALUES (11111, 1, 5.0),
(11111, 10, NULL),
(22222, 7, 32.6),
(33333, 11, 15.88),
(11111, 15, 20.0);
SELECT *
FROM elbat;
| id | time | value |
|---|---|---|
| 11111 | 1 | 5 |
| 11111 | 10 | null |
| 22222 | 7 | 33 |
| 33333 | 11 | 16 |
| 11111 | 15 | 20 |
SELECT t1.id,
t1.time,
coalesce(t1.value,
((SELECT t2.value
FROM elbat t2
WHERE t2.id = t1.id
AND t2.time < t1.time
ORDER BY t2.time DESC
LIMIT 1)
+
(SELECT t2.value
FROM elbat t2
WHERE t2.id = t1.id
AND t2.time > t1.time
ORDER BY t2.time ASC
LIMIT 1)
)
/
2) value
FROM elbat t1;
| id | time | value |
|---|---|---|
| 11111 | 1 | 5.0000 |
| 11111 | 10 | 12.5000 |
| 22222 | 7 | 33.0000 |
| 33333 | 11 | 16.0000 |
| 11111 | 15 | 20.0000 |