Window function for get the last value to extend missing rows

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Window function for get the last value to extend missing rows
Date: 2023-05-12 11:04:32
Message-ID: CAEcMXhmU=tzxhnHot2+5kYffBKJgjyvUtiK73wguSk4TsFNp1g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nathaniel Sabanski 2023-05-12 11:29:09 Adding SHOW CREATE TABLE
Previous Message shveta malik 2023-05-12 06:33:38 Re: Support logical replication of DDLs