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

On Fri, 12 May 2023 at 13:04, Durumdara <durumdara(at)gmail(dot)com> 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