基于FIFO规则的MariaDB每日库存及采购价格计算方案咨询

问题背景 #

需要基于先进先出（FIFO）规则计算不同产品的每日库存可用量、销量及对应采购成本，核心要求是旧批次库存售罄前不使用新采购批次的价格，以此准确核算利润。涉及三张表：

• products：存储在售产品基础信息
• product_sale_date：通过API同步的每日销量数据（部分日期-产品组合无记录）
• product_purchase_date：用户手动录入的采购订单数据（仅包含有实际采购的日期）

现有实现是带WHILE循环的存储过程，但存在缺陷：当单次销量大于单个采购批次数量时，逻辑失效，寻求更高效可靠的解决方案。

测试数据创建代码 #

-- 创建products表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

INSERT INTO products VALUES
(1, '产品A'),
(2, '产品B');

-- 创建product_purchase_date表（采购数据）
CREATE TABLE product_purchase_date (
    product_id INT,
    purchase_date DATE,
    purchase_quantity INT NOT NULL,
    purchase_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (product_id, purchase_date),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO product_purchase_date VALUES
(1, '2024-01-01', 100, 5.00),
(1, '2024-01-05', 150, 5.50),
(2, '2024-01-02', 200, 3.00);

-- 创建product_sale_date表（销量数据）
CREATE TABLE product_sale_date (
    product_id INT,
    sale_date DATE,
    sale_quantity INT NOT NULL,
    PRIMARY KEY (product_id, sale_date),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO product_sale_date VALUES
(1, '2024-01-03', 120),
(1, '2024-01-06', 130),
(2, '2024-01-04', 180);

预期结果 #

现有存储过程的问题 #

带WHILE循环的存储过程通常逐行处理销量和采购批次，当单次销量超过单个采购批次的库存时，无法自动拆分销量到多个旧批次，导致成本计算错误，且循环处理在数据量较大时性能低下。

优雅解决方案：基于窗口函数的FIFO计算 #

利用MariaDB的窗口函数（SUM() OVER()）和公共表表达式（CTE），可以高效实现FIFO逻辑，无需循环，支持单次跨批次销量的处理：

WITH date_range AS (
    -- 生成需要统计的日期范围（取采购和销量的最早/最晚日期）
    SELECT MIN(d) AS start_date, MAX(d) AS end_date
    FROM (
        SELECT purchase_date AS d FROM product_purchase_date
        UNION ALL
        SELECT sale_date AS d FROM product_sale_date
    ) AS all_dates
),
all_dates AS (
    -- 生成日期序列
    SELECT DATE_ADD(start_date, INTERVAL seq DAY) AS stat_date
    FROM date_range
    JOIN seq_0_to_365 ON seq <= DATEDIFF(end_date, start_date)
),
product_dates AS (
    -- 关联所有产品与日期，生成基础统计行
    SELECT p.product_id, p.product_name, ad.stat_date
    FROM products p
    CROSS JOIN all_dates ad
),
purchase_running AS (
    -- 计算每个产品的采购批次累计库存
    SELECT 
        product_id,
        purchase_date,
        purchase_quantity,
        purchase_price,
        SUM(purchase_quantity) OVER (PARTITION BY product_id ORDER BY purchase_date) AS running_purchase_qty
    FROM product_purchase_date
),
sale_running AS (
    -- 计算每个产品的每日累计销量
    SELECT 
        ps.product_id,
        ps.sale_date,
        ps.sale_quantity,
        SUM(ps.sale_quantity) OVER (PARTITION BY ps.product_id ORDER BY ps.sale_date) AS running_sale_qty
    FROM product_sale_date ps
),
daily_sale AS (
    -- 关联基础统计行与销量数据，填充无销量的日期为0
    SELECT 
        pd.product_id,
        pd.product_name,
        pd.stat_date,
        COALESCE(sr.sale_quantity, 0) AS sale_quantity,
        COALESCE(sr.running_sale_qty, 0) AS running_sale
    FROM product_dates pd
    LEFT JOIN sale_running sr ON pd.product_id = sr.product_id AND pd.stat_date = sr.sale_date
),
daily_purchase AS (
    -- 获取每个日期及之前的累计采购库存
    SELECT 
        pd.product_id,
        pd.stat_date,
        COALESCE(MAX(pr.running_purchase_qty), 0) AS running_purchase
    FROM product_dates pd
    LEFT JOIN purchase_running pr ON pd.product_id = pr.product_id AND pr.purchase_date <= pd.stat_date
    GROUP BY pd.product_id, pd.stat_date
),
daily_inventory AS (
    -- 计算每日可用库存
    SELECT 
        ds.product_id,
        ds.product_name,
        ds.stat_date,
        dp.running_purchase - ds.running_sale AS available_stock,
        ds.sale_quantity
    FROM daily_sale ds
    JOIN daily_purchase dp ON ds.product_id = dp.product_id AND ds.stat_date = dp.stat_date
),
fifo_cost AS (
    -- 计算每日销量对应的FIFO成本
    SELECT 
        di.product_id,
        di.product_name,
        di.stat_date,
        di.available_stock,
        di.sale_quantity,
        SUM(
            CASE 
                -- 销量覆盖当前采购批次的全部库存
                WHEN pr.running_purchase_qty <= di.running_sale THEN pr.purchase_quantity * pr.purchase_price
                -- 销量仅覆盖当前采购批次的部分库存
                WHEN pr.running_purchase_qty > di.running_sale AND pr.running_purchase_qty - pr.purchase_quantity < di.running_sale THEN (di.running_sale - (pr.running_purchase_qty - pr.purchase_quantity)) * pr.purchase_price
                -- 销量未触及当前采购批次
                ELSE 0
            END
        ) - COALESCE(
            SUM(
                CASE 
                    WHEN pr.running_purchase_qty <= di.running_sale - di.sale_quantity THEN pr.purchase_quantity * pr.purchase_price
                    WHEN pr.running_purchase_qty > di.running_sale - di.sale_quantity AND pr.running_purchase_qty - pr.purchase_quantity < di.running_sale - di.sale_quantity THEN ((di.running_sale - di.sale_quantity) - (pr.running_purchase_qty - pr.purchase_quantity)) * pr.purchase_price
                    ELSE 0
                END
            ), 0
        ) AS cost_of_goods_sold
    FROM daily_inventory di
    LEFT JOIN purchase_running pr ON di.product_id = pr.product_id
    GROUP BY di.product_id, di.product_name, di.stat_date, di.available_stock, di.sale_quantity
)
-- 最终结果
SELECT 
    product_id,
    product_name,
    stat_date AS date,
    GREATEST(available_stock, 0) AS available_stock, -- 确保库存不为负数
    sale_quantity,
    ROUND(cost_of_goods_sold, 2) AS cost_of_goods_sold
FROM fifo_cost
ORDER BY product_id, stat_date;

方案说明 #

1. 日期序列生成：通过seq_0_to_365生成需要统计的所有日期，确保无销量/采购的日期也能被覆盖。
2. 累计采购/销量计算：用窗口函数计算每个产品的采购累计库存和销量累计数量，为FIFO匹配提供基础。
3. 每日库存计算：通过累计采购减累计销量得到当日可用库存。
4. FIFO成本核算：对比累计销量与采购批次的累计库存，拆分销量到对应的采购批次，计算当日销售成本。

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