Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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.

Tagged:

Answers

  • Jim_KnicelyJim_Knicely Administrator

    How about this?

    verticademos=> SELECT * FROM some_data;
        date    | some_value | filled_value
    ------------+------------+--------------
     2021-01-01 | V0001      | V0001
     2021-01-02 | V0002      | V0002
     2021-01-05 |            | V0003
     2021-01-07 |            | V0003
     2021-01-08 | V0003      | V0003
    (5 rows)
    
    verticademos=> SELECT date, FIRST_VALUE(some_value IGNORE NULLS) OVER (ORDER BY date RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) some_value, filled_value FROM some_data;
        date    | some_value | filled_value
    ------------+------------+--------------
     2021-01-01 | V0001      | V0001
     2021-01-02 | V0002      | V0002
     2021-01-05 | V0003      | V0003
     2021-01-07 | V0003      | V0003
     2021-01-08 | V0003      | V0003
    (5 rows)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.