基于实际库存的SQL Server SKU库存回溯计算需求

我来帮你搞定这个需求——完全不用Python，直接在SQL里就能实现！核心思路是用窗口函数按日期倒序计算累计的出入库差值，再结合当前库存倒推每日库存水平。

步骤1：准备当前库存变量 #

首先，你需要先获取目标SKU的当前实际库存，把它存到变量里：

DECLARE @CurrentStock INT;
-- 替换成你实际获取当前库存的查询，比如从库存表中读取
SET @CurrentStock = (SELECT ActualStock FROM YourInventoryTable WHERE SKU = '你的目标SKU');

步骤2：基础每日出入库统计（复用你的逻辑） #

用CTE先完成你原本的每日出入库交易数、数量统计，如果需要覆盖过去365天所有日期（包括无交易的日子），可以先生成日期序列再关联临时表：

DECLARE @StartDate DATE = DATEADD(DAY, -364, GETDATE()); -- 过去365天起始日
DECLARE @EndDate DATE = GETDATE();

WITH DateRange AS (
    -- 生成过去365天的完整日期序列
    SELECT @StartDate AS [DATE]
    UNION ALL
    SELECT DATEADD(DAY, 1, [DATE])
    FROM DateRange
    WHERE [DATE] < @EndDate
),
DailyMovements AS (
    -- 关联日期序列和你的#MOVIMENTS临时表，统计每日数据（无交易时为0）
    SELECT 
        DR.[DATE],
        ISNULL(SUM(CASE WHEN MOV='IN' THEN 1 ELSE 0 END), 0) AS TRANSACTION_IN,
        ISNULL(SUM(CASE WHEN MOV='OUT' THEN 1 ELSE 0 END), 0) AS TRANSACTION_OUT,
        ISNULL(SUM(CASE WHEN MOV='IN' THEN QTD ELSE 0 END), 0) AS SUM_IN,
        ISNULL(SUM(CASE WHEN MOV='OUT' THEN QTD ELSE 0 END), 0) AS SUM_OUT
    FROM DateRange DR
    LEFT JOIN #MOVIMENTS M ON DR.[DATE] = M.DT
    GROUP BY DR.[DATE]
)

步骤3：倒推计算每日库存 #

在上面的CTE基础上，用倒序窗口函数计算累计净出库，再推导每日库存：

SELECT 
    [DATE],
    TRANSACTION_IN,
    TRANSACTION_OUT,
    SUM_IN,
    SUM_OUT,
    -- 核心计算逻辑：从当前库存倒推当日库存
    @CurrentStock + 
    SUM(SUM_OUT - SUM_IN) OVER (ORDER BY [DATE] DESC) - 
    (SUM_OUT - SUM_IN) AS STOCK
FROM DailyMovements
ORDER BY [DATE]
OPTION (MAXRECURSION 365); -- 生成365天日期需要开启递归限制

逻辑解释 #

这个计算的核心是模拟Excel的倒推逻辑：

• SUM(SUM_OUT - SUM_IN) OVER (ORDER BY [DATE] DESC)：按日期从新到旧，计算从当前日期到所有未来日期的累计净出库（出库减入库）。
• 对于最新的日期，累计值就是当天的净出库，减去它之后就得到当前库存，正好匹配实际值。
• 对于更早的日期，累计值包含了当前日期和之后所有日期的净出库，减去当前日期的净出库后，剩下的就是之后所有日期的总净变化，加上当前库存就得到了当日的库存（相当于把之后所有的出库加回来、入库扣回去，倒推回当天的库存水平）。

如果不需要覆盖无交易的日期，直接去掉DateRange CTE，用你原本的GROUP BY DT即可。

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