在Teradata中根据列重复情况新增列查询表的实现方法

原表数据 #

目标结果 #

实现SQL #

SELECT 
    Id,
    Type,
    -- 判断当前Id是否同时存在A和B类型
    CASE 
        WHEN MAX(CASE WHEN Type = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY Id) = 1 
             AND MAX(CASE WHEN Type = 'B' THEN 1 ELSE 0 END) OVER (PARTITION BY Id) = 1 
        THEN 1 
        ELSE 0 
    END AS "Id with both A+B",
    -- 判断当前Id是否仅存在A类型
    CASE 
        WHEN MAX(CASE WHEN Type = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY Id) = 1 
             AND MAX(CASE WHEN Type = 'B' THEN 1 ELSE 0 END) OVER (PARTITION BY Id) = 0 
        THEN 1 
        ELSE 0 
    END AS "Id with only A"
FROM your_table_name;

逻辑说明 #

1. 利用窗口聚合函数MAX() OVER (PARTITION BY Id)统计每个Id下的类型分布：
   • 对Type='A'的行标记1，其余标记0，取最大值得到该Id是否包含A（1=存在，0=不存在）
   • 同理统计该Id是否包含B
2. 通过外层CASE WHEN组合两个统计结果，生成目标标识列：
   • 当Id同时包含A和B时，"Id with both A+B"设为1，否则为0
   • 当Id仅包含A、不包含B时，"Id with only A"设为1，否则为0

这种方式无需额外关联子查询，直接在原表基础上通过窗口函数计算，适配Teradata的分布式架构，执行效率更高。

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