如何在DolphinDB中妥善处理股票逐笔行情数据的连续NULL值？

在DolphinDB处理股票逐笔报价数据时，因买卖盘不平衡产生大量NULL值，尝试过多种填充方法但无法处理连续NULL值的情况。

示例数据 #

// 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

已尝试方法 #

1. 直接计算产生大量NULL值 #

// 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

spread列存在大量NULL值，导致后续统计分析无法进行。

2. 使用prev()函数无法处理连续NULL值 #

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)

该方法仅能填充单个NULL值，当多个时间点存在连续NULL值时，后续记录仍为NULL。

解决方案 #

方法1：使用ffill函数进行前向填充 #

DolphinDB内置的ffill函数可以沿序列（按股票分组后）将连续NULL值替换为最近的前一个非NULL值，完美解决连续NULL的问题。

// 按symbol分组，对买卖盘价格和成交量进行前向填充
filled_data = select tradetime, symbol,
    ffill(bid_price1) as filled_bid_price1,
    ffill(bid_vol1) as filled_bid_vol1,
    ffill(ask_price1) as filled_ask_price1,
    ffill(ask_vol1) as filled_ask_vol1,
    last_price
from tick_quotes
context by symbol

// 计算spread，此时NULL值大幅减少
result = select tradetime, filled_bid_price1, filled_ask_price1,
    (filled_ask_price1 - filled_bid_price1) as spread
from filled_data

// 验证NULL数量
select count(*) as total, sum(isNull(spread)) as null_count from result

如果序列开头就存在NULL值，可以结合nullFill指定默认值（比如基准价）：

filled_bid_price1 = nullFill(ffill(bid_price1), base_price)

方法2：结合last_price的业务逻辑填充 #

考虑到last_price字段已填充了有效值，可以将其作为买卖盘缺失时的参考，再结合前向填充处理连续NULL：

enhanced_filled = select tradetime, symbol,
    // 用last_price填充单个NULL，再前向填充连续NULL
    ffill(iif(isNull(bid_price1), last_price, bid_price1)) as filled_bid_price1,
    ffill(iif(isNull(bid_vol1), prev(bid_vol1), bid_vol1)) as filled_bid_vol1,
    ffill(iif(isNull(ask_price1), last_price, ask_price1)) as filled_ask_price1,
    ffill(iif(isNull(ask_vol1), prev(ask_vol1), ask_vol1)) as filled_ask_vol1,
    last_price
from tick_quotes
context by symbol

这种方式更贴合股票交易的实际逻辑，成交价（last_price）可以作为买卖盘价格缺失时的合理替代。

内容的提问来源于stack exchange，提问作者Jane