SQL/R实现按条件复制pmt值：从指定月份至180月或总和达标

SQL 实现（以递归CTE为例，适用于PostgreSQL、SQL Server等支持递归的数据库） #

递归CTE是生成序列记录的常用方式，可通过递归逐步生成每个key的月度记录，同时计算累计PMT，直到满足停止条件：

WITH RECURSIVE monthly_pmt AS (
    -- 初始行：取原始表所有记录，计算初始累计值
    SELECT 
        key,
        total,
        pmt,
        month,
        pmt AS cumulative_pmt
    FROM your_table_name
    UNION ALL
    -- 递归生成后续月份的记录
    SELECT 
        m.key,
        m.total,
        m.pmt,
        m.month + 1 AS month,
        m.cumulative_pmt + m.pmt AS cumulative_pmt
    FROM monthly_pmt m
    -- 停止条件：累计和未达到total，且月份未到180
    WHERE m.cumulative_pmt < m.total
      AND m.month < 180
)
-- 最终输出所有符合条件的记录
SELECT key, total, pmt, month
FROM monthly_pmt
ORDER BY key, month;

说明 #

• 递归CTE分为两部分：初始查询获取原始数据，递归部分生成下一个月的记录并更新累计值
• 停止条件严格控制：只要累计cumulative_pmt等于total，或者月份到180，就停止递归
• 若pmt为0或total无法被pmt整除，会在月份到180时自动停止

R 实现（使用dplyr + tidyr工具链） #

先计算每个key需要生成的月份数量，再扩展记录并过滤：

library(dplyr)
library(tidyr)

# 假设原始数据框名为df
result <- df %>%
  # 计算每个key需要生成的最大月份数：取"达到total所需步数"和"到180的剩余步数"中的较小值
  mutate(
    required_months = ifelse(pmt == 0, 180 - month + 1, ceiling(total / pmt)),
    actual_months = pmin(required_months, 180 - month + 1)
  ) %>%
  # 按actual_months扩展每个key的记录
  uncount(actual_months, .id = "month_offset") %>%
  # 计算实际的month值
  mutate(month = month + month_offset - 1) %>%
  # 计算累计pmt，过滤到累计和不超过total的行
  group_by(key) %>%
  mutate(cumulative_pmt = cumsum(pmt)) %>%
  filter(cumulative_pmt <= total) %>%
  # 选择需要的字段
  select(key, total, pmt, month) %>%
  ungroup() %>%
  arrange(key, month)

说明 #

• 先通过required_months计算理论上累计PMT达到total所需的月份数，再和到180的剩余月份数取最小值，确定实际生成的记录数
• 使用uncount扩展记录，再计算每个记录对应的month
• 最后通过累计和过滤，确保不出现累计值超过total的多余记录

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