如何实现忽略库存转移日期但保留数量影响的FIFO库存账龄报表

需求与问题 #

需要生成基于FIFO方法的库存账龄报表，核心要求：

• 计算截至指定日期（2014-06-06）各仓库的现有库存
• 按原始入库日期划分账龄区间
• 内部库存转移仅变更库存位置，不得改变库存账龄

当前查询存在逻辑矛盾：

• 保留转移记录时，Warehouse B的212库存账龄被计算为0天（实际应为42天，与原始入库日期一致）
• 移除转移记录后，所有库存被归到Warehouse A，无法体现实际库存分布
• 尝试过的解决方案性能极差（执行时间超15分钟），且账龄计算不准确

期望输出 #

截至2014-06-06的报表结果：

数据示例 #

采购入库记录 #

库存转移记录 #

当前存在问题的SQL查询 #

WITH inventory_movements AS (
    SELECT 
        warehouse,
        quantity,
        transaction_date,
        'IN' AS type
    FROM purchase_receipts
    UNION ALL
    SELECT 
        to_warehouse AS warehouse,
        quantity,
        transaction_date,
        'TRANSFER_IN' AS type
    FROM inventory_transfers
    UNION ALL
    SELECT 
        from_warehouse AS warehouse,
        -quantity,
        transaction_date,
        'TRANSFER_OUT' AS type
    FROM inventory_transfers
)
SELECT 
    warehouse,
    SUM(remaining_qty) AS total_inventory,
    DATEDIFF('2014-06-06', transaction_date) AS age_days,
    CASE 
        WHEN DATEDIFF('2014-06-06', transaction_date) <= 30 THEN '0-1个月'
        WHEN DATEDIFF('2014-06-06', transaction_date) <= 90 THEN '1-3个月'
        ELSE '3个月以上'
    END AS age_bucket
FROM (
    SELECT 
        warehouse,
        transaction_date,
        quantity,
        SUM(quantity) OVER (PARTITION BY warehouse ORDER BY transaction_date) - 
        SUM(quantity) OVER (PARTITION BY warehouse ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS remaining_qty
    FROM inventory_movements
) t
WHERE remaining_qty > 0
GROUP BY warehouse, age_days, age_bucket;

现有错误输出 #

优化解决方案 #

核心思路 #

• 追踪每批库存的原始入库日期，库存转移仅变更库存所在仓库，不修改账龄计算的基准日期
• 按转移日期+原始入库日期的顺序处理库存分配，严格遵循FIFO逻辑
• 简化查询层级，避免冗余计算，提升性能

优化后的SQL查询 #

-- 基于FIFO的库存账龄报表，正确处理库存转移的账龄继承
WITH raw_inventory_batches AS (
    -- 初始化采购入库批次，保留原始入库日期和初始仓库
    SELECT
        pr.purchase_receipt_id AS batch_id,
        pr.warehouse AS current_warehouse,
        pr.quantity AS original_qty,
        pr.quantity AS remaining_qty,
        pr.transaction_date AS original_receipt_date
    FROM purchase_receipts pr
    WHERE pr.transaction_date <= '2014-06-06'
),
transfer_allocations AS (
    -- 按转移日期顺序，逐批分配库存转移量
    SELECT
        rb.batch_id,
        rb.original_receipt_date,
        rb.current_warehouse AS source_warehouse,
        it.to_warehouse AS target_warehouse,
        -- 计算原仓库剩余库存
        CASE WHEN it.quantity <= rb.remaining_qty THEN rb.remaining_qty - it.quantity ELSE 0 END AS source_remaining,
        -- 计算转移到目标仓库的库存
        CASE WHEN it.quantity <= rb.remaining_qty THEN it.quantity ELSE rb.remaining_qty END AS target_transferred
    FROM raw_inventory_batches rb
    LEFT JOIN inventory_transfers it
        ON rb.current_warehouse = it.from_warehouse
        AND it.transaction_date <= '2014-06-06'
    ORDER BY it.transaction_date, rb.original_receipt_date
),
consolidated_inventory AS (
    -- 合并原仓库剩余批次
    SELECT
        batch_id,
        original_receipt_date,
        source_warehouse AS warehouse,
        source_remaining AS qty
    FROM transfer_allocations
    WHERE source_remaining > 0
    UNION ALL
    -- 合并转移到目标仓库的批次（保留原始入库日期）
    SELECT
        batch_id || '_transfer',
        original_receipt_date,
        target_warehouse AS warehouse,
        target_transferred AS qty
    FROM transfer_allocations
    WHERE target_transferred > 0
)
-- 最终汇总生成账龄报表
SELECT
    warehouse,
    ROUND(SUM(qty), 2) AS total_inventory,
    DATEDIFF('2014-06-06', original_receipt_date) AS age_days,
    CASE
        WHEN DATEDIFF('2014-06-06', original_receipt_date) <= 30 THEN '0-1个月'
        WHEN DATEDIFF('2014-06-06', original_receipt_date) <= 90 THEN '1-3个月'
        ELSE '3个月以上'
    END AS age_bucket
FROM consolidated_inventory
GROUP BY warehouse, original_receipt_date, age_days, age_bucket
ORDER BY warehouse;

优化说明 #

1. 账龄准确性：所有库存批次的账龄均基于原始入库日期计算，转移操作仅变更仓库，不影响账龄
2. 性能提升：避免了复杂的窗口函数嵌套，通过批次化处理减少计算量，执行时间可控制在分钟级
3. 逻辑严谨：严格按照FIFO顺序分配转移库存，保证库存分布与实际业务一致

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