如何编写Teradata查询实现特定Project_Type的RN字段赋值逻辑

需求拆解 #

• 对每个Project_Num（输出中为MSN）：
  1. A/B类型记录：首次出现的A或B类型记录，RN设为1；同项目下其他A/B类型记录RN为NULL。
  2. E类型记录：
     • 若项目无A/B类型记录：首次出现的E类型记录RN设为2，其余E类型记录RN为NULL。
     • 若项目同时存在A/B和E类型：仅当E的首次出现早于A/B的首次出现时，首次E类型记录RN设为2，其余E类型记录RN为NULL；若E的首次出现晚于A/B的首次出现，所有E类型记录RN为NULL。
  3. 其他类型记录：RN统一设为NULL。

实现查询 #

WITH project_data AS (
    SELECT 
        Project_Num AS MSN,
        Project_Type,
        Project_Date,
        -- 对A/B类型记录按项目分组、日期排序生成行号
        ROW_NUMBER() OVER (PARTITION BY Project_Num ORDER BY Project_Date) 
            FILTER (WHERE Project_Type IN ('A', 'B')) AS ab_rn,
        -- 对E类型记录按项目分组、日期排序生成行号
        ROW_NUMBER() OVER (PARTITION BY Project_Num ORDER BY Project_Date) 
            FILTER (WHERE Project_Type = 'E') AS e_rn,
        -- 标记该项目是否存在A/B类型记录
        MAX(CASE WHEN Project_Type IN ('A', 'B') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Project_Num) AS has_ab,
        -- 计算该项目A/B类型的首次出现日期
        MIN(CASE WHEN Project_Type IN ('A', 'B') THEN Project_Date END) 
            OVER (PARTITION BY Project_Num) AS first_ab_date,
        -- 计算该项目E类型的首次出现日期
        MIN(CASE WHEN Project_Type = 'E' THEN Project_Date END) 
            OVER (PARTITION BY Project_Num) AS first_e_date
    FROM your_table_name -- 替换为实际表名
)
SELECT 
    MSN,
    Project_Type AS "Project Type",
    CASE
        -- A/B类型：首次出现则RN=1，否则NULL
        WHEN Project_Type IN ('A', 'B') AND ab_rn = 1 THEN 1
        -- E类型：按条件判断RN值
        WHEN Project_Type = 'E' THEN 
            CASE
                WHEN has_ab = 0 AND e_rn = 1 THEN 2
                WHEN has_ab = 1 AND first_e_date < first_ab_date AND e_rn = 1 THEN 2
                ELSE NULL
            END
        -- 其他类型：RN=NULL
        ELSE NULL
    END AS RN
FROM project_data
ORDER BY MSN, Project_Date;

逻辑说明 #

1. CTE部分：通过窗口函数和聚合函数，提前计算每个项目的关键标记（是否有A/B、A/B和E的首次出现日期、A/B和E各自的行号），简化后续主查询的条件判断。
2. 主查询CASE判断：
   • 直接匹配A/B类型的首次出现场景，赋值RN=1；
   • 对E类型分场景判断，结合项目是否有A/B、E与A/B的出现顺序，决定是否赋值RN=2；
   • 其他类型直接返回NULL。

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