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
