SQL Server使用SUM()窗口函数实现FIFO库存批次与销售单关联问题

实现方案 #

要实现FIFO批次匹配，核心是避免销售表和库存表直接关联产生的笛卡尔积，我们可以先分别计算销售的累计销量区间、库存的累计库存区间，再通过区间匹配实现单批次优先扣减的效果。

以下是可直接运行的完整查询脚本：

WITH SalesWithCum AS (
    -- 计算按时间排序的销售累计销量，得到每笔销售的销量覆盖区间
    SELECT 
        DWF_SALES_INVOICE_NUM,
        DWF_SALES_INVOICE_DATE,
        DWF_SALES_ITEM_CODE,
        DWF_SALES_SALES_QTY,
        -- 累计到上一笔的总销量
        COALESCE(SUM(DWF_SALES_SALES_QTY) OVER(
            PARTITION BY DWF_SALES_ITEM_CODE 
            ORDER BY DWF_SALES_INVOICE_DATE 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),0) AS cum_sales_prev,
        -- 累计到当前笔的总销量
        SUM(DWF_SALES_SALES_QTY) OVER(
            PARTITION BY DWF_SALES_ITEM_CODE 
            ORDER BY DWF_SALES_INVOICE_DATE 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cum_sales_curr
    FROM DWF_SALES
    WHERE DWF_SALES_SALES_QTY > 0
),
InventoryWithCum AS (
    -- 计算按入库时间排序的库存累计区间，注意要把varchar类型的入库日期转成日期类型
    SELECT 
        DWF_INVENTORY_ITEM_CD,
        CAST(DWF_INVENTORY_DELIVERY_DATE AS DATE) AS DWF_INVENTORY_DELIVERY_DATE,
        DWF_INVENTORY_NUMBER_DESC,
        DWF_INVENTORY_QUANTITY,
        -- 累计到上一个批次的总库存
        COALESCE(SUM(DWF_INVENTORY_QUANTITY) OVER(
            PARTITION BY DWF_INVENTORY_ITEM_CD 
            ORDER BY CAST(DWF_INVENTORY_DELIVERY_DATE AS DATE)
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),0) AS cum_inv_prev,
        -- 累计到当前批次的总库存
        SUM(DWF_INVENTORY_QUANTITY) OVER(
            PARTITION BY DWF_INVENTORY_ITEM_CD 
            ORDER BY CAST(DWF_INVENTORY_DELIVERY_DATE AS DATE)
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cum_inv_curr
    FROM DWF_INVENTORY
)
SELECT 
    s.DWF_SALES_INVOICE_NUM,
    s.DWF_SALES_INVOICE_DATE,
    i.DWF_INVENTORY_NUMBER_DESC,
    i.DWF_INVENTORY_DELIVERY_DATE,
    s.DWF_SALES_ITEM_CODE,
    s.DWF_SALES_SALES_QTY,
    -- 计算扣减后当前批次剩余库存
    i.cum_inv_curr - s.cum_sales_curr AS DWF_INVENTORY_RUNNING_BALANCE_QTY
FROM SalesWithCum s
INNER JOIN InventoryWithCum i 
    ON s.DWF_SALES_ITEM_CODE = i.DWF_INVENTORY_ITEM_CD
    -- 仅匹配入库时间早于销售时间的批次
    AND i.DWF_INVENTORY_DELIVERY_DATE < s.DWF_SALES_INVOICE_DATE
    -- 销售累计区间落在当前库存的累计区间内，即说明当前销售扣减该批次库存
    AND s.cum_sales_prev < i.cum_inv_curr
    AND s.cum_sales_curr > i.cum_inv_prev
ORDER BY s.DWF_SALES_INVOICE_DATE;

效果说明 #

你的测试数据中5笔销售的累计销量分别为30、380、480、484、584，全部落在第一个批次（13763001924657）的库存区间0-5000内，因此所有销售都会匹配到该批次，直到累计销量超过5000后，才会自动匹配下一个批次13763002028961，完全符合FIFO单批次用尽后才切换的需求。

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