基于过滤条件获取指定前置数据的技术实现需求问询

输入数据 #

预期结果 #

业务规则 #

• 新增一列存储符合条件的前置数据的total_assets值
• 前置数据需满足：day_yield > 0且create_date早于当前记录的create_date
• 若当前记录是第一条满足day_yield > 0的数据，新增列值为null
• 后续所有记录均取最近的（本例为唯一的）前置中day_yield > 0的total_assets值

解决方案1：SQL（窗口函数实现） #

利用LAST_VALUE窗口函数结合条件判断，精准获取前置有效数据：

SELECT
    total_assets,
    day_yield,
    create_date,
    CASE
        WHEN day_yield > 0 THEN NULL
        ELSE LAST_VALUE(CASE WHEN day_yield > 0 THEN total_assets END IGNORE NULLS) 
             OVER (ORDER BY create_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS prev_valid_assets
FROM your_table
ORDER BY create_date;

逻辑说明：

1. 通过CASE语句仅保留day_yield > 0记录的total_assets，其余设为NULL
2. LAST_VALUE(...) IGNORE NULLS忽略空值，取当前行之前最后一个有效total_assets
3. 对自身day_yield > 0的记录，直接将新增列设为NULL，匹配预期结果

解决方案2：Python Pandas实现 #

通过标记有效数据、向前填充的方式实现需求：

import pandas as pd

# 构造输入数据集
data = {
    'total_assets': [100, 200, 300, 400],
    'day_yield': [0.01, -0.01, -0.01, -0.01],
    'create_date': ['20171026', '20171027', '20171028', '20171029']
}
df = pd.DataFrame(data)

# 标记有效资产值，向前填充
df['valid_assets'] = df.apply(lambda x: x['total_assets'] if x['day_yield'] > 0 else pd.NA, axis=1)
df['prev_valid_assets'] = df['valid_assets'].ffill()

# 对自身为有效数据的行，将新增列设为空
df.loc[df['day_yield'] > 0, 'prev_valid_assets'] = pd.NA

# 输出结果
print(df.drop('valid_assets', axis=1))

输出结果：

total_assets  day_yield create_date prev_valid_assets
0           100       0.01    20171026               NaN
1           200      -0.01    20171027               100
2           300      -0.01    20171028               100
3           400      -0.01    20171029               100

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