SQL递归实现库存与订单动态计算：基于历史行数据

阿华AIGC实验室

2026-6-14

解决库存补货递归计算的SQL实现问题

字段说明

week_start：日期类型，每周周一（当年的周起始日）
delivery_time：配送所需时长（天数）
safety_stock：仓库最低维持库存值
stock：初始库存数量
order_amount：每次下单的固定数量
Needed：当周所需物料数量

订单规则

当当前库存(stock) - 当周需求(Needed) 低于**安全库存(safety_stock)**时，下单order_amount数量的物料；下单后，该订单量需添加到配送到达对应的week_start行的stock字段中。

示例数据

周起始日	配送时长	安全库存	库存	下单量	实际下单量	当周需求
06-01-2025	14	10	8	20	20	5
13-01-2025	14	10	3	20	20	2
20-01-2025	14	10	21	20	0	5

问题分析

尝试用递归CTE实现时遇到两个核心难点：

无法精准定位需要参考的历史行来判断是否下单
无法将下单量正确追加到对应到货周的库存中，且循环判断后续下单逻辑

初始代码仅实现了基础的周关联，未处理库存更新和下单逻辑的递归传递。

解决方案：递归CTE实现完整逻辑

以下是适配逻辑的递归CTE代码（以SQL Server语法为例，其他数据库可调整日期函数）：

WITH WeeklyInventory AS (
    -- 基准CASE：处理第一周数据，计算剩余库存和是否下单
    SELECT 
        week_start,
        delivery_time,
        safety_stock,
        -- 第一周扣除需求后的库存
        stock - Needed AS remaining_stock,
        order_amount,
        -- 判断是否下单：剩余库存低于安全库存则下单order_amount，否则0
        CASE WHEN (stock - Needed) < safety_stock THEN order_amount ELSE 0 END AS actual_order,
        Needed,
        -- 计算到货周：当前周+配送时长（14天=2周）
        DATEADD(week, delivery_time / 7, week_start) AS delivery_week
    FROM original_table
    WHERE week_start = (SELECT MIN(week_start) FROM original_table)

    UNION ALL

    -- 递归CASE：逐周处理，累加之前的到货订单，计算当前库存和下单逻辑
    SELECT 
        ot.week_start,
        ot.delivery_time,
        ot.safety_stock,
        -- 当前初始库存 + 所有历史订单中本周到货的数量 - 当周需求
        ot.stock + COALESCE(SUM(wi_delivery.actual_order), 0) - ot.Needed AS remaining_stock,
        ot.order_amount,
        -- 判断是否下单：剩余库存低于安全库存则下单
        CASE WHEN (ot.stock + COALESCE(SUM(wi_delivery.actual_order), 0) - ot.Needed) < ot.safety_stock 
             THEN ot.order_amount 
             ELSE 0 
        END AS actual_order,
        ot.Needed,
        DATEADD(week, ot.delivery_time / 7, ot.week_start) AS delivery_week
    FROM original_table ot
    INNER JOIN WeeklyInventory wi 
        ON ot.week_start = DATEADD(week, 1, wi.week_start)
    -- 关联所有到货周等于当前周的历史订单
    LEFT JOIN WeeklyInventory wi_delivery 
        ON wi_delivery.delivery_week = ot.week_start
    WHERE ot.week_start > (SELECT MIN(week_start) FROM original_table)
    GROUP BY ot.week_start, ot.delivery_time, ot.safety_stock, ot.stock, ot.order_amount, ot.Needed
)
-- 最终输出：包含所有周的计算结果，可按需调整字段
SELECT 
    week_start AS 周起始日,
    delivery_time AS 配送时长,
    safety_stock AS 安全库存,
    -- 显示当前周初始库存（含到货）
    stock + COALESCE((SELECT SUM(actual_order) FROM WeeklyInventory WHERE delivery_week = wi.week_start), 0) AS 当前库存,
    order_amount AS 下单量,
    actual_order AS 实际下单量,
    Needed AS 当周需求,
    remaining_stock AS 当周剩余库存,
    delivery_week AS 到货周
FROM WeeklyInventory wi
ORDER BY week_start;

代码说明

基准CASE：处理第一周数据，直接计算扣除需求后的剩余库存，判断是否需要下单，并计算该订单的到货周。
递归CASE：
- 逐周关联上一周的计算结果，确保按时间顺序处理
- 通过LEFT JOIN关联所有历史订单中到货周等于当前周的记录，累加这些订单量到当前库存中
- 重新计算当前库存扣除需求后的剩余量，判断是否需要下单
最终输出：展示每一周的初始库存（含到货）、实际下单量、剩余库存等关键数据，便于验证逻辑。