本文介绍使用 pandas 实现「基于订单日期查找生效前最新成本价」的完整方案，通过时间对齐、左连接、时间过滤与去重三步法，确保每笔订单关联其下单时刻实际有效的成本价。
本文介绍使用 pandas 实现「基于订单日期查找生效前最新成本价」的完整方案，通过时间对齐、左连接、时间过滤与去重三步法，确保每笔订单关联其下单时刻实际有效的成本价。
在电商或 Marketplace 销售分析中，商品成本价往往随采购批次、供应商调价或汇率变动而动态更新。若直接用最新成本价统一计算所有历史订单毛利，会导致严重失真；而若仅存储单一静态成本字段，又无法反映真实业务逻辑。正确做法是：为每一笔订单匹配其 order_date 时点已生效、且距离该时点最近的一次成本价变更记录——即“生效日 ≤ 订单日”的最新价格。
核心思路是构建「时间有效区间」逻辑：每条成本价记录（如 price_change_date=2024-01-10, new_cost_price=110）表示自该日起，该价格开始生效，直至下一次调价前均有效。因此，对任意订单日期，需找到满足 price_change_date ≤ order_date 的最大 price_change_date 对应的成本价。
以下是可直接运行的完整实现（基于修正后的数据结构）：
import pandas as pd
# 订单表（已转为 datetime）
orders_test = pd.DataFrame({
'order_id': ['82514913-0085-1', '82514913-0085-2', '82514913-0090-3', '82514913-0090-4'],
'article_id': ['99823WH2MO11093_34'] * 4,
'profit': [1042.91, 1042.91, 2000.00, 2000.00],
'order_date': pd.to_datetime(['2024-01-15', '2024-01-21', '2024-02-15', '2024-02-20'])
})
# 成本价变更表（含生效日期与新价格）
cost_price_test = pd.DataFrame({
'article_id': ['99823WH2MO11093_34'] * 3,
'cost_price': [100, 110, 120], # 原始旧价（可选字段）
'price_change_date': pd.to_datetime(['2024-01-10', '2024-01-20', '2024-02-10']),
'new_cost_price': [110, 120, 100] # 实际生效的新成本价
})
# 步骤 1：填充缺失的新成本价（防御性处理）
cost_price_test["new_cost_price"] = cost_price_test["new_cost_price"].fillna(
cost_price_test["cost_price"]
)
# 步骤 2：左连接 + 按订单时间排序（为后续取“最近生效价”做准备）
merged = orders_test.merge(cost_price_test, on="article_id", how="left")
merged = merged.sort_values(["article_id", "order_date", "price_change_date"])
# 步骤 3：筛选「成本生效日不晚于订单日」的记录，并对每笔订单取最后一条（即最近生效价）
valid_mask = merged["price_change_date"] <= merged["order_date"]
result = (merged[valid_mask]
.groupby(["order_id", "article_id", "order_date", "profit"], as_index=False)
.apply(lambda g: g.nlargest(1, "price_change_date"))
.reset_index(drop=True)
.drop(columns=["cost_price", "price_change_date"])
.rename(columns={"new_cost_price": "cost_price"}))
print(result)✅ 输出结果：
order_id article_id profit order_date cost_price 0 82514913-0085-1 99823WH2MO11093_34 1042.91 2024-01-15 110 1 82514913-0085-2 99823WH2MO11093_34 1042.91 2024-01-21 120 2 82514913-0090-3 99823WH2MO11093_34 2000.00 2024-02-15 100 3 82514913-0090-4 99823WH2MO11093_34 2000.00 2024-02-20 100
? 关键注意事项：
- 时间精度一致性：务必确保 order_date 与 price_change_date 同为 datetime64[ns] 类型，避免字符串比较错误；
- 空值鲁棒性：建议在合并前用 .fillna() 处理 new_cost_price 缺失值，防止因数据录入不全导致结果为空；
- 性能优化：当成本价变更频率高或订单量巨大时，可先对 cost_price_test 按 (article_id, price_change_date) 排序并使用 pd.merge_asof 替代普通 merge，显著提升效率；
- 业务校验：上线前务必验证是否存在订单日期早于所有 price_change_date 的情况（即无历史成本），此类订单应单独标记或赋予默认值。
该方法本质是将「时间有效区间匹配」转化为「分组内 Top-1 时间筛选」，逻辑清晰、易于维护，适用于各类需要按时间快照关联维度属性的分析场景（如税率、运费模板、佣金比例等）。
