如何用MySQL 8的FIFO方法计算含退换货的产品库存价值

一、基础FIFO库存计价需求 #

初始数据表 #

预期输出 #

手动计算逻辑 #

产品1

Purchase 10 qty at price 100 on 2024-08-01 so that stock value is 1000
after 2024-08-01, I sold 7 qty at 120 so now my stock value will be 300 as the purchase price was 100.
On 2024-08-09 I purchased another 10 qty at 80.
Now the final stock value for me is:
3  qty @ 100 = 300 (left from a previous purchase)
10 qty @ 80  = 800 
------------------- 
13 qty      = 1100 stock value

产品2

Purchase 25 qty at price 80 
Sell     20 qty at 
---------------------------
          5 qty remaining from first purchase at 80 price
Purchase 20 qty at price 90
---------------------------
   Total 25 qty (5 qty @ 80 price and 20 qty @ 90 price)
Sell     20 qty (using fifo method 5 deduct from first purchase and 15 deduct from second purchase)
---------------------------
Remaining 5 qty @ 90 price 
So, the stock value is 5 * 90 = 450

产品3

Purchase  40 qty at price 50
Purchase  20 qty at price 40
Purchase  20 qty at price 20
----------------------------
-         50 qty (sell)
----------------------------
Remaining 30 qty (using FIFO 10 qty left at 40 price and 20 left at 20 price
----------------------------
-         20 qty (sell)
----------------------------
Remaining 10 qty (using FIFO 10 qty left at 20 price)
So, stock value is 10 qty * 20 price = 200

二、新增退货场景的库存计价需求 #

规则说明 #

新增SALE RETURN、PURCHASE RETURN类型记录，需遵循：

• 销售退货：按最后售出商品优先冲销（LIFO逻辑）
• 采购退货：按最后采购商品优先冲销（LIFO逻辑）

预期输出 #

手动计算逻辑 #

产品1

Purchase 10 qty at a price 100 on 2024-08-01 so that stock value is 1000
after 2024-08-01, I sold 7 qty at 120 so now my stock value will be 300 as the purchase price was 100.
On 2024-08-09 I purchased another 10 qty at 80.
Now the final stock value for me is:
3  qty @ 100 = 300 (left from a previous purchase)
10 qty @ 80  = 800 
------------------- 
13 qty      = 1100 stock value

产品2

Purchase 25 qty at price 80 
Sell     20 qty at 
---------------------------
          5 qty remaining from first purchase at 80 price
Purchase 20 qty at price 90
---------------------------
   Total 25 qty (5 qty @ 80 price and 20 qty @ 90 price)
Sell     20 qty (using fifo method 5 deduct from first purchase and 15 deduct from second purchase)
---------------------------
Remaining 5 qty @ 90 price 
Purchase Return 4 @ whatever price  (debit in stock)
---------------------------
Remaining 1 qty @ 90 price
So, the stock value is 1 * 90 = 90

产品3

Purchase  40 qty at price 50
Purchase  20 qty at price 40
Purchase  20 qty at price 20
----------------------------
-         50 qty (sell)
----------------------------
Remaining 30 qty (using FIFO 10 qty left at 40 price and 20 left at 20 price
----------------------------
-         20 qty (sell)
----------------------------
Remaining 10 qty (using FIFO 10 qty left at 20 price)
+         20 qty (sell return) (credit in stock)
----------------------------
Remaining 30 qty (using LIFO 20 qty @ 20 and 10 @ at 40)
So, the stock value is (20 * 20 = 400) +  (10 * 40 = 400) = 800

三、MySQL 8 实现方案 #

基础场景SQL实现 #

WITH product_operations AS (
    SELECT
        product_id,
        type,
        quantity,
        price,
        operation_date,
        CASE type
            WHEN 'PURCHASE' THEN quantity
            WHEN 'SALE' THEN -quantity
        END AS qty_change
    FROM inventory
),
purchase_batches AS (
    SELECT
        product_id,
        quantity,
        price,
        operation_date,
        SUM(quantity) OVER (PARTITION BY product_id ORDER BY operation_date) AS cumulative_purchase,
        SUM(ABS(qty_change)) OVER (PARTITION BY product_id) AS total_sales
    FROM product_operations
    WHERE type = 'PURCHASE'
),
batch_consumption AS (
    SELECT
        pb.product_id,
        pb.price,
        GREATEST(0, pb.cumulative_purchase - pb.total_sales) - 
        GREATEST(0, LAG(pb.cumulative_purchase, 1, 0) OVER (PARTITION BY pb.product_id ORDER BY pb.operation_date) - pb.total_sales) AS remaining_qty
    FROM purchase_batches pb
)
SELECT
    product_id,
    SUM(remaining_qty * price) AS `Stock Value`
FROM batch_consumption
GROUP BY product_id
ORDER BY product_id;

含退货场景的SQL实现 #

WITH product_operations AS (
    SELECT
        product_id,
        type,
        quantity,
        price,
        operation_date,
        CASE type
            WHEN 'PURCHASE' THEN quantity
            WHEN 'SALE' THEN -quantity
            WHEN 'PURCHASE RETURN' THEN -quantity
            WHEN 'SALE RETURN' THEN quantity
        END AS qty_change
    FROM inventory
),
-- 处理采购批次的累计和销售总数量
purchase_batches AS (
    SELECT
        product_id,
        quantity,
        price,
        operation_date,
        SUM(quantity) OVER (PARTITION BY product_id ORDER BY operation_date) AS cumulative_purchase,
        -- 计算总净销售（销售-销售退货）
        SUM(CASE WHEN type IN ('SALE', 'SALE RETURN') THEN -qty_change ELSE 0 END) OVER (PARTITION BY product_id) AS net_sales,
        -- 计算总采购退货
        SUM(CASE WHEN type = 'PURCHASE RETURN' THEN quantity ELSE 0 END) OVER (PARTITION BY product_id) AS total_purchase_returns
    FROM product_operations
    WHERE type = 'PURCHASE'
),
-- 处理销售和销售退货后的剩余采购批次
post_sale_batches AS (
    SELECT
        product_id,
        price,
        operation_date,
        cumulative_purchase,
        GREATEST(0, cumulative_purchase - net_sales) - 
        GREATEST(0, LAG(cumulative_purchase, 1, 0) OVER (PARTITION BY product_id ORDER BY operation_date) - net_sales) AS sale_remaining_qty
    FROM purchase_batches
),
-- 处理采购退货（按LIFO倒序冲销）
final_batches AS (
    SELECT
        product_id,
        price,
        operation_date,
        sale_remaining_qty,
        -- 倒序计算累计剩余数量，用于分配采购退货
        SUM(sale_remaining_qty) OVER (PARTITION BY product_id ORDER BY operation_date DESC) AS reverse_cumulative,
        total_purchase_returns
    FROM post_sale_batches
),
batch_final_qty AS (
    SELECT
        product_id,
        price,
        GREATEST(0, sale_remaining_qty - 
            GREATEST(0, reverse_cumulative - total_purchase_returns) + 
            GREATEST(0, LAG(reverse_cumulative, 1, 0) OVER (PARTITION BY product_id ORDER BY operation_date DESC) - total_purchase_returns)
        ) AS final_qty
    FROM final_batches
)
SELECT
    product_id,
    SUM(final_qty * price) AS `Stock Value`
FROM batch_final_qty
GROUP BY product_id
ORDER BY product_id;

表结构优化建议 #

1. 新增operation_id自增主键：确保操作顺序的唯一性，避免同一时间多条操作的排序歧义
2. 拆分价格字段：将采购的成本价存储为cost_price，销售/退货的交易价存储为transaction_price，避免字段混淆
3. 新增batch_id字段：为每批采购分配唯一批次号，可直接跟踪批次的消耗与剩余，简化FIFO逻辑的实现

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