Relative window current sum via data.table non-equi join

Question

I have a customerId, transactionDate, productId, purchaseQty dataset loaded into a data table. for each line, I want to calculate the amount and average value of buyQty for the previous 45 days.

        productId customerID transactionDate purchaseQty
 1:    870826    1186951      2016-03-28      162000
 2:    870826    1244216      2016-03-31        5000
 3:    870826    1244216      2016-04-08        6500
 4:    870826    1308671      2016-03-28      221367
 5:    870826    1308671      2016-03-29       83633
 6:    870826    1308671      2016-11-29       60500

I am looking for such a conclusion:

    productId customerID transactionDate purchaseQty    sumWindowPurchases
 1:    870826    1186951      2016-03-28      162000                162000
 2:    870826    1244216      2016-03-31        5000                  5000
 3:    870826    1244216      2016-04-08        6500                 11500
 4:    870826    1308671      2016-03-28      221367                221367
 5:    870826    1308671      2016-03-29       83633                305000
 6:    870826    1308671      2016-11-29       60500                 60500

Thus, sumWindowPurchases contains the Qty purchase amount for the customer / product for a 45-day window from the current transaction date. Once I have this work, casting the average and other calculations that I need should be trivial

I went back to my SQL roots and thought about myself:

select   DT.customerId, DT.transactionDate, DT.productId, sum(DT1.purchaseQty)
from     DT
         inner join DT as DT1 on 
             DT.customerId = DT1.customerId
             and DT.productId =  DT1.productId
             and DT1.transactionDate between DT.transactionDate and dateadd(day, -45, DT.transactionDate)

Trying to translate this to R using the data.dable syntax, I was hoping to do something like this:

DT1 <- DT #alias. have confirmed this is just a pointer
DT[DT1[DT1$transactionDate >= DT$transactionDate - 45],
   .(sum(DT1$purchaseQty)), 
   by = .(DT$customerId , DT$transactionDate ), 
   on = .(customerId , DT1$transactionDate <= DT$TransactionDate), 
   allow.cartesian = TRUE]

, 2 . "R-" . data.table self , Reduce?

, - 45- . 2 - data.table, , , .

, ,

DT[.(p = productId, c = customerID, t = transactionDate, start = transactionDate - 45),
        on = .(productId==p, customerID==c, transactionDate<=t, transactionDate>=start),
        allow.cartesian = TRUE, nomatch = 0]

:

   productId customerID transactionDate purchaseQty transactionDate.1
1:    870826    1186951      2016-03-28      162000        2016-02-12
2:    870826    1244216      2016-03-31        5000        2016-02-15
3:    870826    1244216      2016-04-08        5000        2016-02-23
4:    870826    1244216      2016-04-08        6500        2016-02-23
5:    870826    1308671      2016-03-28      221367        2016-02-12
6:    870826    1308671      2016-03-29      221367        2016-02-13
7:    870826    1308671      2016-03-29       83633        2016-02-13
8:    870826    1308671      2016-11-29       60500        2016-10-15

, . , /product/transactionDate.1, - . , , transactionDate.1

Answer 1

, . , .

, 2 transactionDate . , -, "on". , , , on

DT[.(p=productId, c=customerID, tmin=transactionDate - 45, tmax=transactionDate),
    on = .(productId==p, customerID==c, transactionDate<=tmax, transactionDate>=tmin),
    .(windowSum = sum(purchaseQty)), by = .EACHI, nomatch = 0]

Answer 2

, 45- ( ).

setDT(df)
df[, n:= 1:.N - findInterval(transactionDate - 45, transactionDate), by=.(customerID)]
df
#   productId customerID transactionDate purchaseQty n
#1:    870826    1186951      2016-03-28      162000 1
#2:    870826    1244216      2016-03-31        5000 1
#3:    870826    1244216      2016-04-08        6500 2
#4:    870826    1308671      2016-03-28      221367 1
#5:    870826    1308671      2016-03-29       83633 2
#6:    870826    1308671      2016-11-29       60500 1

purchaseQty n.

g <- function(x, window){
  b_pos <- seq_along(x) - window + 1  # begin positions
  cum <- cumsum(x)
  cum - cum[b_pos] + x[b_pos]
}
df[, sumWindowPurchases := g(purchaseQty, n),][,n:=NULL,]
df
#   productId customerID transactionDate purchaseQty sumWindowPurchases
#1:    870826    1186951      2016-03-28      162000             162000
#2:    870826    1244216      2016-03-31        5000               5000
#3:    870826    1244216      2016-04-08        6500              11500
#4:    870826    1308671      2016-03-28      221367             221367
#5:    870826    1308671      2016-03-29       83633             305000
#6:    870826    1308671      2016-11-29       60500              60500

---

structure(list(productId = c(870826L, 870826L, 870826L, 870826L, 
870826L, 870826L), customerID = c(1186951L, 1244216L, 1244216L, 
1308671L, 1308671L, 1308671L), transactionDate = structure(c(16888, 
16891, 16899, 16888, 16889, 17134), class = "Date"), purchaseQty = c(162000L, 
5000L, 6500L, 221367L, 83633L, 60500L)), .Names = c("productId", 
"customerID", "transactionDate", "purchaseQty"), row.names = c("1:", 
"2:", "3:", "4:", "5:", "6:"), class = "data.frame")