运行余额下降时如何结转最近有效余额并覆盖异常月份数据

需求说明 #

现有数据集包含3个核心字段：

• client_id：客户唯一标识
• balance_month：月度统计时间
• balance：对应月度客户余额
  数据覆盖范围为2021年3月起的多客户全量月度余额，核心规则为：单客户当月余额较上月出现下降时，当月余额直接替换为最近一次余额未下降月份的余额值，要求逻辑可支持长周期数据自动化处理，无需随时间范围调整修改代码。

实现方案 #

SQL 实现（适配Hive/Spark SQL等主流数仓引擎） #

核心逻辑是用窗口函数标记余额上升节点，再用最大值窗口回溯填充：

WITH mark_rise AS (
  SELECT 
    client_id,
    balance_month,
    balance,
    -- 标记当前月是否为余额上升节点（比上月高/等于，或是首月）
    CASE WHEN balance >= LAG(balance,1) OVER(PARTITION BY client_id ORDER BY balance_month) 
          OR LAG(balance,1) OVER(PARTITION BY client_id ORDER BY balance_month) IS NULL
         THEN balance ELSE NULL END AS rise_balance
  FROM 你的原表名
),
fill_group AS (
  SELECT 
    *,
    -- 给连续的下降区间归为同一组，组标识为最近一次上升节点的时间
    MAX(CASE WHEN rise_balance IS NOT NULL THEN balance_month END) OVER(PARTITION BY client_id ORDER BY balance_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
  FROM mark_rise
)
SELECT 
  client_id,
  balance_month,
  balance AS original_balance,
  -- 同组取上升节点的余额作为最终填充值
  MAX(rise_balance) OVER(PARTITION BY client_id, group_id) AS adjusted_balance
FROM fill_group
ORDER BY client_id, balance_month;

Python Pandas 实现（适合本地小批量数据处理） #

import pandas as pd

def adjust_balance(df: pd.DataFrame) -> pd.DataFrame:
    # 先按客户+月份排序
    df = df.sort_values(['client_id', 'balance_month']).reset_index(drop=True)
    # 标记上升节点
    df['is_rise'] = df.groupby('client_id')['balance'].diff().ge(0) | df.groupby('client_id').cumcount().eq(0)
    # 生成组标识，把连续下降的行分到同一个上升节点的组里
    df['group_id'] = df.groupby('client_id')['is_rise'].cumsum()
    # 同组取最大值填充
    df['adjusted_balance'] = df.groupby(['client_id', 'group_id'])['balance'].transform('max')
    return df

# 调用示例
# df = pd.read_csv('你的余额数据文件路径.csv')
# result_df = adjust_balance(df)

逻辑验证说明 #

举个实际例子验证效果：

某客户原始余额数据：
2021-03：100 → 首月保留
2021-04：120 → 较上月上升，保留
2021-05：90 → 较上月下降，替换为最近上升节点2021-04的120
2021-06：110 → 较上月（原始90）上升，保留110
2021-07：105 → 较上月（原始110）下降，替换为最近上升节点2021-06的110
2021-08：130 → 较上月上升，保留
2021-09：125 → 较上月下降，替换为130
最终调整后余额和预期完全一致，且逻辑不限制时间范围，新增后续月份数据直接输入即可自动处理。

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