Sorry for bumping the question maybe it got shadowed by foll

Question

Sorry for bumping the question maybe it got shadowed by following ones gt Hello gt What is the recommended approach to getting the last non null value gt Use a UDF

Kishore G · Answer

Last non null value for a key

Kishore G · Answer

Or across lot of keys

Jonathan Meyer · Answer

Across lot of keys Like in a periodic timeseries ex 1 data point per day where some values ex some days are null How can we efficiently fetch the last non null value

Jonathan Meyer · Answer

Date Value 05 01 2021 null 04 01 2021 null 03 01 2021 3 0 02 01 2021 1 0 Output 3 0

Kishore G · Answer

Ah got it

Jonathan Meyer · Answer

Maybe it d be simpler not to store null values and get the `last` value Not sure that d be more efficient that way

Kishore G · Answer

Select date Val from T where F value gt 0 order by date desc limit 1 should work

Kishore G · Answer

Writing a udf might be more efficient but see if this works for now

Jonathan Meyer · Answer

Makes sense indeed Do you expect this query to be highly performant Should a specific index be added

Jonathan Meyer · Answer

Actually what about negative values We d be interested in those too ^^ I know there s some support for null values as in IS NOT NULL but iirc there s a performance penalty associated with this feature

Kishore G · Answer

No not null index is very fast

Kishore G · Answer

So you can use that

Jonathan Meyer · Answer

So enable this feature `nullHandlingEnabled` And query like `SELECT value FROM table WHERE value IS NOT NULL ORDER BY date LIMIT 1`

Jonathan Meyer · Answer

Sounds right

Kishore G · Answer

yes

Jonathan Meyer · Answer

Great thank you < Kishore G> slightly smiling face Have a good day