基于冷却期逻辑计算客户存续期的SQL问题及优化需求

业务规则 #

• 以满足客户身份连续前提下的最早账户开立日期作为存续期起点
• 同一客户可同时拥有多个活跃账户
• 6个月冷却期规则：客户保持连续身份需满足任一条件：
  • 某账户关闭后6个月内开立新账户
  • 该账户关闭前已有其他活跃账户在运行
• 若账户关闭后超过6个月才开新账户，新账户的开立日期为新的存续期起点

场景示例（加粗为正确存续起始日期） #

• Customer_ID=11111：Account1开立日期为起点，因关闭账户前已有其他活跃账户
• Customer_ID=11112：Account1开立日期为起点，因账户关闭后6个月内开立新账户
• Customer_ID=11113：Account2开立日期为起点，因账户关闭后超6个月才开新账户
• Customer_ID=11114：**Account1开立日期（2000-01-20）**为起点，因Account1关闭后6个月内开立Account3，但原查询错误将Account3开立日期作为起点

原查询问题 #

原查询仅按账户开立日期顺序处理，未考虑跨账户的重叠活跃或非顺序关联场景，导致在某些复杂账户关系下误判存续起点。

修正后的SQL方案 #

核心思路是通过**会话分组（Sessionization）**识别客户的连续活跃周期：将满足冷却期规则的账户归为同一会话，每个会话的最早开立日期即为该周期的存续起点，最终取客户当前所属会话的最早起点。

WITH account_events AS (
    -- 标准化账户结束日期：未关闭账户用当前日期替代
    SELECT 
        Customer_ID,
        ACCT_SERIAL_NUM,
        ACCT_STRT_DT,
        NVL(ACCT_END_DT, SYSDATE) AS ACCT_END_DT,
        -- 判断当前账户是否属于连续活跃周期
        CASE 
            -- 场景1：当前账户活跃期间存在其他并行活跃账户
            WHEN EXISTS (
                SELECT 1 
                FROM calc_customer_tenure ct2
                WHERE ct2.Customer_ID = ct.Customer_ID
                AND ct2.ACCT_SERIAL_NUM != ct.ACCT_SERIAL_NUM
                AND ct2.ACCT_STRT_DT <= ct.ACCT_END_DT
                AND NVL(ct2.ACCT_END_DT, SYSDATE) >= ct.ACCT_STRT_DT
            ) THEN 'Y'
            -- 场景2：当前账户关闭后6个月内有新账户开立
            WHEN NVL(LEAD(ACCT_STRT_DT) OVER(PARTITION BY Customer_ID ORDER BY ACCT_STRT_DT), SYSDATE) 
                 <= ADD_MONTHS(NVL(ACCT_END_DT, SYSDATE), 6) THEN 'Y'
            -- 场景3：当前账户是后续账户的前置活跃账户（后续账户活跃期间包含当前账户关闭时间）
            WHEN EXISTS (
                SELECT 1 
                FROM calc_customer_tenure ct2
                WHERE ct2.Customer_ID = ct.Customer_ID
                AND ct2.ACCT_SERIAL_NUM != ct.ACCT_SERIAL_NUM
                AND ct2.ACCT_STRT_DT > ct.ACCT_STRT_DT
                AND ct.ACCT_END_DT >= ct2.ACCT_STRT_DT
                AND ct.ACCT_END_DT <= NVL(ct2.ACCT_END_DT, SYSDATE)
            ) THEN 'Y'
            ELSE 'N'
        END AS IS_CONTINUOUS
    FROM calc_customer_tenure ct
),
session_groups AS (
    -- 为每个客户划分连续活跃会话：遇到非连续标记则开启新会话
    SELECT 
        *,
        SUM(CASE 
                WHEN ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY ACCT_STRT_DT) = 1 THEN 0
                WHEN IS_CONTINUOUS = 'N' THEN 1
                ELSE 0
            END) 
        OVER(PARTITION BY Customer_ID ORDER BY ACCT_STRT_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SESSION_ID
    FROM account_events
),
session_start_dates AS (
    -- 计算每个会话的最早开立日期
    SELECT 
        Customer_ID,
        SESSION_ID,
        MIN(ACCT_STRT_DT) AS SESSION_START_DATE
    FROM session_groups
    GROUP BY Customer_ID, SESSION_ID
),
current_session AS (
    -- 确定客户当前所属的会话：优先选择包含未关闭账户的最新会话
    SELECT 
        s.Customer_ID,
        s.SESSION_START_DATE,
        ROW_NUMBER() OVER(PARTITION BY s.Customer_ID 
                          ORDER BY s.SESSION_ID DESC, 
                                   CASE WHEN ae.ACCT_END_DT = SYSDATE THEN 0 ELSE 1 END) AS RN
    FROM session_start_dates s
    JOIN session_groups ae ON s.Customer_ID = ae.Customer_ID 
                          AND s.SESSION_ID = ae.SESSION_ID
)
-- 最终输出每个客户的存续期起点
SELECT 
    Customer_ID,
    SESSION_START_DATE AS CUST_TNUR
FROM current_session
WHERE RN = 1
ORDER BY Customer_ID;

方案优势 #

• 覆盖所有核心业务场景：账户重叠活跃、冷却期内开新账户、非顺序账户关联
• 会话分组逻辑清晰，避免因账户顺序问题导致的起点误判
• 兼容未关闭账户的处理，用当前日期统一标准化结束日期

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