PostgreSQL先进先出(FIFO)库存计算SQL实现求助

示例表结构与测试数据 #

先定义典型的库存交易表，并插入测试数据（模拟入库、出库的混合场景）：

CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL, -- 产品ID，支持多产品FIFO计算
    transaction_date TIMESTAMP NOT NULL, -- 交易时间
    quantity NUMERIC(10,2) NOT NULL -- 库存数量：正为入库，负为出库
);

-- 测试数据：产品1的入库/出库记录
INSERT INTO inventory (product_id, transaction_date, quantity) VALUES
(1, '2024-01-01', 100),  -- 入库100
(1, '2024-01-05', 150),  -- 入库150
(1, '2024-01-10', -80),  -- 出库80
(1, '2024-01-15', -120), -- 出库120
(1, '2024-01-20', 200),  -- 入库200
(1, '2024-01-25', -180); -- 出库180

完整FIFO计算SQL #

以下SQL通过CTE串联逻辑，实现出库按顺序消耗最早入库的库存，并生成包含消耗明细、剩余库存的结果表：

WITH all_transactions AS (
    -- 第一步：整理所有交易，按产品+时间排序，标记交易类型
    SELECT
        id,
        product_id,
        transaction_date,
        quantity,
        CASE WHEN quantity > 0 THEN 'IN' ELSE 'OUT' END AS trans_type,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY transaction_date, id) AS trans_seq
    FROM inventory
),
inbound_transactions AS (
    -- 第二步：处理入库交易，计算累计入库量（到当前批次的总可用库存）
    SELECT
        product_id,
        id AS inbound_id,
        transaction_date AS inbound_date,
        quantity AS inbound_qty,
        trans_seq,
        SUM(quantity) OVER (PARTITION BY product_id ORDER BY trans_seq) AS cumulative_inbound
    FROM all_transactions
    WHERE trans_type = 'IN'
),
outbound_transactions AS (
    -- 第三步：处理出库交易，计算累计出库需求区间
    SELECT
        product_id,
        id AS outbound_id,
        transaction_date AS outbound_date,
        ABS(quantity) AS outbound_qty,
        trans_seq,
        -- 当前出库为止的累计需求总量
        SUM(ABS(quantity)) OVER (PARTITION BY product_id ORDER BY trans_seq) AS cumulative_outbound,
        -- 上一次出库后的累计需求总量（用于确定当前出库的需求起始点）
        LAG(SUM(ABS(quantity)) OVER (PARTITION BY product_id ORDER BY trans_seq), 1, 0) 
            OVER (PARTITION BY product_id ORDER BY trans_seq) AS prev_cumulative_outbound
    FROM all_transactions
    WHERE trans_type = 'OUT'
),
match_inbound_outbound AS (
    -- 第四步：核心匹配逻辑，找出每个出库对应的入库批次及消耗数量
    SELECT
        ot.product_id,
        ot.outbound_id,
        ot.outbound_date,
        ot.outbound_qty,
        it.inbound_id,
        it.inbound_date,
        it.inbound_qty,
        -- 计算当前出库从该入库批次消耗的数量：取区间重叠部分的数值
        GREATEST(
            0,
            LEAST(it.cumulative_inbound, ot.cumulative_outbound) 
            - GREATEST(it.cumulative_inbound - it.inbound_qty, ot.prev_cumulative_outbound)
        ) AS consumed_qty
    FROM outbound_transactions ot
    JOIN inbound_transactions it 
        ON ot.product_id = it.product_id
        -- 入库批次的累计量大于上一次出库累计（说明该入库在当前出库的需求范围内）
        AND it.cumulative_inbound > ot.prev_cumulative_outbound
        -- 入库批次的起始累计量小于当前出库累计（说明该入库有部分/全部被当前出库消耗）
        AND (it.cumulative_inbound - it.inbound_qty) < ot.cumulative_outbound
),
final_result AS (
    -- 第五步：合并出库消耗明细与入库剩余库存数据
    -- 出库交易的消耗明细
    SELECT
        product_id,
        'OUT' AS transaction_type,
        outbound_id AS transaction_id,
        outbound_date AS transaction_date,
        -outbound_qty AS quantity,
        inbound_id AS source_transaction_id,
        inbound_date AS source_transaction_date,
        consumed_qty AS consumed_quantity,
        NULL AS remaining_quantity
    FROM match_inbound_outbound
    UNION ALL
    -- 入库交易的剩余库存计算
    SELECT
        it.product_id,
        'IN' AS transaction_type,
        it.inbound_id AS transaction_id,
        it.inbound_date AS transaction_date,
        it.inbound_qty AS quantity,
        NULL AS source_transaction_id,
        NULL AS source_transaction_date,
        COALESCE(SUM(mio.consumed_qty), 0) AS consumed_quantity,
        it.inbound_qty - COALESCE(SUM(mio.consumed_qty), 0) AS remaining_quantity
    FROM inbound_transactions it
    LEFT JOIN match_inbound_outbound mio 
        ON it.product_id = mio.product_id AND it.inbound_id = mio.inbound_id
    GROUP BY it.product_id, it.inbound_id, it.inbound_date, it.inbound_qty
)
-- 最终结果按产品、交易时间排序
SELECT * FROM final_result ORDER BY product_id, transaction_date, transaction_id;

逻辑说明 #

• all_transactions：统一整理所有交易，按产品和交易时间排序，确保FIFO的顺序正确性，同时标记交易是入库还是出库。
• inbound_transactions：单独提取入库记录，计算累计入库量，用于后续判断入库批次的可用库存区间。
• outbound_transactions：单独提取出库记录，将负数量转为正需求，计算累计出库的区间（当前出库的需求是从prev_cumulative_outbound到cumulative_outbound）。
• match_inbound_outbound：核心关联逻辑，通过累计值的区间重叠，找到每个出库对应的入库批次，计算该批次被当前出库消耗的具体数量。
• final_result：合并出库的消耗明细和入库的剩余库存数据，生成完整的FIFO计算结果。

结果格式说明 #

最终结果包含以下字段：

• product_id：产品ID
• transaction_type：交易类型（IN/OUT）
• transaction_id：原交易记录ID
• transaction_date：交易时间
• quantity：原交易数量（出库为负）
• source_transaction_id：出库对应的入库批次ID（入库记录为NULL）
• source_transaction_date：出库对应的入库批次时间（入库记录为NULL）
• consumed_quantity：该交易被消耗的数量（入库为被出库消耗的总量，出库为消耗对应入库的数量）
• remaining_quantity：入库批次的剩余库存（出库记录为NULL）

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