如何在SQL中基于历史行条件动态计算补货量与期末库存？

因为你的需求涉及逐行动态依赖前序计算结果的逻辑，lag函数只能静态获取上一行的固定值，无法处理补货后库存重置的动态迭代场景，递归CTE是最适合的方案——它可以按周顺序逐行计算，每一步都基于上一周的期末库存判断是否需要补货，进而得到当前周的结果。

核心逻辑梳理 #

针对每个item + location_id组合，按row_number（周顺序）执行以下步骤：

1. 首周初始库存为pack_size
2. 判断当前初始库存是否能覆盖本周forecast：
   • 若初始库存 < forecast：补货pack_size（units_to_store = pack_size），用补货后的库存（pack_size）减去forecast得到期末库存
   • 若初始库存 >= forecast：不补货（units_to_store = 0），用当前初始库存减去forecast得到期末库存
3. 后续周的初始库存直接继承上一周的期末库存，重复步骤2

通用SQL实现 #

-- 递归CTE处理动态库存计算
WITH recursive_inventory AS (
    -- 锚点查询：处理首周（row_number=1）的初始数据
    SELECT
        item,
        location_id,
        forecast,
        week_begin,
        pack_size,
        row_number,
        -- 首周判断是否需要补货
        CASE WHEN pack_size < forecast THEN pack_size ELSE 0 END AS units_to_store,
        -- 首周期末库存计算
        CASE WHEN pack_size < forecast THEN pack_size - forecast ELSE pack_size - forecast END AS week_end_inventory
    FROM your_sales_forecast_table
    WHERE row_number = 1

    UNION ALL

    -- 递归查询：处理后续周的数据，依赖上一周的计算结果
    SELECT
        t.item,
        t.location_id,
        t.forecast,
        t.week_begin,
        t.pack_size,
        t.row_number,
        -- 根据上一周期末库存判断是否补货
        CASE WHEN r.week_end_inventory < t.forecast THEN t.pack_size ELSE 0 END AS units_to_store,
        -- 当前周期末库存计算
        CASE WHEN r.week_end_inventory < t.forecast THEN t.pack_size - t.forecast ELSE r.week_end_inventory - t.forecast END AS week_end_inventory
    FROM your_sales_forecast_table t
    INNER JOIN recursive_inventory r
        ON t.item = r.item
        AND t.location_id = r.location_id
        AND t.row_number = r.row_number + 1
)
-- 输出最终结果，按商品、门店、周顺序排序
SELECT * 
FROM recursive_inventory 
ORDER BY item, location_id, row_number;

注意事项 #

• 不同SQL方言的递归CTE写法略有差异：MySQL、PostgreSQL需要显式加RECURSIVE关键字（即WITH RECURSIVE ...），SQL Server、Oracle则不需要
• 确保your_sales_forecast_table是你的实际表名，且row_number在每个item + location_id组内是从1开始连续递增的（代表周的顺序）

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