使用Recursive CTE计算库存逐行移动平均（Running Average）

针对你的需求，我们可以通过递归CTE来实现基于批次逆推的库存移动平均计算，完全匹配你手动计算的逻辑。

需求回顾 #

我们需要为AKTest表中的每一条采购记录，计算该记录完成后库存的加权平均价，预期结果如下：

数据表与测试数据 #

首先是表结构和测试数据的SQL代码：

CREATE TABLE [dbo].[AKTest]( 
    [IntakeSellingPrice] [decimal](38, 20) NULL, 
    [IntakeSellingAmount] [decimal](38, 6) NULL, 
    [Item No_] [nvarchar](20) NOT NULL, 
    [Variant Code] [nvarchar](10) NOT NULL, 
    [Unit of Measure Code] [nvarchar](10) NOT NULL, 
    [Posting Date] [datetime] NOT NULL, 
    [PurchaseQty] [decimal](38, 20) NULL, 
    [ReceiptNo] [bigint] NULL, 
    [InventoryBalance] [decimal](38, 20) NOT NULL, 
    [NewBalance] [decimal](38, 20) NULL 
) ON [PRIMARY] 
GO

INSERT [dbo].[AKTest] ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) 
VALUES (CAST(10.00000000000000000000 AS Decimal(38, 20)), CAST(1000.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A80800000000 AS DateTime), CAST(100.00000000000000000000 AS Decimal(38, 20)), 1, CAST(0.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20))) 
GO

INSERT [dbo].[AKTest] ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) 
VALUES (CAST(5.00000000000000000000 AS Decimal(38, 20)), CAST(250.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A80E00000000 AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)), 2, CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20))) 
GO

INSERT [dbo].[AKTest] ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) 
VALUES (CAST(12.50000000000000000000 AS Decimal(38, 20)), CAST(625.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A81900000000 AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)), 3, CAST(60.00000000000000000000 AS Decimal(38, 20)), CAST(110.00000000000000000000 AS Decimal(38, 20))) 
GO

手动计算逻辑（以ReceiptNo 3为例） #

正如你所说，建议从最后一行逆推计算：

A) 从ReceiptNo 3开始，其NewBalance为110单位。
B) 本次采购50单位，单价12.50，总金额625。
C) 剩余60单位，取上一行（ReceiptNo 2）采购的50单位，单价5.00，总金额250。
D) 剩余10单位，取ReceiptNo 1采购的100单位中的10单位，对应金额为1000/100*10=100。
E) 总金额求和：625+250+100=975，除以总数量110，得到平均价8.86。

递归CTE解决方案 #

下面的递归CTE会为每个ReceiptNo从自身开始，向上追溯更早的采购批次，累计数量和金额直到满足当前库存数量，最终计算出平均价：

WITH InventoryCTE AS (
    -- 锚点成员：初始化每个ReceiptNo的递归，从自身采购批次开始
    SELECT 
        ReceiptNo,
        CurrentReceiptNo = ReceiptNo,
        RemainingQty = NewBalance,
        UsedQty = CASE WHEN PurchaseQty >= NewBalance THEN NewBalance ELSE PurchaseQty END,
        TotalAmount = CASE WHEN PurchaseQty >= NewBalance THEN (NewBalance * IntakeSellingPrice) ELSE IntakeSellingAmount END,
        CumulativeQty = CASE WHEN PurchaseQty >= NewBalance THEN NewBalance ELSE PurchaseQty END
    FROM AKTest

    UNION ALL

    -- 递归成员：如果还有剩余库存数量需要凑齐，就向上取前一个采购批次的数量
    SELECT 
        ic.ReceiptNo,
        t.ReceiptNo,
        RemainingQty = ic.RemainingQty - t.PurchaseQty,
        UsedQty = CASE WHEN t.PurchaseQty >= ic.RemainingQty THEN ic.RemainingQty ELSE t.PurchaseQty END,
        TotalAmount = ic.TotalAmount + CASE WHEN t.PurchaseQty >= ic.RemainingQty THEN (ic.RemainingQty * t.IntakeSellingPrice) ELSE t.IntakeSellingAmount END,
        CumulativeQty = ic.CumulativeQty + CASE WHEN t.PurchaseQty >= ic.RemainingQty THEN ic.RemainingQty ELSE t.PurchaseQty END
    FROM InventoryCTE ic
    JOIN AKTest t ON t.ReceiptNo = ic.CurrentReceiptNo - 1
    WHERE ic.RemainingQty > 0
)
-- 最终查询：筛选出每个ReceiptNo完成累计的记录，计算平均价并格式化
SELECT 
    ReceiptNo,
    ROUND(TotalAmount / NewBalance, 2) AS Average
FROM (
    SELECT 
        ic.ReceiptNo,
        ic.TotalAmount,
        ak.NewBalance,
        -- 每个ReceiptNo只取累计完成的第一条记录
        ROW_NUMBER() OVER (PARTITION BY ic.ReceiptNo ORDER BY ic.CumulativeQty DESC) AS rn
    FROM InventoryCTE ic
    JOIN AKTest ak ON ic.ReceiptNo = ak.ReceiptNo
    WHERE ic.RemainingQty <= 0
) sub
WHERE rn = 1
ORDER BY ReceiptNo;

结果验证 #

运行上述代码后，会得到完全符合预期的结果：

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