MySQL成本持续变动场景下如何按日期和产品匹配核算成本

需按日期、产品维度匹配对应生效的单位成本完成核算，成本生效规则：表B中同一产品的成本自dailydate字段记录的日期开始启用，直至同产品出现新的成本记录为止。例：产品A在6月1日-6月7日期间单位成本为20，6月8日及之后单位成本为50，直到后续新的成本记录生效。

Table A（销量表） #

存储各产品每日销售数据，字段说明：

• product：产品编号
• date：销售日期
• qty：销售数量
  测试数据：

Table B（成本表） #

存储各产品成本调整记录，字段说明：

• product：产品编号
• dailydate：成本生效起始日期
• cost：单位成本
  测试数据：

1. 先对成本表做处理：用LEAD()窗口函数按产品分组、按生效日期排序，拿到每条成本记录对应的失效日期（即同产品下一条成本的生效日期，最后一条成本记录的失效日期设为足够远的未来日期，保证能匹配到所有后续无新成本调整的销售记录）
2. 关联销量表和处理后的成本区间表，关联条件为产品一致，且销售日期落在对应成本的生效区间内
3. 直接通过关联结果取对应单位成本，按需计算单条记录的总成本即可

注意：测试数据中日期为日/月/两位年的字符串格式，实际业务中建议将日期字段设为DATE类型，避免字符串格式不统一导致匹配错误，示例中已加入日期格式转换逻辑。

WITH cost_with_interval AS (
    SELECT
        product,
        dailydate AS effect_start,
        -- 计算当前成本的失效日期：同产品下一条成本的生效日期，最后一条取远期日期'2999-12-31'
        LEAD(dailydate, 1, '2999-12-31') OVER (
            PARTITION BY product
            ORDER BY STR_TO_DATE(dailydate, '%d/%c/%y')
        ) AS effect_end,
        cost
    FROM TableB
)
SELECT
    a.product,
    a.date,
    a.qty,
    c.cost AS unit_cost,
    a.qty * c.cost AS total_cost
FROM TableA a
LEFT JOIN cost_with_interval c
    ON a.product = c.product
    AND STR_TO_DATE(a.date, '%d/%c/%y') >= STR_TO_DATE(c.effect_start, '%d/%c/%y')
    AND STR_TO_DATE(a.date, '%d/%c/%y') < STR_TO_DATE(c.effect_end, '%d/%c/%y');

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