SQL Server如何基于首行值按日期升序级联更新Price列空值

错误原因分析 #

你之前使用LAG函数的写法无法生效的核心原因是：SQL的UPDATE操作是「快照式」执行的，窗口函数读取的是UPDATE执行前的原表数据快照，不会读取同一次更新中刚刚修改的行值，因此每次执行只能更新紧接基准行的下一行，无法完成多行级联累积计算。

实现方案 #

针对SQL Server 2019，我们可以利用对数的数学特性实现累积乘法运算，一次性完成所有Price空值的填充，完整执行语句如下：

WITH BaseData AS (
    -- 提取最早日期的基准Price值
    SELECT TOP 1 Price AS BasePrice
    FROM StockA
    WHERE Price IS NOT NULL
    ORDER BY date ASC
),
CalcCTE AS (
    SELECT 
        date,
        PercentChange,
        Price,
        -- 按日期升序累积计算乘积，还原得到当前行的Price
        BasePrice * EXP(SUM(LOG(1 + PercentChange)) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS CalculatedPrice
    FROM StockA
    CROSS JOIN BaseData
    -- 仅处理Price为空的行，保留基准行原值
    WHERE Price IS NULL
)
UPDATE CalcCTE
-- 保留2位小数与字段类型匹配
SET Price = ROUND(CalculatedPrice, 2)

实现逻辑说明 #

• 利用对数性质转换运算：a*b*c = EXP(LOG(a) + LOG(b) + LOG(c))，将累积乘法转换为窗口函数可以直接实现的累积求和运算，避免逐行迭代
• 窗口函数的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW限定按日期升序，从最早的空值行开始逐行累积计算，一次性得到所有行的结果
• 仅更新Price为空的行，不会修改基准行的原始值

结果验证 #

执行完更新后，运行查询语句验证结果：

SELECT * FROM StockA ORDER BY date ASC

得到的结果符合预期：

边界注意事项 #

如果业务场景中可能出现1 + PercentChange <= 0的情况（比如涨跌幅小于等于-100%），需要额外增加CASE判断处理异常值，避免对数计算报错。

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