sql-server - 使用递归 CTE 计算股票的运行平均值

Question

我有以下数据，需要使用前一行的金额计算每行的运行平均值。

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        Average 
_________________________
 1                 10.00

 2                 7.50

 3                 8.86

我用于手动计算的公式在下面为第三行定义。如果先从底部开始计算会更好。

A) 我从底部开始使用receiptNo 3，其中NewBalance 为110。

B) 购买 50 件的价格为 12.50 = 625

C) 还剩 60 个单位。上一行购买了 50 个单位，价格为 5 = 250

D) 还剩 10 个单位。在上一行中，购买了 100 个单位，价格为 10 = 1000。但我们只需要 10 的成本，因此 1000/10 = 100。

E) 将所有成本相加 625 + 250 + 100 = 975/110 = 8.86

Answer 1

我认为使用 ROWS BETWEEN 和 OVER 是不可能的，因为逻辑有点奇怪？

我创建了一个临时表来处理数据并测试结果，但基本上这只是添加了递归 CTE 的原始脚本:

CREATE TABLE #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);
GO
INSERT #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 #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 #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
SELECT * FROM #AKTest;

WITH cte AS (
    SELECT
        ReceiptNo,
        ReceiptNo AS linked_to,
        NewBalance,
        NewBalance - PurchaseQty AS remaining,
        PurchaseQty AS purchased,
        IntakeSellingPrice
    FROM
        #AKTest
    UNION ALL
    SELECT
        c.ReceiptNo,
        c.linked_to - 1 AS linked_to,
        a.NewBalance,
        c.remaining - a.PurchaseQty AS remaining,
        CASE WHEN a.PurchaseQty > c.remaining THEN c.remaining ELSE a.PurchaseQty END AS purchased,
        a.IntakeSellingPrice
    FROM
        cte c
        INNER JOIN #AKTest a ON a.ReceiptNo = c.linked_to - 1
    WHERE
        c.linked_to > 1)
SELECT
    ReceiptNo,
    SUM(purchased * IntakeSellingPrice) / MAX(NewBalance) AS avg_price
FROM
    cte
GROUP BY
    ReceiptNo
ORDER BY
    ReceiptNo;

得到正确答案:

ReceiptNo   avg_price
1           10.000000
2           7.500000
3           8.863636

根据要求，这将显示表格中的所有数据，最后是平均价格:

WITH cte AS (
    SELECT
        ReceiptNo,
        ReceiptNo AS linked_to,
        NewBalance,
        NewBalance - PurchaseQty AS remaining,
        PurchaseQty AS purchased,
        IntakeSellingPrice
    FROM
        #AKTest
    UNION ALL
    SELECT
        c.ReceiptNo,
        c.linked_to - 1 AS linked_to,
        a.NewBalance,
        c.remaining - a.PurchaseQty AS remaining,
        CASE WHEN a.PurchaseQty > c.remaining THEN c.remaining ELSE a.PurchaseQty END AS purchased,
        a.IntakeSellingPrice
    FROM
        cte c
        INNER JOIN #AKTest a ON a.ReceiptNo = c.linked_to - 1
    WHERE
        c.linked_to > 1),
Averages AS (
    SELECT
        ReceiptNo,
        SUM(purchased * IntakeSellingPrice) / MAX(NewBalance) AS avg_price
    FROM
        cte 
    GROUP BY
        ReceiptNo)
SELECT
    a.*,
    v.avg_price
FROM
    Averages v
    INNER JOIN #AKTest a ON a.ReceiptNo = v.ReceiptNo
ORDER BY
    a.ReceiptNo;