Databricks SQL实现合同承诺量基于日期的按比例分摊（Pro-Rate）方案问询

需求理解 #

我完全get到你的需求：针对同一ID和部件的主合同及后续修正案，需要精准计算每个承诺版本的生效时长占整个合同周期（从StartDate到EndDate）的比例，再用这个比例乘以对应Qty_Commit得到Pro_Rate。核心逻辑可以拆解为：

• 每个承诺的生效起始点是自身的Commit Date（首行主合同用StartDate，刚好和首行Commit Date一致）
• 每个承诺的生效结束点是下一个承诺的Commit Date；如果是最后一个承诺，则用合同的EndDate
• 用「当前承诺生效时长」除以「合同总周期时长」得到占比百分比
• Pro_Rate = Qty_Commit × 百分比数值

解决方案代码 #

在Databricks SQL中，我们可以借助窗口函数LEAD()轻松获取下一个承诺的生效日期，再结合日期差计算完成需求，以下是可直接运行的SQL：

WITH contract_next_date_cte AS (
    SELECT 
        ID,
        Part,
        Qty_Commit,
        Commit_Date,
        StartDate,
        EndDate,
        -- 按ID+Part分组、Commit_Date升序，获取下一个承诺日期；最后一行用EndDate填充
        LEAD(Commit_Date, 1, EndDate) OVER (PARTITION BY ID, Part ORDER BY Commit_Date) AS Next_Effective_Date
    FROM Contract_Base
),
period_calculation_cte AS (
    SELECT 
        *,
        -- 计算合同总周期天数（避免除以0，加异常判断）
        DATEDIFF(day, StartDate, EndDate) AS Total_Contract_Days,
        -- 计算当前承诺的生效天数：从自身Commit Date到下一个生效日期的天数
        DATEDIFF(day, Commit_Date, Next_Effective_Date) AS Active_Days
    FROM contract_next_date_cte
    WHERE DATEDIFF(day, StartDate, EndDate) > 0
)
SELECT 
    ID,
    Part,
    Qty_Commit,
    Commit_Date,
    StartDate,
    EndDate,
    -- 计算百分比，保留1位小数后拼接%符号
    CONCAT(ROUND((Active_Days / Total_Contract_Days) * 100, 1), '%') AS Percentage,
    -- 计算Pro_Rate，保留2位小数
    ROUND(Qty_Commit * (Active_Days / Total_Contract_Days), 2) AS Pro_Rate
FROM period_calculation_cte
ORDER BY ID, Part, Commit_Date;

代码分步解释 #

1. CTE 1: contract_next_date_cte

   • 用LEAD(Commit_Date, 1, EndDate)窗口函数，完美解决「下一个生效日期」的获取问题：按ID+Part分组、Commit_Date升序排序，自动为每个行匹配下一个承诺的日期；如果是最后一行，就用合同EndDate作为结束时间，彻底处理边界场景。

2. CTE 2: period_calculation_cte

   • 计算合同总周期天数Total_Contract_Days：从StartDate到EndDate的天数
   • 计算当前承诺的生效天数Active_Days：从自身Commit_Date到下一个生效日期（或EndDate）的天数
   • 加入WHERE DATEDIFF(...) > 0避免除以0的错误，兼容异常数据

3. 最终查询

   • 百分比计算：将生效天数占比转成百分比格式，用ROUND()控制精度后拼接%
   • Pro_Rate计算：直接用承诺数量乘以占比数值，保留2位小数
   • 最后按ID、Part、Commit_Date排序，和你提供的示例结果顺序完全一致

示例数据验证 #

用你给出的测试数据跑这个SQL，会完全匹配预期结果：

• 首行主合同：Active_Days = 2/24/2025 - 10/1/2024 = 146天，总周期365天，146/365=40%，Pro_Rate=550×0.4=220
• 第二行修正案：Active_Days=1天，1/365≈0.3%，Pro_Rate=600×0.00274≈1.64
• 最后一行修正案：Active_Days=10/1/2025 -7/17/2025=76天，76/365≈21%，Pro_Rate=900×0.2082≈187.4

额外注意事项 #

• 确保Commit_Date、StartDate、EndDate是DATE类型；如果是字符串格式，需要先转成日期：TO_DATE(Commit_Date, 'MM/dd/yyyy')
• 若存在同一ID+Part下Commit_Date重复的情况，可以在ORDER BY中补充其他字段（比如修正案编号）来保证排序逻辑准确
• 可以根据业务需求调整ROUND()的保留位数，适配不同精度要求

内容来源于stack exchange