如何在R中按先旧后新规则计算客户累计信贷余额

业务背景与规则 #

• 我司向客户赊销产品，客户需在产品质保到期日前结清欠款
• 采购记录字段：clientID、产品类别、采购日期、信贷金额、质保到期日
• 还款记录字段：clientID、还款金额、还款日期
• 核心规则：客户可在旧信贷未结清时再次赊购；还款不指定对应产品，仅冲抵总欠款，且还款优先冲抵最早的未结清产品

模拟业务数据代码 #

# 设置随机种子保证可复现
set.seed(123)

# 定义数据行数
n_purchase <- 20
n_repayment <- 20

# 定义各字段可选值
clientID <- 101:110
product_category <- LETTERS[1:3]
purchase_date <- seq(as.Date("2020-01-01"), as.Date("2020-12-31"), by = "day")
credit_amount <- seq(1000, 2000, by = 100)
repayment_amount <- seq(500, 1000, by = 50)
repayment_date <- purchase_date

# 生成采购数据框
purchase_df <- tibble(
  clientID = sample(clientID, n_purchase, replace = TRUE),
  product_category = sample(product_category, n_purchase, replace = TRUE),
  purchase_date = sample(purchase_date, n_purchase, replace = FALSE),
  credit_amount = sample(credit_amount, n_purchase, replace = TRUE),
  warranty_expiration_date = purchase_date + years(1) + days(14)
)

# 生成还款数据框
repayment_df <- tibble(
  clientID = sample(clientID, n_repayment, replace = TRUE),
  repayment_amount = sample(repayment_amount, n_repayment, replace = TRUE),
  repayment_date = sample(repayment_date, n_repayment, replace = FALSE)
)

需求与问题 #

需要为purchase_df的每条记录添加客户当日信贷余额字段，现有方法因不同产品质保期差异无法匹配规则要求，求满足逻辑的R代码。

解决方案代码 #

library(tidyverse)
library(lubridate)

# 按规则计算每笔采购的当日信贷余额
result_df <- purchase_df %>%
  # 按客户、采购日期排序，保证先购先冲逻辑
  arrange(clientID, purchase_date) %>%
  group_by(clientID) %>%
  mutate(purchase_seq = row_number()) %>%
  ungroup() %>%
  # 合并还款数据，筛选采购当日及之前的还款记录
  full_join(
    repayment_df %>%
      arrange(clientID, repayment_date) %>%
      group_by(clientID) %>%
      mutate(cum_repayment = cumsum(repayment_amount)) %>%
      ungroup(),
    by = "clientID"
  ) %>%
  filter(repayment_date <= purchase_date | is.na(repayment_date)) %>%
  # 按客户和采购记录分组，计算截至采购日的累计还款
  group_by(clientID, purchase_seq) %>%
  summarize(
    across(c(product_category, purchase_date, credit_amount, warranty_expiration_date), first),
    total_repayment = sum(repayment_amount, na.rm = TRUE)
  ) %>%
  group_by(clientID) %>%
  # 计算累计信贷额，按先购先冲规则分配还款
  mutate(
    cum_credit = cumsum(credit_amount),
    # 计算当前采购可被冲抵的金额：累计还款减去前序所有采购的信贷总额，不超过自身信贷额
    allocated_payment = pmin(
      credit_amount,
      max(0, total_repayment - lag(cum_credit, default = 0))
    ),
    # 修正：若累计还款超过累计信贷，当前采购全额冲抵
    allocated_payment = ifelse(total_repayment >= cum_credit, credit_amount, allocated_payment),
    # 当日余额 = 信贷额 - 已分配冲抵金额
    credit_balance_on_purchase = credit_amount - allocated_payment
  ) %>%
  ungroup() %>%
  # 清理中间计算字段
  select(-purchase_seq, -cum_credit, -allocated_payment, -total_repayment)

# 查看结果
print(result_df)

代码说明 #

1. 先对采购记录按客户、日期排序，生成采购顺序标识，保证先购先冲的规则基础
2. 合并还款数据后，仅保留采购当日及之前的还款，确保计算的是采购当日的余额
3. 按客户分组计算累计信贷额，再根据累计还款额为每笔采购分配冲抵金额：优先冲抵最早的采购，直到该笔采购的信贷额被完全冲抵，再处理后续采购
4. 最终计算每笔采购当日的剩余信贷余额

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