Window function for get the last value to extend missing rows

[Durumdara <durumdara@xxxxxxxxx>]

Dear Members!

I have a table with temperature measures.

The data is coming from the PLC, but sometimes the period is "slipping", so the values are not correctly minute based.

03:00     10
03:02     12
03:03     11

03:05     13

I have to make a virtual table which is minute based.

I thought I would make a generated temp table (generate_series) and then join these values based on minue.

03:00     10

03:01     NULL
03:02     12
03:03     11

03:04     NULL

03:05     13

I need a code to replace the value to the last value on NULL.

03:00     10

03:01     10 <
03:02     12
03:03     11

03:04     11 <

03:05     13

The LAG function seems to be ok, but how to handle if more than two periods are missing?

03:00     10

03:01     NULL
03:02     NULL
03:03     NULL

03:04     11

03:05     13

select *, coalesce(value, prev_value) as value from (
select mmin, value,
lag(value, 1) over (order by mmin) as prev_value
from test_table) t

or

select *, coalesce(value, prev_value) as value from (

select mmin, value,
coalesce(lag(value, 1) over (order by mmin),
lag(value, 2) over (order by mmin)) as prev_value
from tmp_test_table) t

The test data:

create table tmp_test_table (mmin int, value int);
insert into tmp_test_table values
(1, 1),
(2, 1),
(3, NULL),
(4, NULL),
(5, 2),
(6, NULL),
(7, NULL),
(10, 10),
(11, NULL),
(12, NULL),
(13, NULL),
(14, NULL);

The result is:

1	1	NULL	1
2	1	1	1
3	NULL	1	1
4	NULL	1	1
5	2	NULL	2
6	NULL	2	2
7	NULL	2	2
10	10	NULL	10
11	NULL	10	10
12	NULL	10	10
13	NULL	NULL	NULL
14	NULL	NULL	NULL

So you can see, the last values are NULL because the LAG can't use the last calculated value.

Do you have any idea how to get the last value, doesn't matter how many NULL-s are in the set?

(15, NULLx20, 10) => 15x21, 10

Thanks

Best regards

dd