Sorry for bumping the question, maybe it got shado...
# troubleshooting
j
Sorry for bumping the question, maybe it got shadowed by following ones.
Hello
What is the recommended approach to getting the "last non-null value" ?
Use a UDF ?
k
Last non null value for a key?
Or across lot of keys
j
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 ?
Date - Value 05/01/2021 - null 04/01/2021 - null 03/01/2021 - 3.0 02/01/2021 - 1.0 Output: 3.0
k
Ah, got it
j
Maybe, it'd be simpler not to store null values and get the
last
value ? Not sure that'd be more efficient that way
k
Select date, Val from T where F value >0 order by date desc limit 1 should work
Writing a udf might be more efficient but see if this works for now
1
j
Makes sense indeed ! Do you expect this query to be highly performant ? Should a specific index be added ?
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
k
No, not null index is very fast
So you can use that
j
So, enable this feature (
nullHandlingEnabled
) And query like
SELECT value FROM table WHERE value IS NOT NULL ORDER BY date LIMIT 1
Sounds right ?
k
yes
j
Great, thank you @Kishore G 🙂 Have a good day !