Fill Missing Intervals with Value from Another Column

Fill missing intervals using the previous value from a specific column to populate multiple columns.

Problem

You have a query like this:

SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
FROM core_price
WHERE symbol = 'EURUSD' AND timestamp IN today()
SAMPLE BY 1s FILL(PREV, PREV);

But when there is an interpolation, instead of getting the PREV value for bid_price and previous for ask_price, you want both prices to show the PREV known value for the ask_price. Imagine this SQL was valid:

SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
FROM core_price
WHERE symbol = 'EURUSD' AND timestamp IN today()
SAMPLE BY 1s FILL(PREV(ask_price), PREV);

Solution

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

Fill bid and ask prices with value from ask priceDemo this query
WITH sampled AS (
  SELECT timestamp, symbol, avg(bid_price) as bid_price, avg(ask_price) as ask_price
  FROM core_price
  WHERE symbol = 'EURUSD' AND timestamp IN today()
  SAMPLE BY 1s FILL(null)
), with_previous_vals AS (
  SELECT *,
    last_value(ask_price) IGNORE NULLS OVER(PARTITION BY symbol ORDER BY timestamp) as filler
  FROM sampled
)
SELECT timestamp, symbol, coalesce(bid_price, filler) as bid_price, coalesce(ask_price, filler) as ask_price
FROM with_previous_vals;

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

Problem​

Solution​

Problem

Solution