r - 使用滚动连接通过引用更新

Question

我如何更新 data.table通过引用使用滚动连接？
例子
在这里，我建立了一张广告表和一张销售表。

# commercials
commercials<-data.table(commercialID=c("C1","C2","C3","C4"), commercialDate=as.Date(c("2014-1-1","2014-4-1","2014-7-1","2014-9-15")))
commercials[, rollDate:=commercialDate] #Add a column, rollDate equal to commercialDate
setkey(commercials, "rollDate")

commercials
   commercialID commercialDate   rollDate
1:           C1     2014-01-01 2014-01-01
2:           C2     2014-04-01 2014-04-01
3:           C3     2014-07-01 2014-07-01
4:           C4     2014-09-15 2014-09-15


# sales (A sale before all commercials, a sale after commercial1 and a sale after commercial2)
sales <- data.table(saleID=c("S0", "S1", "S2"), saleDate=as.Date(c("2010-12-31", "2014-2-1", "2014-5-1")))
sales[, rollDate:=saleDate]
setkey(sales, "rollDate")

sales
   saleID   saleDate   rollDate
1:     S0 2010-12-31 2010-12-31
2:     S1 2014-02-01 2014-02-01
3:     S2 2014-05-01 2014-05-01

我想将每个商业广告与紧接其之前的销售和紧接其后的销售(包括与商业广告同一日期的销售)相关联。

尝试 1

commercials[sales, roll=-Inf, `:=`(saleIDBefore=saleID, saleDateBefore=saleDate)]
commercials[sales, roll=Inf, `:=`(saleIDAfter=saleID, saleDateAfter=saleDate)]

commercials
   commercialID commercialDate   rollDate saleIDBefore saleDateBefore saleIDAfter saleDateAfter
1:           C1     2014-01-01 2014-01-01           S0     2010-12-31          S1    2014-02-01
2:           C2     2014-04-01 2014-04-01           S1     2014-02-01          S2    2014-05-01
3:           C3     2014-07-01 2014-07-01           S2     2014-05-01          NA          <NA>
4:           C4     2014-09-15 2014-09-15           NA           <NA>          NA          <NA>

失败。注意 saleIDBefore 下的 NA。

正确(但次优)的解决方案

commercials <- sales[commercials, roll=Inf]
setnames(commercials, c("saleID", "saleDate"), c("saleIDBefore", "saleDateBefore"))
commercials <- sales[commercials, roll=-Inf]
setnames(commercials, c("saleID", "saleDate"), c("saleIDAfter", "saleDateAfter"))

commercials
   saleIDAfter saleDateAfter   rollDate saleIDBefore saleDateBefore commercialID commercialDate
1:          S1    2014-02-01 2014-01-01           S0     2010-12-31           C1     2014-01-01
2:          S2    2014-05-01 2014-04-01           S1     2014-02-01           C2     2014-04-01
3:          NA          <NA> 2014-07-01           S2     2014-05-01           C3     2014-07-01
4:          NA          <NA> 2014-09-15           S2     2014-05-01           C4     2014-09-15

但我想通过引用使用更新来做到这一点 :=如果它是可能的。

Answer 1

使用非对等连接:

commercials[sales, on = .(rollDate >= rollDate),
              `:=`(saleIDBefore=saleID, saleDateBefore=saleDate)][
            sales, on = .(rollDate <= rollDate), mult = 'last',
              `:=`(saleIDAfter=saleID, saleDateAfter=saleDate)][]
#   commercialID commercialDate   rollDate saleIDBefore saleDateBefore saleIDAfter saleDateAfter
#1:           C1     2014-01-01 2014-01-01           S0     2010-12-31          S1    2014-02-01
#2:           C2     2014-04-01 2014-04-01           S1     2014-02-01          S2    2014-05-01
#3:           C3     2014-07-01 2014-07-01           S2     2014-05-01          NA          <NA>
#4:           C4     2014-09-15 2014-09-15           S2     2014-05-01          NA          <NA>