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