基于LAG与Partition By实现同客户同诉求7天内重复触达判断

核心逻辑调整 #

原来的判断仅基于CustomerID和呼叫时间差，现在需要新增ContactReason1的匹配条件，核心思路是将呼叫原因纳入分区范围，或者在时间差判断时同时校验呼叫原因，以下是两种可行方案：

方案1：扩展分区键（推荐，性能更优）

1. 分区与排序：将PARTITION BY的范围从CustomerID扩展为CustomerID, ContactReason1，这样每个窗口内仅包含同一客户、同一呼叫原因的历史记录，再按呼叫时间CallDate升序排序。
2. 获取同原因的最近呼叫时间：用LAG()函数提取当前记录所在窗口内的上一条呼叫时间，得到LastSameReasonCallDate。
3. 时间差判断与结果映射：计算当前呼叫时间与LastSameReasonCallDate的天数差，若差值≤7则返回N（7天内相同原因重复联系），否则返回Y；首次出现该原因的记录因LastSameReasonCallDate为NULL，直接返回Y。

示例SQL：

SELECT
    CustomerID,
    CallDate,
    ContactReason1,
    CASE
        -- 处理首次呼叫该原因的场景，以及时间差超过7天的场景
        WHEN LAG(CallDate) OVER(
            PARTITION BY CustomerID, ContactReason1 
            ORDER BY CallDate
        ) IS NULL THEN 'Y'
        WHEN DATEDIFF(day, LAG(CallDate) OVER(
            PARTITION BY CustomerID, ContactReason1 
            ORDER BY CallDate
        ), CallDate) <= 7 THEN 'N'
        ELSE 'Y'
    END AS IsUniqueReasonContact
FROM
    YourCallRecordsTable;

方案2：保留原分区，新增原因校验

如果需要保留原有的分区逻辑（比如同时要判断任意原因的重复联系），可以在CASE判断中同时校验上一条记录的ContactReason1是否与当前一致，以及时间差是否在7天内：

示例SQL：

SELECT
    CustomerID,
    CallDate,
    ContactReason1,
    -- 原逻辑：7天内任意重复联系的标识
    CASE
        WHEN DATEDIFF(day, LAG(CallDate) OVER(PARTITION BY CustomerID ORDER BY CallDate), CallDate) <=7 THEN 'N'
        ELSE 'Y'
    END AS IsAnyRepeatContact,
    -- 新逻辑：7天内相同原因重复联系的标识
    CASE
        WHEN LAG(ContactReason1) OVER(PARTITION BY CustomerID ORDER BY CallDate) = ContactReason1
             AND DATEDIFF(day, LAG(CallDate) OVER(PARTITION BY CustomerID ORDER BY CallDate), CallDate) <=7 THEN 'N'
        ELSE 'Y'
    END AS IsSameReasonRepeatContact
FROM
    YourCallRecordsTable;

注意事项 #

• 若使用DATEDIFF函数，需注意不同数据库的语法差异（比如PostgreSQL用AGE()或DATE_PART，MySQL的DATEDIFF参数顺序相反）。
• 对于存在多条历史同原因记录的场景，LAG()仅取最近的一条，若需检查7天内所有同原因记录，可改用EXISTS关联子查询，但性能略逊于窗口函数。

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