如何在SQL中实现客户账户重叠/切换的多账户日期对比逻辑？

需求明确 #

• 数据字段：客户、账户、Renewal Date（续费日期）、Nth Renewal（第N次续费）
• 字段规则：
  • 当Nth Renewal为'1'时，代表该账户首次开通/续费
  • 当Nth Renewal为非'1'值时，代表该账户最后一次续费
• 判断逻辑：对每个首次开通/续费的账户，需对比其Renewal Date与该客户所有历史账户的最后续费日期：
  • 只要存在任一历史账户的最后续费日期 ≥ 新账户首次续费日期 → 标记为Overlap（账户重叠）
  • 若所有历史账户的最后续费日期都早于新账户首次续费日期 → 标记为Switch（账户切换）

解决方案SQL #

-- 提取每个客户所有历史账户的最后续费记录
WITH customer_last_renewals AS (
    SELECT 
        customer,
        account,
        renewal_date AS last_renewal_date
    FROM your_table
    WHERE nth_renewal != '1'
),
-- 提取所有首次开通/续费的账户记录
first_renewals AS (
    SELECT 
        customer,
        account,
        renewal_date AS first_renewal_date
    FROM your_table
    WHERE nth_renewal = '1'
)
SELECT 
    fr.customer,
    fr.account,
    fr.first_renewal_date,
    -- 核心判断：检查是否存在符合条件的历史账户
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM customer_last_renewals clr
            WHERE clr.customer = fr.customer
              AND clr.account != fr.account  -- 排除当前账户自身的历史记录
              AND clr.last_renewal_date >= fr.first_renewal_date
        ) THEN 'Overlap'
        ELSE 'Switch'
    END AS account_status
FROM first_renewals fr
ORDER BY fr.customer, fr.first_renewal_date;

逻辑说明 #

1. customer_last_renewals：筛选出所有账户的最终续费记录，作为历史账户的时间节点参考
2. first_renewals：筛选出所有需要判断的首次开通/续费账户
3. 主查询通过EXISTS子查询，针对每个首次续费账户，检查同客户下的其他历史账户是否有最后续费日期不早于当前首次续费日期的情况，以此输出对应的状态标记

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