在Synapse SQL中获取允许30天间隔的客户连续合同周期

我需要在Synapse SQL中，根据客户合同的起止日期识别所有连续合同周期，规则是合同间最多允许30天的间隔。之前尝试用LAG和ROW_NUMBER函数，但因为按StartDate排序时存在重叠合同，导致结果不符合预期。

现有代码 #

WITH RankedContracts AS (
    SELECT 
        CustomerID,
        ContractID,
        StartDate,
        EndDate,
        -- 确保计算PrevEndDate的正确顺序
        LAG(EndDate) OVER (PARTITION BY CustomerID ORDER BY StartDate, ContractID) AS PrevEndDate
    FROM Contracts
),
PeriodBreaks AS (
    SELECT 
        CustomerID,
        ContractID,
        StartDate,
        EndDate,
        -- 判断当前合同是否与上一个合同不连续
        CASE 
            WHEN DATEDIFF(DAY, PrevEndDate, StartDate) > 30 THEN 1 
            ELSE 0 
        END AS IsNewPeriod
    FROM RankedContracts
)

问题说明 #

现有代码的问题在于：第3行数据（CustomerID=1，ContractID=41）与第1行的间隔在30天内，但被错误标记为新周期。当前逻辑仅对比当前行与上一行，无法处理重叠合同场景下的连续判断。

现有代码运行结果 #

期望结果 #

解决方案 #

核心思路是跟踪每个客户的当前连续周期的最晚结束日期，通过递归CTE实现与所有历史合同的连续判断：

WITH ContractOrder AS (
    SELECT 
        CustomerID,
        ContractID,
        StartDate,
        EndDate,
        -- 按客户分组，按开始日期生成排序行号
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY StartDate) AS RowNum
    FROM Contracts
),
RunningPeriod AS (
    -- 初始化第一个合同的周期结束日期
    SELECT 
        CustomerID,
        ContractID,
        StartDate,
        EndDate,
        RowNum,
        EndDate AS CurrentPeriodMaxEnd
    FROM ContractOrder
    WHERE RowNum = 1
    
    UNION ALL
    
    -- 递归处理后续合同，维护当前周期的最晚结束日期
    SELECT 
        co.CustomerID,
        co.ContractID,
        co.StartDate,
        co.EndDate,
        co.RowNum,
        CASE 
            -- 当前合同与周期最晚结束日期间隔<=30天，更新周期最晚结束日期为两者最大值
            WHEN DATEDIFF(DAY, rp.CurrentPeriodMaxEnd, co.StartDate) <= 30 THEN 
                IIF(co.EndDate > rp.CurrentPeriodMaxEnd, co.EndDate, rp.CurrentPeriodMaxEnd)
            -- 间隔超过30天，开启新周期
            ELSE co.EndDate
        END AS CurrentPeriodMaxEnd
    FROM ContractOrder co
    INNER JOIN RunningPeriod rp ON co.CustomerID = rp.CustomerID AND co.RowNum = rp.RowNum + 1
),
PeriodBreaks AS (
    SELECT 
        CustomerID,
        ContractID,
        StartDate,
        EndDate,
        -- 通过周期最晚结束日期的变化判断是否为新周期
        CASE 
            WHEN RowNum = 1 THEN 0
            WHEN CurrentPeriodMaxEnd <> LAG(CurrentPeriodMaxEnd) OVER (PARTITION BY CustomerID ORDER BY RowNum) THEN 1
            ELSE 0
        END AS IsNewPeriod
    FROM RunningPeriod
)
SELECT * FROM PeriodBreaks ORDER BY CustomerID, RowNum;

方案说明 #

1. ContractOrder：给每个客户的合同按开始日期排序，生成行号，为后续递归处理提供顺序依据。
2. RunningPeriod：用递归CTE逐个处理合同，维护当前连续周期的最晚结束日期。如果当前合同与该日期间隔≤30天，则更新周期的最晚结束日期；否则开启新周期。
3. PeriodBreaks：通过对比当前行与上一行的CurrentPeriodMaxEnd值，判断是否开启新周期——值发生变化则标记为1，否则为0。

这种方法能正确处理重叠合同场景，确保当前合同与所有历史合同的连续性判断。

内容的提问来源于stack exchange，提问作者Jani Hämäläinen