使用shift与data.table计算库存滚动平均价值问题

问题描述 #

我需要用data.table处理库存的滚动价值计算，核心难点在于平均价格的规则：

• 采购记录：用前序库存均价和新采购价格做加权平均（权重是对应库存数量）
• 销售记录：保持之前的平均价格不变

示例数据和库存计算代码如下：

library(data.table)
ledger <- data.table(Date = c('2017-04-05','2017-06-12','2017-08-12','2017-10-27','2017-11-01'),
                 Op = c('Purchase','Sale','Purchase','Purchase','Sale'),
                 Prod = c('ProdA','ProdA','ProdA','ProdA','ProdA'),
                 Qty = c(27,-20,15,10,-22),
                 Prc = c(36.47,41.64,40.03,40.95,40.82))

# 库存数量的滚动计算没问题
ledger[, Stock := cumsum(Qty)]

但我尝试的均价计算代码因为自引用（需要用到刚计算出的前一行AvgPrice）无法运行：

# 这段代码无法正常工作
ledger[,AvgPrice := ifelse(Op == "Purchase",
                     (Prc * Qty + shift(AvgPrice,1,fill = 0)*shift(Stock,1,fill = 0))/ (Qty + shift(Stock,1,fill = 0)),
                     shift(AvgPrice,1,fill = 0))]

期望得到的结果是：

Date        Op          Prod    Qty  Prc    Stock   AvgPrice
1   05-04-17    Purchase    ProdA   27   36.47  27      36.47
2   12-06-17    Sale        ProdA   -20  41.64  7       36.47
3   12-08-17    Purchase    ProdA   15   40.03  22      38.90
4   27-10-17    Purchase    ProdA   10   40.95  32      39.54
5   01-11-17    Sale        ProdA   -22  40.82  10      39.54

注：这个规则是巴西股票资本利得计算的标准逻辑，求可行方案！

解决方案 #

这是典型的递推依赖场景，当前行的结果依赖前一行的计算值，data.table的常规向量化操作没法直接处理这种自引用。下面提供两种高效的实现方式：

方法1：用Reduce实现递推计算

Reduce可以逐行累积计算，非常适合这种需要连续依赖的场景：

# 先确保已经计算了Stock
ledger[, Stock := cumsum(Qty)]

# 定义递推逻辑的函数
calc_avg <- function(prev_avg, row) {
  if (row$Op == "Purchase") {
    # 采购时：加权平均 = (前序库存总价值 + 新采购总价值) / 新库存总量
    new_avg <- (prev_avg * (row$Stock - row$Qty) + row$Prc * row$Qty) / row$Stock
  } else {
    # 销售时：直接沿用前序均价
    new_avg <- prev_avg
  }
  return(new_avg)
}

# 应用递推，按产品分组（如果有多产品的话）
ledger[, AvgPrice := Reduce(calc_avg, 
                           split(.SD, seq_len(.N)), 
                           init = .SD[1, Prc], 
                           accumulate = TRUE)[-1], 
       by = Prod]

方法2：用set循环（大数据集更高效）

如果你的数据集很大，set循环的性能会更好，因为它直接修改内存中的数据，避免不必要的复制：

ledger[, Stock := cumsum(Qty)]
ledger[, AvgPrice := 0.0]

# 初始化第一行的均价
ledger[1, AvgPrice := Prc]

# 逐行循环计算
for (i in 2:nrow(ledger)) {
  if (ledger[i, Op] == "Purchase") {
    prev_stock <- ledger[i-1, Stock]
    prev_avg <- ledger[i-1, AvgPrice]
    ledger[i, AvgPrice := (prev_avg * prev_stock + Prc[i] * Qty[i]) / (prev_stock + Qty[i])]
  } else {
    ledger[i, AvgPrice := ledger[i-1, AvgPrice]]
  }
}

# 多产品版本（按Prod分组循环）
ledger[, {
  AvgPrice <- numeric(.N)
  AvgPrice[1] <- Prc[1]
  for (i in 2:.N) {
    AvgPrice[i] <- if (Op[i] == "Purchase") {
      (AvgPrice[i-1] * Stock[i-1] + Prc[i] * Qty[i]) / Stock[i]
    } else {
      AvgPrice[i-1]
    }
  }
  .(Date, Op, Prod, Qty, Prc, Stock, AvgPrice)
}, by = Prod]

验证结果 #

运行代码后，用下面的命令格式化输出可以得到你期望的结果：

ledger[, .(Date = format(as.Date(Date), "%d-%m-%y"), Op, Prod, Qty, Prc, Stock, AvgPrice = round(AvgPrice, 2))]

输出：

Date       Op  Prod Qty   Prc Stock AvgPrice
1: 05-04-17 Purchase ProdA  27 36.47    27    36.47
2: 12-06-17     Sale ProdA -20 41.64     7    36.47
3: 12-08-17 Purchase ProdA  15 40.03    22    38.90
4: 27-10-17 Purchase ProdA  10 40.95    32    39.54
5: 01-11-17     Sale ProdA -22 40.82    10    39.54

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