I'm processing stock tick-by-tick quote data in DolphinDB and encountering numerous NULL values (caused by imbalanced bid/ask quotes). I've tried several methods to fill these NULL values, but none have successfully handled consecutive NULLs.

Sample Data:

// Generate sample data
n = 20
tradetime = 2024.01.08T09:30:00.000 + (1..n) * 500  
symbol = take(`000001, n)

base_price = 10.25
bid_price1 = base_price + rand(0.05, n) - 0.02  
ask_price1 = bid_price1 + rand(0.03, n) + 0.01  

bid_null_mask = rand(1.0, n) < 0.3
ask_null_mask = rand(1.0, n) < 0.3
bid_price1[bid_null_mask] = NULL
ask_price1[ask_null_mask] = NULL

bid_vol1 = rand(2000, n) + 500  
ask_vol1 = rand(2000, n) + 500
bid_vol1[bid_null_mask] = NULL 
ask_vol1[ask_null_mask] = NULL

last_price = (bid_price1 + ask_price1) \ 2 
last_price = nullFill(last_price, base_price) 

tick_quotes = table(tradetime, symbol, bid_price1, bid_vol1, ask_price1, ask_vol1, last_price)

select * from tick_quotes

What I've Tried:

1. Direct calculation results in many NULL values

// Calculate bid-ask spread
result1 = select tradetime, bid_price1, ask_price1, 
    (ask_price1 - bid_price1) as spread 
from tick_quotes

// Check NULL count
select count(*) as total, sum(isNull(spread)) as null_count from result1

The spread column contains many NULL values, making subsequent statistical analysis impossible.

2. Using prev() function cannot handle consecutive NULLs

t4 = select tradetime,
    iif(isNull(bid_price1), prev(bid_price1), bid_price1) as bid
from tick_quotes
context by symbol

select * from t4 where isNull(bid)

This method only fills a single NULL value. When there are consecutive NULL values across multiple time points, the subsequent records remain NULL.

My Question:

How should I properly handle consecutive NULL values in this scenario?