如何编写Teradata SQL CASE条件判断ID及起始日期识别新成员

需求场景与规则 #

同一ID对应多条不同起始日期的记录，需根据以下规则标记会员身份：

• 若该ID存在当月起始日期记录（activerecord字段值为yes），且过去三个月内无其他起始日期记录，标记为yes it's a new member
• 其他所有情况，标记为not a new member

示例数据 #

源表数据 #

ID   startdate name  activerecord 
101 03-01-2023 Lee     yes
101 02-01-2023 Lee     No
101 12-01-2022 Lee     No
102 03-01-2023 Mark   yes
102 08-01-2022 Mark     No
102 07-01-2022 Mark     No

期望输出 #

ID  startdate name  activerecord flag
101 03-01-2023 Lee   yes          not a new member
101 02-01-2023 Lee   No           not a new member
101 12-01-2022 Lee   No           not a new member 
102 03-01-2023 Mark  yes          yes it's a new member
102 08-01-2022 Mark  No           not a new member
102 07-01-2022 Mark  No           not a new member

解决方案（Teradata SQL） #

利用窗口函数实现ID级别的聚合判断，无需关联子查询，效率更高：

WITH member_summary AS (
    SELECT 
        ID,
        startdate,
        name,
        activerecord,
        -- 标记当前ID是否存在当月有效记录
        MAX(CASE WHEN activerecord = 'yes' THEN 1 ELSE 0 END) OVER(PARTITION BY ID) AS has_current_month_record,
        -- 统计当前ID在过去三个月内（非当月）的记录数量
        COUNT(CASE WHEN TO_DATE(startdate, 'DD-MM-YYYY') >= ADD_MONTHS(TRUNC(CURRENT_DATE, 'MONTH'), -3) 
                   AND TO_DATE(startdate, 'DD-MM-YYYY') < TRUNC(CURRENT_DATE, 'MONTH') 
                   THEN 1 END) OVER(PARTITION BY ID) AS past_3month_records_cnt
    FROM member_records
)
SELECT 
    ID,
    startdate,
    name,
    activerecord,
    CASE 
        WHEN has_current_month_record = 1 AND past_3month_records_cnt = 0 
        THEN 'yes it''s a new member'
        ELSE 'not a new member'
    END AS flag
FROM member_summary;

逻辑说明 #

1. CTE member_summary：
   • has_current_month_record：通过MAX窗口函数，判断当前ID是否存在activerecord='yes'的当月记录，存在则返回1，否则0。
   • past_3month_records_cnt：通过COUNT窗口函数，统计当前ID在「当月起始日往前推3个月」到「当月起始日之前」的区间内的记录数。
2. 主查询CASE判断：
   • 当ID存在当月记录，且过去三个月内无其他记录时，标记为新会员；其余情况均标记为非新会员。

注意事项 #

• 若源表中startdate为DATE类型，可直接使用，无需TO_DATE转换。
• TRUNC(CURRENT_DATE, 'MONTH')用于获取当月第一天，ADD_MONTHS是Teradata自带的月份加减函数，确保日期区间计算准确。

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