使用MSSQL LAG函数计算库存时返回NULL问题排查

需要修正表中quantity_closing_stock列的0值，规则是用上一行的quantity_closing_stock减去当日的quantity来替换0值。原始的quantity_closing_stock是已获取的独立数据，无预先生成逻辑。尝试用MSSQL的LAG函数实现，但计算后对应位置返回NULL值。

初始样本数据 #

期望输出 #

尝试的代码 #

WITH mycte ([timestamp],quantity_closing_stock)  AS (
    SELECT [timestamp],
        LAG(quantity_closing_stock) OVER (ORDER BY timestamp)
    FROM #my_table
    WHERE quantity_closing_stock = 0

)
    UPDATE #my_table
    SET quantity_closing_stock = mycte.quantity_closing_stock - quantity
    FROM #my_table AS id
     JOIN mycte ON mycte.[timestamp] = id.[timestamp]
        
SELECT * FROM  #my_table ORDER BY timestamp ASC

问题分析与修正方案 #

原代码的问题在于CTE仅筛选了quantity_closing_stock = 0的行，导致LAG函数只能在这些0值行的范围内取上一行数据，无法获取全表排序后真正的上一行库存值，因此返回NULL。

修正后的代码 #

WITH updated_data AS (
    SELECT 
        unit_id,
        [timestamp],
        quantity,
        quantity_closing_stock,
        -- 按unit_id分组、timestamp排序，获取同一单位下上一行的库存值
        LAG(quantity_closing_stock) OVER (PARTITION BY unit_id ORDER BY [timestamp]) AS prev_closing_stock
    FROM #my_table
)
UPDATE #my_table
SET quantity_closing_stock = ud.prev_closing_stock - ud.quantity
FROM #my_table t
JOIN updated_data ud ON t.unit_id = ud.unit_id AND t.[timestamp] = ud.[timestamp]
WHERE t.quantity_closing_stock = 0;

SELECT * FROM #my_table ORDER BY [timestamp] ASC;

关键说明 #

1. PARTITION BY unit_id：如果表中存在多个unit_id，确保只取同一单位下的上一行数据，避免跨单位取错值。
2. 全表计算上一行库存：不对数据做提前筛选，保证每个行都能获取到正确的上一行quantity_closing_stock值。
3. 精准更新0值行：通过JOIN关联原表，仅更新quantity_closing_stock为0的记录，不影响其他正常数据。

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