如何基于关联表起止日期复制记录并更新自身生效日期？

我有一张工时节省表（Savings Table），每行记录软件模块、利益相关者，以及该模块通过自动化为利益相关者节省的工时相关数据，包括手动/自动化任务耗时、执行次数、频率，还有生效起止日期。示例数据如下：

还有一张利益相关者表（Stakeholder Table），记录利益相关者姓名、基本工资及薪资生效起止日期，薪资变动时会新增记录，最新记录的生效结束日期为NULL。示例数据：

核心需求 #

需要关联两张表，实现以下目标：

1. 计算小时费率（由基本工资转换）、生效周期内节省的工时及人力成本
2. 若利益相关者在工时节省记录的生效周期内发生薪资变动，需拆分原记录，分别对应不同薪资区间的生效日期，最终输出如下格式的表格：

规则澄清 #

• Task 1 Automation：需拆分记录，分别应用60,000和80,000两个薪资标准
• Task 4 Automation：仅需应用80,000薪资（整个生效周期内薪资未变动）
• Task 5 Automation：仅需应用60,000薪资（整个生效周期内薪资未变动）

请问实现该需求的最优方案是什么？

方案1：SQL查询直接生成结果（推荐） #

利用SQL的日期区间重叠匹配逻辑，将工时节省记录与利益相关者的薪资记录关联，自动拆分重叠的日期区间，无需额外预处理。以下是通用SQL逻辑（以MySQL为例，其他数据库可调整日期函数）：

SELECT
    s.Module,
    s.Stakeholder,
    s.`Manual Task Time (min)`,
    s.`Automated Task Time (min)`,
    s.`Times Task Performed`,
    s.Frequency,
    -- 计算薪资区间与工时记录区间的重叠起始日期
    GREATEST(s.`Effective Start`, sh.`Effective Start`) AS `Effective Start`,
    -- 计算重叠结束日期：处理薪资记录的NULL（表示当前生效）
    CASE
        WHEN s.`Effective End` IS NULL AND sh.`Effective End` IS NULL THEN NULL
        WHEN s.`Effective End` IS NULL THEN sh.`Effective End`
        WHEN sh.`Effective End` IS NULL THEN s.`Effective End`
        ELSE LEAST(s.`Effective End`, sh.`Effective End`)
    END AS `Effective End`,
    sh.`Base Salary` AS Salary,
    -- 计算小时费率：按年2080标准工作小时换算
    ROUND(sh.`Base Salary` / 2080, 2) AS Hourly_Rate,
    -- 计算单次任务节省工时（分钟转小时）
    ROUND((s.`Manual Task Time (min)` - s.`Automated Task Time (min)`)/60, 2) AS Saved_Hours_Per_Task,
    -- 计算周期内总节省工时：根据频率换算执行次数
    ROUND(
        ((s.`Manual Task Time (min)` - s.`Automated Task Time (min)`)/60) *
        s.`Times Task Performed` *
        CASE s.Frequency
            WHEN 'Day' THEN DATEDIFF(LEAST(s.`Effective End`, COALESCE(sh.`Effective End`, CURDATE())), GREATEST(s.`Effective Start`, sh.`Effective Start`)) + 1
            WHEN 'Week' THEN FLOOR(DATEDIFF(LEAST(s.`Effective End`, COALESCE(sh.`Effective End`, CURDATE())), GREATEST(s.`Effective Start`, sh.`Effective Start`)) / 7) + 1
            WHEN 'Month' THEN PERIOD_DIFF(DATE_FORMAT(LEAST(s.`Effective End`, COALESCE(sh.`Effective End`, CURDATE())), '%Y%m'), DATE_FORMAT(GREATEST(s.`Effective Start`, sh.`Effective Start`), '%Y%m')) + 1
        END
    , 2) AS Total_Saved_Hours,
    -- 计算节省的人力成本
    ROUND(
        ((s.`Manual Task Time (min)` - s.`Automated Task Time (min)`)/60) *
        s.`Times Task Performed` *
        CASE s.Frequency
            WHEN 'Day' THEN DATEDIFF(LEAST(s.`Effective End`, COALESCE(sh.`Effective End`, CURDATE())), GREATEST(s.`Effective Start`, sh.`Effective Start`)) + 1
            WHEN 'Week' THEN FLOOR(DATEDIFF(LEAST(s.`Effective End`, COALESCE(sh.`Effective End`, CURDATE())), GREATEST(s.`Effective Start`, sh.`Effective Start`)) / 7) + 1
            WHEN 'Month' THEN PERIOD_DIFF(DATE_FORMAT(LEAST(s.`Effective End`, COALESCE(sh.`Effective End`, CURDATE())), '%Y%m'), DATE_FORMAT(GREATEST(s.`Effective Start`, sh.`Effective Start`), '%Y%m')) + 1
        END *
        (sh.`Base Salary` / 2080)
    , 2) AS Total_Saved_Cost
FROM
    Savings_Table s
JOIN
    Stakeholder_Table sh ON s.Stakeholder = sh.Stakeholder
    -- 关联条件：薪资区间与工时记录区间存在重叠
    AND (
        (sh.`Effective Start` <= s.`Effective End` OR s.`Effective End` IS NULL)
        AND (sh.`Effective End` >= s.`Effective Start` OR sh.`Effective End` IS NULL)
    )
ORDER BY
    s.Module, `Effective Start`;

方案优势 #

• 无需额外ETL工具或预处理，直接通过SQL生成目标结果
• 自动处理薪资变动导致的区间拆分，逻辑清晰
• 一次性完成关联、拆分、计算三个核心步骤
• 可直接集成到报表或数据可视化工具中

方案2：ETL工具处理（适合复杂场景） #

如果涉及更复杂的业务规则（比如节假日、非标准工作时间），可以用ETL工具（如Apache Airflow、DataStage、Fivetran）处理：

1. 数据抽取：从两张表中读取原始数据
2. 数据转换：
   • 遍历每条工时记录，匹配该利益相关者在对应周期内的所有薪资记录
   • 对每个薪资记录，计算与工时记录的重叠日期区间，拆分生成新记录
   • 计算小时费率、节省工时、人力成本等字段
3. 数据加载：将处理后的数据写入目标表或数据仓库

方案优势 #

• 适合处理复杂的业务规则和数据清洗需求
• 可扩展性强，支持后续新增规则

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