SQL Server环境下使用平均法计算库存交易每行销货成本(COGS)

你需要的是逐行滚动计算的移动加权平均成本，SQL Server 环境下可以通过递归CTE实现，代码如下：

实现逻辑 #

• 先给同产品的所有交易记录按时间排序生成唯一行号
• 递归处理每一行，逐行累计期初库存数量、金额，入库时重新计算单位平均成本，出库时用当前平均成本核算COGS
• 逐行结转期末库存数量、金额作为下一行的期初值

WITH RankedTransactions AS (
    -- 给同产品的交易按日期排序生成行号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY [date]) AS rn
    FROM SampleData
),
RecursiveCalc AS (
    -- 初始化第一条记录
    SELECT 
        rn,
        [date],
        product_id,
        Qty,
        price,
        progress,
        CASE WHEN progress = 'IN' THEN Qty ELSE 0 END AS end_qty,
        CASE WHEN progress = 'IN' THEN Qty * price ELSE 0 END AS end_amount,
        CASE WHEN progress = 'IN' THEN price ELSE 0 END AS avg_unit_cost,
        0 AS cogs
    FROM RankedTransactions
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续每一行
    SELECT 
        curr.rn,
        curr.[date],
        curr.product_id,
        curr.Qty,
        curr.price,
        curr.progress,
        prev.end_qty + curr.Qty AS end_qty,
        CASE 
            WHEN curr.progress = 'IN' THEN prev.end_amount + curr.Qty * curr.price
            ELSE (prev.end_qty + curr.Qty) * prev.avg_unit_cost
        END AS end_amount,
        CASE 
            WHEN curr.progress = 'IN' THEN (prev.end_amount + curr.Qty * curr.price) / (prev.end_qty + curr.Qty)
            ELSE prev.avg_unit_cost
        END AS avg_unit_cost,
        CASE 
            WHEN curr.progress = 'OUT' THEN ABS(curr.Qty) * prev.avg_unit_cost
            ELSE 0
        END AS cogs
    FROM RecursiveCalc prev
    INNER JOIN RankedTransactions curr ON prev.product_id = curr.product_id AND curr.rn = prev.rn + 1
)
-- 输出最终结果
SELECT 
    [date],
    product_id,
    Qty,
    price,
    progress,
    avg_unit_cost AS 单位平均成本,
    cogs AS 销货成本(COGS),
    end_qty AS 期末库存数量,
    end_amount AS 期末库存金额
FROM RecursiveCalc
ORDER BY product_id, [date]
OPTION (MAXRECURSION 0); -- 交易记录超过100行时需添加该参数取消递归层数限制

注意事项 #

• 代码默认同一产品按日期排序处理，如果存在同日期多笔交易的场景，可以在排序规则中补充其他字段保证顺序符合业务要求
• 数值精度可根据业务需要自行调整，可对avg_unit_cost、end_amount字段添加ROUND函数指定保留小数位数
• 测试数据中出库记录的price字段为0不影响计算，出库成本取结转的历史平均成本，不会读取当前行的price值

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