Re: Window function for get the last value to extend missing rows

[GF <phabriz@xxxxxxxxx>]

On Fri, 12 May 2023 at 13:04, Durumdara <durumdara@xxxxxxxxx> wrote:

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

Unfortunately, as per https://www.postgresql.org/docs/current/functions-window.html

"The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namely RESPECT NULLS".

So, I'd keep the incoming data as is, i.e. with no nulls in values, and densify it with some generate_series magic:

select gmin as mmin, d.value

from

    ( select mmin, lead(mmin) over (order by mmin) nextmin, value from test_table ) d,

    generate_series(d.mmin, nextmin - interval'1 minute') gmin

(I assumed a time representation for mmin, but adapt the generate_series call to whatever your representation is)

Best,

g