基于FIFO规则的PostgreSQL入库批次销售总价计算需求

问题概述 #

公司产品采购入库（正数量）与销售出库（负数量），销售价格手动调整且记录在表中。需按**先进先出（FIFO）**规则，计算每一批入库对应的总销售金额，剩余库存不纳入计算范围。

示例数据 #

先创建测试表并插入示例数据：

CREATE TABLE inventory_movements (
    movement_date DATE,
    quantity_change INTEGER,
    sale_price NUMERIC
);

INSERT INTO inventory_movements VALUES
('2024-01-01', 120, NULL),
('2024-01-02', -20, 100),
('2024-01-03', -40, 80),
('2024-01-04', 100, NULL),
('2024-01-05', -50, 100),
('2024-01-06', -40, 90),
('2024-01-07', 60, NULL),
('2024-01-08', -20, 100),
('2024-01-09', -40, 90),
('2024-01-10', -40, 80);

解决方案SQL #

WITH inventory_batches AS (
    -- 标记所有入库批次，分配唯一ID
    SELECT
        movement_date AS batch_date,
        quantity_change AS batch_quantity,
        ROW_NUMBER() OVER (ORDER BY movement_date) AS batch_id
    FROM inventory_movements
    WHERE quantity_change > 0
),
outgoing_movements AS (
    -- 整理出库记录，计算累计出库量（用于匹配FIFO消耗顺序）
    SELECT
        movement_date,
        ABS(quantity_change) AS outgoing_quantity,
        sale_price,
        SUM(ABS(quantity_change)) OVER (ORDER BY movement_date) AS cumulative_outgoing
    FROM inventory_movements
    WHERE quantity_change < 0
),
batch_cumulative AS (
    -- 计算入库批次的累计入库量，确定每个批次的库存覆盖范围
    SELECT
        batch_id,
        batch_date,
        batch_quantity,
        SUM(batch_quantity) OVER (ORDER BY batch_date) AS cumulative_incoming
    FROM inventory_batches
),
batch_outgoing_mapping AS (
    -- 关联出库与入库批次，计算每个批次被各出库单消耗的数量
    SELECT
        ob.batch_id,
        om.sale_price,
        -- 计算当前出库单对当前入库批次的实际消耗数量
        LEAST(
            ob.batch_quantity,
            om.cumulative_outgoing,
            ob.cumulative_incoming
        ) - GREATEST(
            ob.cumulative_incoming - ob.batch_quantity,
            om.cumulative_outgoing - om.outgoing_quantity
        ) AS consumed_quantity
    FROM batch_cumulative ob
    CROSS JOIN outgoing_movements om
    -- 筛选出库单与入库批次的覆盖范围
    WHERE om.cumulative_outgoing > ob.cumulative_incoming - ob.batch_quantity
      AND om.cumulative_outgoing - om.outgoing_quantity < ob.cumulative_incoming
      AND consumed_quantity > 0
)
-- 汇总每个入库批次的总销售金额
SELECT
    '入库批次' || batch_id AS batch_name,
    SUM(consumed_quantity * sale_price) AS total_sales_amount
FROM batch_outgoing_mapping
GROUP BY batch_id
ORDER BY batch_id;

逻辑说明 #

1. inventory_batches：提取所有入库记录，按日期排序并分配批次ID，明确每一批入库的基础信息。
2. outgoing_movements：将出库数量转为正数，计算累计出库量，标记出库的先后顺序与累计消耗规模。
3. batch_cumulative：计算每个入库批次的累计入库量，确定该批次库存的覆盖区间（比如第一批120的覆盖区间是1-120，第二批100是121-220）。
4. batch_outgoing_mapping：通过范围匹配，计算每个出库单对各入库批次的实际消耗数量，确保FIFO规则下先入库的库存优先被消耗。
5. 最终汇总：将每个批次的消耗数量乘以对应出库价格，得到该批次的总销售金额。

验证结果 #

执行上述SQL后，将得到与预期一致的结果：

batch_name   | total_sales_amount
-------------|-------------------
入库批次1     | 10300
入库批次2     | 9100
入库批次3     | 2400

剩余库存：总入库280 - 总出库250 = 30，未纳入计算范围，符合需求。

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