Fill NULL value with next non-NULL value over date
Hello everyone, I'm trying to do something that is really simple in other data base engines like postgres.
I have a column with null values that I want to fill with the next non-null value. My expected output is something like this:
date | some_value | filled_value |
---|---|---|
2021-01-01 | V0001 | V0001 |
2021-01-02 | V0002 | V0002 |
2021-01-05 | NULL |
V0003 |
2021-01-07 | NULL |
V0003 |
2021-01-08 | V0003 | V0003 |
I've tried following the documentation for the LAST_VALUE
, FIRST_VALUE
, TS_FIRST_VALUE
and TS_LAST_VALUE
functions, however I can't get it to work on a simple table like the one above. The documentation is very poorly written with no reproducible examples to get started.
I've tried the following query:
select *, ts_last_value(some_value) filled_value from example_table timeseries slice_time as '1 day' over (partition by 1 order by date::timestamp)
but I can't get it to work, I get this weird error message, tried google but no good answer from that.
ERROR: Column "example_table.date" must appear in the PARTITION BY list of Timeseries clause or be used in a Time Series Aggregate Function
I am seriously frustrated with this, I'm following as best as I can the documentation for the TS_FIRST_VALUE
and TS_LAST_VALUE
functions, the query is very similar.
Answers
How about this?