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

[Kirk Wolak]

On Sat, May 13, 2023 at 2:18 AM Andrew Gierth <and...@tao11.riddles.org.uk>
wrote:

> >>>>> "Durumdara" == Durumdara  <durumd...@gmail.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;