Teradata SQL按种族优先级对ID分组取唯一值的实现咨询

原始数据 #

Id.         Race
101         White
101         Hispanic
101         Black 
101       Not provided
202      Hispanic
202      Black
202       Asian
303       Not provided 
303       Hispanic
404       Other 
404      not provided 

需求说明 #

为每个ID保留唯一一行记录，严格遵循以下种族优先级：

• White（最高优先级）
• Hispanic
• Black
• Asian
• 若仅存在Not provided或Other（含大小写变体），则显示Null

期望输出 #

ID    race
101  White
202  Hispanic
303  Hispanic 
404  Null

解决方案 #

通过窗口函数ROW_NUMBER()结合自定义优先级排序即可实现，核心逻辑是给每个ID下的种族按优先级分配排序号，再筛选出每个ID的最高优先级记录：

SQL代码示例 #

WITH ranked_races AS (
    SELECT 
        "Id." AS ID,
        -- 先将低优先级值转为Null
        CASE 
            WHEN LOWER(Race) IN ('not provided', 'other') THEN NULL
            ELSE Race
        END AS race,
        -- 按优先级给每个ID下的种族分配排序号
        ROW_NUMBER() OVER (
            PARTITION BY "Id." 
            ORDER BY 
                CASE Race 
                    WHEN 'White' THEN 1
                    WHEN 'Hispanic' THEN 2
                    WHEN 'Black' THEN 3
                    WHEN 'Asian' THEN 4
                    ELSE 5 -- 低优先级值排最后
                END
        ) AS rn
    FROM your_table_name
)
SELECT ID, race
FROM ranked_races
WHERE rn = 1; -- 取每个ID的最高优先级记录

逻辑拆解 #

1. CTE子查询ranked_races：
   • 统一处理大小写，将Not provided/Other类值转为Null
   • 用PARTITION BY "Id."按ID分组，通过CASE语句定义优先级排序规则，优先级越高的种族排序号越小
2. 主查询：筛选每个ID中排序号为1的记录，即为该ID符合要求的唯一结果

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