本文介绍如何基于订单发生时间，精准关联对应生效日期的成本价，解决因价格变动导致的利润计算失真问题，核心是通过时间区间匹配实现“所见即所得”的成本归属。
本文介绍如何基于订单发生时间，精准关联对应生效日期的成本价，解决因价格变动导致的利润计算失真问题，核心是通过时间区间匹配实现“所见即所得”的成本归属。
在电商或零售类销售分析中，商品成本价往往随采购批次、供应商调价或汇率波动而动态变化。若直接用最新成本价统一核算所有历史订单，将严重扭曲毛利率、ROI等关键指标；若仅用初始成本价，则无法反映真实经营成本。正确做法是：为每笔订单匹配其下单时刻已生效的最新成本价——即查找该订单日期前（含当日）最后一次生效的价格记录。
实现这一逻辑的关键在于构建“有效时间区间”。观察 cost_price_test 表可知，每条记录代表一次调价事件：price_change_date 是新价格 new_cost_price 的生效起始日，而前一条记录的 price_change_date（或业务起始日）即为其有效期的开始。因此，对任意订单日期 order_date，应匹配满足 price_change_date ≤ order_date 的最大 price_change_date 对应的成本价。
以下是完整、健壮的 Pandas 实现方案：
import pandas as pd
# 示例数据（已修正原始输入中的不一致）
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.0, 2000.0],
'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：确保 new_cost_price 非空（处理可能的 NaN）
cost_price_test["new_cost_price"] = cost_price_test["new_cost_price"].fillna(
cost_price_test["cost_price"]
)
# 步骤2：以 article_id 为键，对 orders 和 cost_price 做笛卡尔积式左连接
merged = orders_test.merge(cost_price_test, on="article_id", how="left")
# 步骤3：筛选出“价格生效日 ≤ 订单日”的有效记录
valid_prices = merged[merged["order_date"] >= merged["price_change_date"]]
# 步骤4：对每个订单，取满足条件的最新（最大）price_change_date 对应的记录
# 先按 article_id + order_date 分组，再对 price_change_date 降序排序，取首行
result = (
valid_prices.sort_values(["article_id", "order_date", "price_change_date"], ascending=[True, True, False])
.groupby(["order_id", "article_id", "order_date"], as_index=False)
.first()
.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.0 1 82514913-0085-2 99823WH2MO11093_34 1042.91 2024-01-21 120.0 2 82514913-0090-3 99823WH2MO11093_34 2000.00 2024-02-15 100.0 3 82514913-0090-4 99823WH2MO11093_34 2000.00 2024-02-20 100.0
✅ 关键注意事项：
- 时间精度：确保 order_date 和 price_change_date 均为 datetime64[ns] 类型，避免字符串比较错误；
- 边界处理：使用 >= 而非 >，保证订单发生在调价当日时能正确匹配；
- 数据完整性：若某订单日期早于所有 price_change_date（如系统上线前的老订单），需额外补充默认成本价或标记异常；
- 性能优化：当订单量或价格变更记录量极大时（>100万行），建议改用 pd.merge_asof()（需先按 price_change_date 排序 cost_price_test），效率提升显著；
- 业务校验：最终结果应与业务规则交叉验证——例如检查 2月15日订单匹配到2月10日调价后的100元，符合“取订单日前最近一次生效价”逻辑。
此方法将成本价从静态字段升级为时间感知变量，是构建可信财务分析报表的基石。
