基于Bottom-Up方法计算期末库存总价值的SQL查询需求

问题背景 #

现有两张业务数据表：

• Purchase（采购表）：记录各商品的采购日期、采购数量及采购单价
• ClosingStock（期末库存表）：记录各商品的期末库存数量

需要基于Bottom-Up方法（优先使用最晚采购的库存计算价值），统计每个商品的期末库存总价值。

表结构 #

Purchase（采购表） #

+----------+--------------+-----+-----------+
| ItemName | PurchaseDate | QTY | CostPrice |
+----------+--------------+-----+-----------+
| ItemA    | 2022-03-14   |  20 | 32.00     |
| ItemA    | 2022-04-28   |   7 | 30.00     |
| ItemA    | 2022-06-17   |  33 | 25.00     |
| ItemB    | 2022-05-16   |  65 | 50.00     |
+----------+--------------+-----+-----------+

ClosingStock（期末库存表） #

+----------+--------------+
| ItemName | ClosingStock |
+----------+--------------+
| ItemA    |           35 |
| ItemB    |           60 |
+----------+--------------+

预期结果 #

+----------+--------------+------------+
| ItemName | ClosingStock | TotalValue |
+----------+--------------+------------+
| ItemA    |           35 |        885 |
| ItemB    |           60 |       3000 |
+----------+--------------+------------+

解决方案SQL #

WITH RankedPurchases AS (
    SELECT 
        ItemName,
        PurchaseDate,
        QTY,
        CostPrice,
        -- 按采购日期倒序排名，最晚采购的批次排第1位
        ROW_NUMBER() OVER (PARTITION BY ItemName ORDER BY PurchaseDate DESC) AS rn,
        -- 计算倒序累计采购量（从最晚采购开始累加）
        SUM(QTY) OVER (PARTITION BY ItemName ORDER BY PurchaseDate DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeQty
    FROM Purchase
),
StockAllocation AS (
    SELECT 
        rp.ItemName,
        cs.ClosingStock,
        rp.QTY,
        rp.CostPrice,
        -- 计算当前批次可分配的库存数量
        CASE 
            WHEN rp.CumulativeQty <= cs.ClosingStock THEN rp.QTY
            ELSE cs.ClosingStock - (rp.CumulativeQty - rp.QTY)
        END AS AllocatedQty
    FROM RankedPurchases rp
    JOIN ClosingStock cs ON rp.ItemName = cs.ItemName
    -- 过滤掉完全不参与库存分配的批次
    WHERE rp.CumulativeQty - rp.QTY < cs.ClosingStock
)
SELECT 
    ItemName,
    ClosingStock,
    SUM(AllocatedQty * CostPrice) AS TotalValue
FROM StockAllocation
GROUP BY ItemName, ClosingStock
ORDER BY ItemName;

计算逻辑说明 #

1. RankedPurchases 阶段：对每个商品的采购记录按采购日期倒序排序，同时计算从最晚采购开始的累计采购量，明确每个批次在Bottom-Up顺序中的位置。
2. StockAllocation 阶段：关联采购批次与期末库存，计算每个批次能分配的库存数量：
   • 若当前批次的累计采购量≤期末库存，整个批次全部计入库存价值；
   • 若累计采购量超过期末库存，仅取填补剩余库存所需的数量。
3. 最终聚合：对每个商品的分配数量×单价求和，得到期末库存总价值。

以ItemA为例：

• 先取最晚采购的33件（单价25），覆盖33件库存；
• 剩余2件库存取倒数第二批次的单价30计算；
• 总价值：33×25 + 2×30 = 885，与预期一致。

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