SQL Server 2019计算库存滚动数量/价值及移动平均单位成本

实现思路 #

由于该计算属于逐行依赖前序结果的状态型计算，普通聚合窗口函数无法直接实现跨行状态传递，因此采用递归CTE+按tId有序迭代的方案实现，10万行数据量级下只要tId字段建有主键/索引，性能完全满足业务需求。

核心计算逻辑 #

• 滚动库存数量（Run_Qty）：逐行累加tQty即可，不受单据类型影响
• 滚动库存价值（Run_Value）：按单据类型区分计算规则
  • PO_IN：前序滚动价值 + 当前入库成本
  • SO_OUT：前序滚动价值 - 出库数量绝对值 * 前序平均成本
  • FIN_ADJ：前序滚动价值 + 财务调整额
• 移动平均单位成本（avg_cost）：当前滚动价值 / 当前滚动库存数量（库存为0时取0）

实现代码 #

WITH RecurInv AS (
    -- 锚点：取最小tId作为第一行计算基准
    SELECT TOP 1
        tId, tType, tcode, tDate, tQty, tValue,
        CAST(tQty AS NUMERIC(19,6)) AS Run_Qty,
        CAST(tValue AS NUMERIC(19,6)) AS Run_Value,
        CASE WHEN tQty > 0 THEN CAST(tValue / tQty AS NUMERIC(19,6)) ELSE 0 END AS avg_cost
    FROM tst_Inv
    ORDER BY tId ASC
    UNION ALL
    -- 递归部分：逐行计算后续所有单据
    SELECT
        curr.tId, curr.tType, curr.tcode, curr.tDate, curr.tQty, curr.tValue,
        -- 计算滚动库存数量
        CAST(prev.Run_Qty + curr.tQty AS NUMERIC(19,6)) AS Run_Qty,
        -- 按类型计算滚动库存价值
        CAST(
            CASE curr.tType
                WHEN 'PO_IN' THEN prev.Run_Value + curr.tValue
                WHEN 'SO_OUT' THEN prev.Run_Value - (ABS(curr.tQty) * prev.avg_cost)
                WHEN 'FIN_ADJ' THEN prev.Run_Value + curr.tValue
            END AS NUMERIC(19,6)
        ) AS Run_Value,
        -- 计算当前平均成本
        CASE
            WHEN (prev.Run_Qty + curr.tQty) > 0 THEN
                CAST(
                    (CASE curr.tType
                        WHEN 'PO_IN' THEN prev.Run_Value + curr.tValue
                        WHEN 'SO_OUT' THEN prev.Run_Value - (ABS(curr.tQty) * prev.avg_cost)
                        WHEN 'FIN_ADJ' THEN prev.Run_Value + curr.tValue
                    END) / (prev.Run_Qty + curr.tQty)
                AS NUMERIC(19,6))
            ELSE 0
        END AS avg_cost
    FROM RecurInv prev
    INNER JOIN tst_Inv curr ON curr.tId = prev.tId + 1
)
SELECT * FROM RecurInv
ORDER BY tId ASC
OPTION (MAXRECURSION 0); -- 取消递归次数限制，适配10万行以上数据

样例输出结果 #

性能优化建议 #

• 给tId字段创建主键聚集索引，递归关联时效率提升明显
• 多SKU核算的话，可以按tcode分区，先给每个SKU生成连续行号再做递归，避免跨SKU的计算干扰

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