sql - 计算过期的凭证，给定一个固定的窗口

Question

问题:
我有赚取和兑换代金券的时间序列数据。优惠券只限一次 3天固定窗口包括他们获得的那一天，例如。 1 月 1 日获得的代金券将在 1 日、2 日和 3 日有效。
我需要做出一个假设，即当涉及到针对他们赚取的赎回时，它是先到先得的。例如。如果我们有数据

Date       VouchersEarned VouchersRedeemed
01/01/2020             10                0
02/01/2020              8                9
03/01/2020              4                4
04/01/2020              2                4
05/01/2020              1                4

然后在 2 日，那 9 张代金券来自第 1 次，即我们还有剩余的代金券

Date       VouchersEarned VouchersRedeemed RemainingVouchers
01/01/2020             10                0                 1
02/01/2020              8                9                 8

然后在 3 日，这 4 次赎回将是第 1 次的剩余 1 次和第 2 次的 3 次

Date       VouchersEarned VouchersRedeemed RemainingVouchers
01/01/2020             10                0                 0
02/01/2020              8                9                 5
03/01/2020              4                4                 0

第四:

Date       VouchersEarned VouchersRedeemed RemainingVouchers
01/01/2020             10                0                 0
02/01/2020              8                9                 1
03/01/2020              4                4                 4
04/01/2020              2                4                 2

第5个:

Date       VouchersEarned VouchersRedeemed RemainingVouchers
01/01/2020             10                0                 0
02/01/2020              8                9                 1**
03/01/2020              4                4                 0
04/01/2020              2                4                 2
05/01/2020              1                4                 1

所以在 5 日，我们拿到了第一张过期的优惠券，因为 ** 一张在 3 天内没有用完。
我需要在任何时间点使用此逻辑流计算有多少凭证过期。我可以在脑海中弄清楚如何做到这一点，像上面一样逐行进行。但是我正在努力了解如何以矢量化方式进行操作，这也可以在 MSSQL 中使用。如果它只是在 R 中，我可以用循环暴力破解它。我也在 Cross Validated 上发布了这个但还没有反馈，因此将其扩展到编程社区。
数据:
我已经包含了一个 R dput最后但这是它的样子
编辑:本摘要末尾有一些新数据，这是一个更棘手的示例

        Date VouchersEarned VouchersRedeemed ActualActive ActualExpired CumulativeEarned
1 01/01/2020             10                0           10             0               10
2 02/01/2020              8                9            9             0               18
3 03/01/2020              4                4            9             0               22
4 04/01/2020              2                4            7             0               24
5 05/01/2020              1                4            3             1               25
6 06/01/2020              0                1            2             0               25
7 07/01/2020              0                1            0             1               25
8 08/01/2020              0                0            0             0               25
  CumulativeRedeemed CumulativeDiff
1                  0             10
2                  9              9
3                 13              9
4                 17              7
5                 21              4
6                 22              3
7                 23              2
8                 23              2

ActualActive & ActualExpired是我用笔和纸得出的数字。请注意，我将它们放在它们到期的日期上，而不是根据它们获得的日期。要么对我有用，只是改变报告 View 。我可以通过查看 CumulativeEarned 来获得总代金券。 & CumulativeRedeemed然后取差额。我想如果我能得到过期的，那么计算活跃的就很简单了。
如果有人有任何想法，我将不胜感激，因为我今天似乎对此有心理障碍!谢谢! :)
编辑:我的实际问题是一个 28 天的窗口，这是一个简化的 View :)

df <- structure(list(Date = c("01/01/2020", "02/01/2020", "03/01/2020", 
"04/01/2020", "05/01/2020", "06/01/2020", "07/01/2020", "08/01/2020"
), VouchersEarned = c(10L, 8L, 4L, 2L, 1L, 0L, 0L, 0L), VouchersRedeemed = c(0L, 
9L, 4L, 4L, 4L, 1L, 1L, 0L), ActualActive = c(10L, 9L, 9L, 7L, 
3L, 2L, 0L, 0L), ActualExpired = c(0L, 0L, 0L, 0L, 1L, 0L, 1L, 
0L), CumulativeEarned = c(10L, 18L, 22L, 24L, 25L, 25L, 25L, 
25L), CumulativeRedeemed = c(0L, 9L, 13L, 17L, 21L, 22L, 23L, 
23L), CumulativeDiff = c(10L, 9L, 9L, 7L, 4L, 3L, 2L, 2L)), class = "data.frame", row.names = c(NA, 
-8L))

编辑 2: 我最近在 R 中的尝试 .虽然有些问题，但我觉得滞后列的正确组合并非不可能

library(data.table)
dt <- as.data.table(df)
dt[, Date := lubridate::dmy(Date)]  

