基于SQL首次购买日期实现新老客户标记的技术问询

原始查询SQL #

SELECT 
    CLIENT_NO, LOB_CD, 
    TO_DATE(CREATE_DT, 'MMYYYY') AS PURCHASE_DT,
    ROW_NUMBER () OVER (PARTITION BY CLIENT_NO ORDER BY CREATE_DT ASC) AS RN  
FROM CLIENTS_T

标签标记规则 #

• 若客户有2条记录（RN=2）且任一购买日期早于2019年，标记为**"OLD Client"**；
• 若客户仅有1条记录（RN=1）：
  • 购买日期早于2019年，标记为**"Old Client"**；
  • 购买日期在2019年及之后，且该客户在后续年份（2020、2021、2022）无购买记录，则标记为对应年份的**"new client"**（如2019年购买则标记为"2019 new client"）；
• 2020、2021、2022年规则同理：若客户在对应年份首次购买且前一年无购买记录，标记为对应年份的**"new client"**。

实现SQL #

WITH client_purchases AS (
    SELECT 
        CLIENT_NO,
        LOB_CD,
        TO_DATE(CREATE_DT, 'MMYYYY') AS PURCHASE_DT,
        ROW_NUMBER() OVER (PARTITION BY CLIENT_NO ORDER BY CREATE_DT ASC) AS RN,
        -- 提取客户首次购买年份
        EXTRACT(YEAR FROM MIN(TO_DATE(CREATE_DT, 'MMYYYY')) OVER (PARTITION BY CLIENT_NO)) AS FIRST_PURCHASE_YEAR,
        -- 统计客户总购买记录数
        COUNT(*) OVER (PARTITION BY CLIENT_NO) AS TOTAL_RECORDS,
        -- 标记各年份是否存在购买记录
        MAX(CASE WHEN EXTRACT(YEAR FROM TO_DATE(CREATE_DT, 'MMYYYY')) = 2018 THEN 1 ELSE 0 END) OVER (PARTITION BY CLIENT_NO) AS HAS_2018,
        MAX(CASE WHEN EXTRACT(YEAR FROM TO_DATE(CREATE_DT, 'MMYYYY')) = 2019 THEN 1 ELSE 0 END) OVER (PARTITION BY CLIENT_NO) AS HAS_2019,
        MAX(CASE WHEN EXTRACT(YEAR FROM TO_DATE(CREATE_DT, 'MMYYYY')) = 2020 THEN 1 ELSE 0 END) OVER (PARTITION BY CLIENT_NO) AS HAS_2020,
        MAX(CASE WHEN EXTRACT(YEAR FROM TO_DATE(CREATE_DT, 'MMYYYY')) = 2021 THEN 1 ELSE 0 END) OVER (PARTITION BY CLIENT_NO) AS HAS_2021,
        MAX(CASE WHEN EXTRACT(YEAR FROM TO_DATE(CREATE_DT, 'MMYYYY')) = 2022 THEN 1 ELSE 0 END) OVER (PARTITION BY CLIENT_NO) AS HAS_2022
    FROM CLIENTS_T
)
SELECT 
    CLIENT_NO,
    LOB_CD,
    PURCHASE_DT,
    RN,
    CASE
        -- 匹配规则1：2条记录且有早于2019年的购买
        WHEN TOTAL_RECORDS = 2 AND FIRST_PURCHASE_YEAR < 2019 THEN 'OLD Client'
        -- 匹配规则2：仅1条记录的分支
        WHEN TOTAL_RECORDS = 1 THEN
            CASE
                WHEN FIRST_PURCHASE_YEAR < 2019 THEN 'Old Client'
                WHEN FIRST_PURCHASE_YEAR = 2019 AND HAS_2020 = 0 AND HAS_2021 = 0 AND HAS_2022 = 0 THEN '2019 new client'
                WHEN FIRST_PURCHASE_YEAR = 2020 AND HAS_2021 = 0 AND HAS_2022 = 0 THEN '2020 new client'
                WHEN FIRST_PURCHASE_YEAR = 2021 AND HAS_2022 = 0 THEN '2021 new client'
                WHEN FIRST_PURCHASE_YEAR = 2022 THEN '2022 new client'
            END
        -- 匹配规则3：对应年份首次购买且前一年无购买
        WHEN FIRST_PURCHASE_YEAR = 2019 AND HAS_2018 = 0 THEN '2019 new client'
        WHEN FIRST_PURCHASE_YEAR = 2020 AND HAS_2019 = 0 THEN '2020 new client'
        WHEN FIRST_PURCHASE_YEAR = 2021 AND HAS_2020 = 0 THEN '2021 new client'
        WHEN FIRST_PURCHASE_YEAR = 2022 AND HAS_2021 = 0 THEN '2022 new client'
        -- 未匹配到上述规则的情况，可根据实际需求调整标签
        ELSE 'Other'
    END AS CLIENT_TAG
FROM client_purchases;

逻辑说明 #

1. 先通过CTEclient_purchases计算每个客户的核心维度数据，包括首次购买年份、总购买记录数，以及2018-2022各年份是否有购买记录，为后续标签判断提供基础数据；
2. 利用多层CASE WHEN按规则优先级依次匹配标签：先处理多记录的情况，再处理单记录分支，最后处理首次购买年份符合要求且前一年无购买的情况；
3. 未匹配到规则的记录统一标记为"Other"，可根据实际业务需求修改该默认标签。

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