Teradata SQL Case条件编写：按ID校验当前月记录与上条日期间隔

核心思路 #

1. 用LAG()窗口函数按ID分组、按startdate降序排序，提取每条记录的上一条记录的enddate（即同一ID的前一个时间区间结束日期）。
2. 借助Teradata日期函数判断当前月记录与上一条记录的时间间隔是否超3个月。
3. 通过CASE表达式分别生成activerec和Flag字段。

完整SQL代码 #

WITH ranked_data AS (
    SELECT 
        ID,
        startdate,
        enddate,
        name,
        -- 生成activerec字段：判断startdate是否为当前月份
        CASE 
            WHEN TRUNC(startdate, 'MONTH') = TRUNC(CURRENT_DATE, 'MONTH') 
            THEN 'Yes' 
            ELSE 'No' 
        END AS activerec,
        -- 获取同一ID的上一条记录的enddate（按startdate从新到旧排序）
        LAG(enddate) OVER (PARTITION BY ID ORDER BY startdate DESC) AS prev_enddate
    FROM your_table_name -- 替换为实际表名
)
SELECT 
    ID,
    startdate,
    enddate,
    name,
    activerec,
    -- 生成Flag字段：仅对当前月记录校验间隔，非当前月记录直接标记'No'
    CASE 
        WHEN activerec = 'Yes' 
            THEN CASE 
                    WHEN ADD_MONTHS(prev_enddate, 3) < startdate 
                    THEN 'Yes' 
                    ELSE 'No' 
                 END
        ELSE 'No'
    END AS Flag
FROM ranked_data
ORDER BY ID, startdate DESC;

代码细节解释 #

• ranked_data CTE：
  • TRUNC(startdate, 'MONTH')：将日期截断至当月第一天，用于和当前月起始日期对比，精准判断是否为当前月记录。
  • LAG(enddate) OVER (PARTITION BY ID ORDER BY startdate DESC)：按ID分组后，按startdate从新到旧排序，自动抓取每条记录的上一条（时间更早的区间）的enddate。
• 主查询：
  • 对activerec = 'Yes'的当前月记录，用ADD_MONTHS(prev_enddate, 3) < startdate判断间隔：如果上一条的enddate加3个月后仍早于当前startdate，说明间隔超3个月，标记'Yes'，否则标记'No'。
  • 非当前月记录的Flag直接设为'No'。

示例结果验证 #

针对提供的测试数据，执行上述SQL后会得到如下结果：

完全匹配预期逻辑：

• ID101的当前月记录（03-01-2023）与上一条enddate（02-28-2023）间隔1个月，Flag为'No'。
• ID102的当前月记录（03-01-2023）与上一条enddate（10-31-2022）间隔5个月（超3个月），Flag为'Yes'。

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