如何在R中基于同列前一行计算当前行的EAD1值？

计算规则 #

• 按Acc_Num分组，每组内按Record_count升序处理
• 当Record_count = 1时，EAD1 = Account_Balance
• 当Record_count > 1时，EAD1 = 同组内上一行已计算的EAD1值 × 当前行BRD值

Python Pandas 实现 #

简洁版（利用累积乘积） #

import pandas as pd

# 构造示例数据
data = {
    'Acc_Num': [100, 100, 100, 102, 102],
    'Record_count': [1, 2, 3, 1, 2],
    'BRD': [0.9, 0.9, 0.9, 0.8, 0.8],
    'Account_Balance': [100, 100, 100, 100, 100]
}
df = pd.DataFrame(data)

# 按客户编号分组，组内按记录序号排序
df = df.sort_values(['Acc_Num', 'Record_count'])

# 构造累积乘积序列：每组第一条BRD替换为1，保证初始值为Account_Balance
df['brd_cum'] = df.groupby('Acc_Num')['BRD'].transform(
    lambda x: pd.Series([1] + list(x[1:]))
)

# 计算EAD1
df['EAD1'] = df.groupby('Acc_Num')['brd_cum'].cumprod() * df['Account_Balance']

# 清理临时列并输出
df = df.drop('brd_cum', axis=1)
print(df)

循环遍历版（更直观） #

如果需要更清晰的步骤展示，可使用循环遍历每组记录：

import pandas as pd

data = {
    'Acc_Num': [100, 100, 100, 102, 102],
    'Record_count': [1, 2, 3, 1, 2],
    'BRD': [0.9, 0.9, 0.9, 0.8, 0.8],
    'Account_Balance': [100, 100, 100, 100, 100]
}
df = pd.DataFrame(data)

# 定义分组计算函数
def calc_ead1(group):
    group = group.sort_values('Record_count')
    group['EAD1'] = 0.0
    # 初始化第一条记录的EAD1
    group.loc[group['Record_count'] == 1, 'EAD1'] = group['Account_Balance'].iloc[0]
    # 遍历后续记录计算
    for i in range(1, len(group)):
        group.iloc[i, group.columns.get_loc('EAD1')] = group.iloc[i-1]['EAD1'] * group.iloc[i]['BRD']
    return group

# 应用函数并重置索引
df = df.groupby('Acc_Num', group_keys=False).apply(calc_ead1).reset_index(drop=True)
print(df)

SQL 实现（递归CTE方案） #

适用于主流关系型数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH ranked_data AS (
    -- 给每组记录按Record_count排序并生成行号
    SELECT 
        Acc_Num,
        Record_count,
        BRD,
        Account_Balance,
        ROW_NUMBER() OVER (PARTITION BY Acc_Num ORDER BY Record_count) AS rn
    FROM customer_data
),
recursive_ead AS (
    -- 递归基础：每组第一条记录的EAD1等于Account_Balance
    SELECT 
        Acc_Num,
        Record_count,
        BRD,
        Account_Balance,
        CAST(Account_Balance AS DECIMAL(10,2)) AS EAD1,
        rn
    FROM ranked_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归计算：后续记录用上一行EAD1乘以当前BRD
    SELECT 
        rd.Acc_Num,
        rd.Record_count,
        rd.BRD,
        rd.Account_Balance,
        CAST(re.EAD1 * rd.BRD AS DECIMAL(10,2)) AS EAD1,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_ead re ON rd.Acc_Num = re.Acc_Num AND rd.rn = re.rn + 1
)
-- 输出最终结果
SELECT 
    Acc_Num,
    Record_count,
    BRD,
    Account_Balance,
    EAD1
FROM recursive_ead
ORDER BY Acc_Num, Record_count;

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