SQL Server 2019 单查询实现时间序列缺口前向填充

此前编写的右连接查询无法达成目标的核心原因：关联逻辑仅以DateId作为匹配条件，未预先构建单证券维度下的全量日期基准网格。举个直观的例子，当SecurityId=1在DateId=2无记录、SecurityId=2在DateId=2有记录时，原查询会错误地把SecurityId=2的价格关联到DateId=2的行上，完全无法定位单个证券自身的时序数据缺口。

整套方案全部通过CTE实现，无临时表、无游标，完全适配SQL Server 2019标准版语法，执行流程共3层：

• 提取所有不重复的证券标识集合，和全量日期维度表做笛卡尔积，生成每个证券对应每个全量日期的基准锚点，保证时序范围无遗漏
• 将基准网格左关联原始时序表，关联条件同时匹配SecurityId和DateId，保留所有基准行的同时匹配上已有的价格值，缺口行的Value字段自然为NULL
• 适配SQL Server 2019不支持窗口函数IGNORE NULLS的版本限制，对每个证券分组按日期排序，通过分组标记非空值的所属区间，实现缺口位置的前向值填充

完整可运行SQL #

WITH AllSecurities AS (
    -- 提取所有不重复的证券标识
    SELECT DISTINCT SecurityId 
    FROM TimeSerie
),
FullDateSecurityGrid AS (
    -- 生成每个证券对应全量日期的基准网格，无遗漏
    SELECT 
        s.SecurityId,
        dt.DateId,
        dt.FullDate -- 替换为实际表中的日期字段名
    FROM AllSecurities s
    CROSS JOIN Dates dt
),
MatchedWithRawData AS (
    -- 关联原始时序数据，缺口位置Value为NULL
    SELECT 
        g.SecurityId,
        g.DateId,
        g.FullDate,
        px.Value
    FROM FullDateSecurityGrid g
    LEFT JOIN TimeSerie px
        ON g.SecurityId = px.SecurityId
        AND g.DateId = px.DateId
),
ForwardFillMarked AS (
    -- 标记每个非空价格关联的日期位置，为前向填充做分组
    SELECT 
        SecurityId,
        DateId,
        FullDate,
        Value,
        -- 非空值记录当前DateId，空值继承上一个非空值的DateId
        MAX(CASE WHEN Value IS NOT NULL THEN DateId END) 
            OVER (PARTITION BY SecurityId ORDER BY DateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastValidDateId
    FROM MatchedWithRawData
)
-- 匹配取每个分组内最近的非空价格，完成前向填充
SELECT 
    mf.SecurityId,
    mf.DateId,
    mf.FullDate,
    MAX(mr.Value) AS FilledValue
FROM ForwardFillMarked mf
LEFT JOIN MatchedWithRawData mr
    ON mf.SecurityId = mr.SecurityId
    AND mf.LastValidDateId = mr.DateId
GROUP BY mf.SecurityId, mf.DateId, mf.FullDate
ORDER BY mf.SecurityId, mf.DateId;

• 所有关联逻辑必须同时匹配SecurityId和DateId，仅按DateId关联是此前方案失效的核心原因
• 上述前向填充逻辑完全兼容SQL Server 2019，不需要依赖2022版本才引入的IGNORE NULLS窗口函数参数，不存在版本兼容问题
• 如果部分证券从最早的日期开始就没有任何价格记录，这类行的FilledValue会为NULL，属于业务上的合理空值，可根据实际需求额外配置默认值替换

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