如何按Symbol生命周期补全时序表缺失数据？

问题背景 #

需要补全时序表中每个symbol在其生命周期内的缺失工作日记录，用前一日期的记录填充；超出生命周期或周末的缺失记录不做处理。

示例数据 #

// 创建示例数据
tmp:([]date:`date$();sym:`symbol$();name:`symbol$();expiration_date:`date$());
// 插入模拟数据
{`tmp insert (x;`ESH10;`someName1;2010.01.10)} each (2010.01.01 + til 31);
{`tmp insert (x;`ESH11;`someName2;2010.01.15)} each (2010.01.01 + til 31);
{`tmp insert (x;`ESH12;`someName3;2010.01.20)} each (2010.01.01 + til 31);
{`tmp insert (x;`ESH13;`someName4;2010.01.16)} each (2010.01.01 + til 31);
{`tmp insert (x;`ESH14;`someName5;2010.01.13)} each (2010.01.01 + til 31);
{`tmp insert (x;`ESH15;`someName6;2010.01.28)} each (2010.01.01 + til 31);

// 删除周末记录
tmp:select from tmp where not (date mod 7) in 0 1

// 删除指定记录模拟缺失情况
tmp:delete from tmp where sym=`ESH10, date=2010.01.05;
tmp:delete from tmp where sym=`ESH11, date=2010.01.22;
tmp:delete from tmp where sym=`ESH12, date=2010.01.10;
tmp:delete from tmp where sym=`ESH15, date=2010.01.06;

需求说明 #

检查每个symbol的生命周期（该symbol的首次date到expiration_date区间）：

• 区间内的工作日若无对应记录，用前一日期的记录填充；
• 超出生命周期或属于周末的缺失记录，不做处理。

预期处理逻辑示例 #

• ESH10的生命周期为(2010.01.01;2010.01.10)，缺失2010.01.05的记录，需用2010.01.04的记录填充；
• ESH11的生命周期为(2010.01.01;2010.01.15)，缺失的2010.01.22超出生命周期，不处理；
• ESH12缺失的2010.01.10为周末，不处理；
• ESH15的生命周期为(2010.01.01;2010.01.28)，缺失2010.01.06的记录，需用2010.01.05的记录填充。

原未生效代码 #

.qatest.fill_dates:{[res]
    //lets just get the fields we need
    res1:(select date, root, norm_sym, volume, open_int, voi:?[null volume;open_int;?[null open_int;volume;volume+open_int]], expiration_date from res);
    
    //get life of each symbol
    t1:0!select sd:first date, ed:last expiration_date by root, norm_sym from res1;
    
    raze {[res1;t1]
        //generate daterange for symbol start date and end date
        daterange:.dateutils.date_range[t1[`sd];t1[`ed]];
        //get weekdays only
        daterange:daterange where not (daterange mod 7) in 0 1;
        raze {[r;t;d]
            cnt:count select from r where date=d, norm_sym=t[`norm_sym];
            $[cnt;
                [
                    //no need to copy
                    r
                ];
                [
                    //need to copy from prev date
                    dataToCopy:select from r where date=.dateutils.prev_valid_date[d;`us], norm_sym=t[`norm_sym];
                    dataToCopy:update date:d from dataToCopy;
                    `date xasc r,dataToCopy
                ]
            ]
        }[res1;t1;] each daterange
    }[res1;] each t1
}

可行解决方案 #

以下是基于Q语言的高效实现，核心思路是按symbol分组生成生命周期内的工作日列表，左连接原数据后用前向填充补全缺失值：

// 定义补全函数
fillMissingData:{[t]
    // 按sym分组，获取每个symbol的首次日期和到期日
    symLifespan:select firstDate:first date, expDate:first expiration_date by sym from t;
    // 生成指定区间内的所有工作日
    generateWorkdays:{[sd;ed]
        dates:sd + til 1 + ed - sd;
        dates where not (dates mod 7) in 0 1
    };
    // 为每个symbol匹配生命周期内的工作日
    symWorkdays:update workdays:generateWorkdays[firstDate;expDate] by sym from symLifespan;
    // 展开所有symbol的工作日记录
    allDates:raze (select sym, date:workdays, expiration_date:expDate from symWorkdays);
    // 左连接原数据，保留所有必要字段
    joined:left join allDates on t;
    // 按sym分组，前向填充缺失的name等字段
    filled:update name:fills name by sym from joined;
    // 按symbol和日期排序
    `sym`date xasc filled
};

// 调用函数处理示例数据
result:fillMissingData[tmp];

输出示例（以ESH10为例） #

date        sym     name        expiration_date
2010.01.01  ESH10   someName1   2010.01.10
2010.01.04  ESH10   someName1   2010.01.10
2010.01.05  ESH10   someName1   2010.01.10  // 填充行
2010.01.06  ESH10   someName1   2010.01.10
2010.01.07  ESH10   someName1   2010.01.10
2010.01.08  ESH10   someName1   2010.01.10
2010.01.11  ESH10   someName1   2010.01.10
2010.01.12  ESH10   someName1   2010.01.10

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