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

On Sat, May 13, 2023 at 2:18 AM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Durumdara" == Durumdara <durumdara(at)gmail(dot)com> writes:
>
> create table tmp_test_table(mmin,val)
> as select o, v
> from unnest(array[1,5,NULL,3,NULL,NULL,10,7,NULL,NULL,NULL,4])
> with ordinality as u(v,o);
> select * from tmp_test_table order by mmin;
>

That seems like a lot of work.
If you have ALL the values (no missing values) a simple CTE handles this:

https://www.db-fiddle.com/f/wKyQV1imGsewR9Az7hi193/0

WITH RECURSIVE rec_cte(mmin, value) AS (
SELECT mmin, value from tmp_test_table where mmin=1
UNION ALL
SELECT t.mmin, COALESCE(t.value,r.value)
FROM tmp_test_table t, rec_cte r WHERE r.mmin=(t.mmin-1)
)
SELECT * from rec_cte order by mmin;