求助：基于周转率与到货的SQL Server每日库存预测实现

我正在基于SQL Server开发库存管理系统，包含Products和Orders两张表：

• Products：存储产品信息，包括当前库存、周转率
• Orders：记录到货信息，包括到货日期、到货数量

需要编写SQL查询，计算每个产品未来365天的每日库存，要求：

• 考虑产品周转率和到货情况
• 库存数值不能低于0

表结构及示例数据 #

Products表 #

Orders表 #

预期输出 #

生成包含以下列的每日库存报表：

• Date：未来365天的每日日期
• ID_PRODUCT：产品唯一标识
• Inventory：当日预期库存（≥0）

以起始日期2023-09-13为例，部分输出示例：

已尝试方案 #

尝试过递归CTE生成日期序列，结合LAG函数计算库存，但未得到理想结果，最后尝试的脚本如下：

WITH CTE_Calendar AS (
    SELECT TOP 365
        [date] = CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2023-09-13') AS DATE)
    FROM
        sys.objects
)
SELECT
    [Date]                =  cal.date
  , [ID_PRODUCT]          =  prod.ID_PRODUCT
  , [turnover_rate]       =  prod.turnover_rate
  , [delivery_amount]     =  ISNULL(ord.amount, 0)
  , [delta]               =  ISNULL(ord.amount, 0) - prod.turnover_rate
  , [inventory_at_date]   =  CASE
                                WHEN LAG(??, 1, prod.current_inventory) 
                                     OVER (PARTITION BY prod.ID_PRODUCT ORDER BY cal.date) + ISNULL(ord.amount, 0) - prod.turnover_rate < 0
                                  THEN 0
                                ELSE LAG(??, 1, prod.current_inventory) 
                                     OVER (PARTITION BY prod.ID_PRODUCT ORDER BY cal.date) + ISNULL(ord.amount, 0) - prod.turnover_rate
                              END

FROM CTE_Calendar AS cal

    CROSS JOIN dbo.Products AS prod
    
    LEFT JOIN dbo.Orders AS ord
        ON ord.delivery_date = cal.date
        AND ord.ID_PRODUCT = prod.ID_PRODUCT

问题在于无法正确计算每日库存，且难以确保库存不低于0。

解决方案 #

要实现每日库存的累计计算，递归CTE是更合适的选择（因为每日库存依赖前一日结果，LAG函数无法处理累计的依赖关系）。以下是完整的SQL脚本：

-- 定义起始日期和天数
DECLARE @StartDate DATE = '2023-09-13';
DECLARE @Days INT = 365;

WITH Calendar AS (
    -- 生成日期序列
    SELECT @StartDate AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM Calendar
    WHERE [Date] < DATEADD(DAY, @Days - 1, @StartDate)
),
ProductDailyData AS (
    -- 关联产品、日期和当日到货
    SELECT
        c.[Date],
        p.ID_PRODUCT,
        p.current_inventory,
        p.turnover_rate,
        ISNULL(o.amount, 0) AS delivery_amount
    FROM Calendar c
    CROSS JOIN Products p
    LEFT JOIN Orders o 
        ON o.ID_PRODUCT = p.ID_PRODUCT 
        AND o.delivery_date = c.[Date]
),
RecursiveInventory AS (
    -- 递归计算每日库存
    SELECT
        [Date],
        ID_PRODUCT,
        turnover_rate,
        delivery_amount,
        -- 第一天库存：初始库存 + 到货 - 周转率，不低于0
        CASE 
            WHEN current_inventory + delivery_amount - turnover_rate < 0 
            THEN 0 
            ELSE current_inventory + delivery_amount - turnover_rate 
        END AS Inventory
    FROM ProductDailyData
    WHERE [Date] = @StartDate

    UNION ALL

    SELECT
        pdd.[Date],
        pdd.ID_PRODUCT,
        pdd.turnover_rate,
        pdd.delivery_amount,
        -- 后续日期：前一日库存 + 到货 - 周转率，不低于0
        CASE 
            WHEN ri.Inventory + pdd.delivery_amount - pdd.turnover_rate < 0 
            THEN 0 
            ELSE ri.Inventory + pdd.delivery_amount - pdd.turnover_rate 
        END AS Inventory
    FROM ProductDailyData pdd
    INNER JOIN RecursiveInventory ri 
        ON pdd.ID_PRODUCT = ri.ID_PRODUCT 
        AND pdd.[Date] = DATEADD(DAY, 1, ri.[Date])
)
-- 输出最终结果
SELECT
    [Date],
    ID_PRODUCT,
    Inventory
FROM RecursiveInventory
ORDER BY ID_PRODUCT, [Date]
OPTION (MAXRECURSION 0); -- 关闭递归次数限制，因为需要365次递归

脚本说明 #

1. Calendar CTE：用递归方式生成未来365天的日期序列，比依赖系统表更可靠。
2. ProductDailyData CTE：将每个产品与每一天关联，并获取当日的到货数量。
3. RecursiveInventory CTE：
   • 锚点成员：计算第一天的库存（初始库存 + 到货 - 周转率，确保结果≥0）。
   • 递归成员：基于前一日的库存，计算当日库存，同样确保结果不低于0。
4. 最终查询：输出要求的三列，按产品和日期排序。

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