# functional form
findExpiredVouchers <- function(dt, period=3){
  
  #  generation of cumulative data
  dt[, CumulativeEarned := cumsum(VouchersEarned)]
  dt[, CumulativeRedeemed := cumsum(VouchersRedeemed)]
  dt[, CumulativeDiff := CumulativeEarned-CumulativeRedeemed]
  
  # I think if we look at the cumulative earned against the cumulative redeemed,
  # plus it's lag period from that point, ie the cumulative redeemed in 2 days,
  # then we can see for data prior to last 3 which have expired
  dt[, LaggedCumulativeRedeemed := shift(CumulativeRedeemed, period-1, type="lead")]
  dt[, ExpiredCumulative := CumulativeEarned - LaggedCumulativeRedeemed]
  
  # Now this creates negative values though for eg the first case, I'm not 100%
  # if I need to worry about these
  dt[ExpiredCumulative < 0, ExpiredCumulative := 0]
  
  # so now it should be the difference in this series that captures the origin
  # of an expiration
  dt[, Expired := c(NA, diff(ExpiredCumulative))]
  dt[1, Expired := ExpiredCumulative]
  
  # and I can shift this by the lag period to get the end result
  dt[, OutputExpired := shift(Expired, period, type="lag")]
  dt[is.na(OutputExpired), OutputExpired := 0]
  
  # and active
  dt[, CumulativeExpired := cumsum(OutputExpired)]
  dt[, OutputActive := CumulativeDiff-CumulativeExpired]
}
dt <- findExpiredVouchers(dt, 3)
dt[] # OutputExpired & OutputActive

有了一些新的虚假数据，随着负到期时间的出现，缺陷就会暴露出来:(

set.seed(1)
p = 0.2
new_dt <- data.table(
  Date = 1:10,
  VouchersEarned = sample(0:15, 10, replace=TRUE)
  )
new_dt[, CumulativeEarned := cumsum(VouchersEarned)]

# fake VouchersRedeemed
new_dt[, VouchersRedeemed := as.integer(NA)]
new_dt[, CumulativeDiff := CumulativeEarned]
for(i in 1:nrow(new_dt)){
  new_value <- sum(rbinom(new_dt$CumulativeDiff[i], 1, p))
  new_dt[i, VouchersRedeemed := new_value]
  new_dt[i:.N, CumulativeDiff := CumulativeDiff - new_value]
}

new_dt <- findExpiredVouchers(dt=new_dt, 3)
new_dt[] # OutputExpired < 0

新例子

    Date VouchersEarned VouchersRedeemed OutputExpired
 1:    1              8                1             0
 2:    2              3                1             0
 3:    3              6                3             0
 4:    4              0                1             3
 5:    5              1                1             2
 6:    6             12                5             5
 7:    7              6                5            -5
 8:    8             10                3            -4
 9:    9             13                7             9
10:   10              1                8            -1

运行 Waldi 的 循环显示了类似的结果，但 -5、-4 和 9 取消为 0(它们应该如此!)

Answer 1

使用 tidyverse 库，特别是 dplyr , 和 magrittr ，你可以写这个代码

df %<>%  
  mutate(VouchersEarnedCumsum = cumsum(VouchersEarned),
         VouchersRedeemedCumsum = cumsum(VouchersRedeemed),
         VouchersCumsumDifference = VouchersEarnedCumsum - lead(VouchersRedeemedCumsum, 2, default = max(VouchersRedeemedCumsum)),
         VouchersCumsumDifference = as.numeric(VouchersCumsumDifference),
         VouchersCumsumDifference = case_when(VouchersCumsumDifference < 0 ~ 0, T ~ VouchersCumsumDifference),
         ExpiredVouchers = VouchersCumsumDifference - lag(VouchersCumsumDifference, default = 0),
         ExpiredVouchersInDate = lag(ExpiredVouchers, 3, default = 0))

结果表，从你的数据帧 df 开始，是

    # A tibble: 8 x 8
      Date       VouchersEarned VouchersRedeemed VouchersEarnedCumsum VouchersRedeemedCumsum VouchersCumsumDifference ExpiredVouchers ExpiredVouchersInDate
      <chr>               <int>            <int>                <int>                  <int>                    <dbl>           <dbl>                 <dbl>
    1 01/01/2020             10                0                   10                      0                        0               0                     0
    2 02/01/2020              8                9                   18                      9                        1               1                     0
    3 03/01/2020              4                4                   22                     13                        1               0                     0
    4 04/01/2020              2                4                   24                     17                        2               1                     0
    5 05/01/2020              1                4                   25                     21                        2               0                     1
    6 06/01/2020              0                1                   25                     22                        2               0                     0
    7 07/01/2020              0                1                   25                     23                        2               0                     1
    8 08/01/2020              0                0                   25                     23                        2               0                     0

然后，很容易使用 dbplyr 翻译相应 SQL 查询中的每个函数，使用 translate_sql .
我不确定此代码是否适用于您将要面对的每种情况，我建议对其进行广泛的测试。
编辑
我认为这个更正的代码解决了这个问题。

df %<>%  
  mutate(VouchersEarnedCumsum = cumsum(VouchersEarned),
         VouchersRedeemedCumsum = cumsum(VouchersRedeemed),
         VouchersCumsumDifference = VouchersEarnedCumsum - lead(VouchersRedeemedCumsum, 2, default = max(VouchersRedeemedCumsum)),
         VouchersCumsumDifference = as.numeric(VouchersCumsumDifference),
         VouchersCumsumDifference = case_when(VouchersCumsumDifference < 0 ~ 0, T ~ VouchersCumsumDifference),
         VouchersCumsumDifference = accumulate(VouchersCumsumDifference, max),
         ExpiredVouchers = VouchersCumsumDifference - lag(VouchersCumsumDifference, default = 0),
         ExpiredVouchersInDate = lag(ExpiredVouchers, 3, default = 0))