递归CTE按ID顺序计算加权平均成本时如何获取同仓库非上一行值

基础规则说明 #

• 计算目标：通过递归CTE计算多仓库维度下的产品加权平均成本，必须严格遵循代表业务操作发生顺序的ID字段顺序执行递归，不能跨ID打乱操作顺序
• 原始数据规则：
  • ID=1、ID=2为各仓库初始库存行，Movement字段取值为N/A，AVG_Weighted_Price直接保留原始取值，其中ID=1作为递归锚点成员，ID=2通过硬编码逻辑保留原值
  • 其余行AVG_Weighted_Price初始值为0，是本次递归需要计算的目标字段
  • 核心计算逻辑：当前行AVG_Weighted_Price =（当前行Movement * 同仓库下最近一次已计算得到的AVG_Weighted_Price）/ 当前行Total_Quantity
  • 注意点：同仓库最近一次计算结果对应的行，不一定是全局ID顺序的上一行
• 性能要求：原始数据覆盖数十个仓库、共数百万行记录，WHILE循环逐行处理的方案性能无法满足业务要求

已验证无效的方案 #

方案1：按仓库维度独立分组递归 #

该方案不遵循全局ID顺序，每个仓库单独按仓内操作顺序递归计算，实现代码如下：

DROP TABLE IF EXISTS #RS

;WITH cte
AS
(
SELECT *
FROM #Sample_Table
WHERE Warehouse_Order = 1

UNION ALL

SELECT b.Warehouse
      ,b.Movement
      ,b.Total_Quantity
      ,CASE WHEN b.Warehouse_Order = 1 THEN b.AVG_Weighted_Price
            ELSE (b.Movement * a.AVG_Weighted_Price) / b.Total_Quantity END AS AVG_Weighted_Price
      ,b.ID
      ,b.Warehouse_Order
FROM cte a
INNER JOIN #Sample_Table b
ON b.Warehouse = a.Warehouse AND b.Warehouse_Order = a.Warehouse_Order + 1 
)

SELECT *
INTO #RS
FROM cte

存在问题：不同仓库的AVG_Weighted_Price存在跨库影响，该方案未按全局ID顺序执行计算，结果不符合业务规则。

方案2：按ID顺序递归+递归内调用LAG函数取同仓库最新值 #

调整锚点为ID=1，递归关联条件为ID逐行+1，在递归成员中使用LAG窗口函数，按Warehouse分区、ID排序获取同仓库下最新的AVG_Weighted_Price参与计算，实现代码如下：

DROP TABLE IF EXISTS #RS

;WITH cte
AS
(
SELECT *
FROM #Sample_Table
WHERE ID = 1

UNION ALL

SELECT b.Warehouse
      ,b.Movement
      ,b.Total_Quantity
      ,CASE WHEN b.Warehouse_Order =1 THEN b.AVG_Weighted_Price
            ELSE (b.Movement * LAG(b.AVG_Weighted_Price) OVER (PARTITION BY b.Warehouse ORDER BY b.ID)) / b.Total_Quantity END AS AVG_Weighted_Price
      ,b.ID
      ,b.Warehouse_Order
FROM cte a
INNER JOIN #Sample_Table b
ON b.ID = a.ID + 1
)

SELECT *
INTO #RS
FROM cte

存在问题：递归CTE中无法正常使用LAG窗口函数，函数返回值始终为NULL，最终结果存在大量空值，不符合预期。

计算逻辑参考 #

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