Native query fill zero bucket with previous value

Hi to all, I cannot find a way to fill an empty bucket with previous values.

I put some context:

I’m developing a financial application that handles fixed income products, I want to store in apache druid the balance investments every time there is a variation, with a daily granularity.

in order to calculate the revenue in the last year, I have Total interest divided by the average annual invested budget.

The total interest is easy is just a sum of the value in the last year, but the average annual invested budged is complicated, I don’t want to store the days that there isn’t a variation I just want to use the previous value.

Is possible doing that in apache druid? I’ve read the documentation but the only option I can see is completely removing the bucket if there aren’t values.

Can you help me? thanks

Hi Mattia,

I think what you are looking for is the LATEST function.
Take a look at the SQL function docs here and search for “latest”.

1 Like

This sounds a bit onerous. I’ve seen some queries that take a previous value if the current one is, eg, 0, but they involve a join, which isn’t the best for performance. Also, what if the previous value is also 0? If there can be varying numbers of 0 days, I’m not sure how that would work. Can you give a pseudo-code example of the logic you want to use?

Throwing in that LEAD and LAG-type window functions (if that’s what would be useful) isn’t supported in Druid as yet: