Search code examples
databasetime-seriesquestdb

FILL missing intervals with Previous value from one column to multiple columns

I would like to have a version of this query:

select timestamp, id, sum(price) as price, sum(dayVolume) as dayVolume
from nasdaq_trades
where id = 'NVDA'
sample by 1s FILL(PREV, PREV)

But when there is an interpolation, instead of getting the PREVious value for price and previous for dayVolume I want both the price and the volume to show the PREVious known value for the dayVolume. Imagine this SQL was valid:

select timestamp, id, sum(price) as price, sum(dayVolume) as dayVolume
from nasdaq_trades
where id = 'NVDA'
sample by 1s FILL(PREV(dayVolume), PREV)
Solution

  • I believe the only way to do this is in multiple steps on a single query: first we get the sampled data interpolating with null values, then we use a window function to get the last non-null value for the DayVolume column, and finally we coalesce the missing columns with this filler value.

    with sampled as (
select timestamp, id, sum(price) as price, sum(dayVolume) as dayVolume
from nasdaq_trades
where id = 'NVDA'
sample by 1s FILL(null)
), with_previous_vals AS (
select *, 
last_value(dayVolume)  IGNORE NULLS OVER(partition by id order by timestamp) as filler
from sampled
)
SELECT timestamp, id, coalesce(price, filler), coalesce(dayVolume, filler)
FROM 
with_previous_vals

    Note I need to use IGNORE NULLS modifier on the window function to make sure we always look back for a value, rather than just over the previous row.