如何从manufacturer_status表按优先级提取组织目标数据

问题说明 #

现有数据表manufacturer_status，包含Organization、Year、Target三列，具体数据：

• ABC：2012年Achieved、2014年Partial、2015年Failed
• XYZ：2014年Partial、2015年Failed
• OPQ：2012年Failed

需求明确：为每个组织优先提取Target为“Achieved”的记录；若无该状态，则提取“Partial”记录；两者均无时不提取。预期输出仅保留ABC（2012年Achieved）和XYZ（2014年Partial）的记录。

此前尝试的SQL存在缺陷：

• Select * from manufacturer_status where target='Achieved'：只能获取ABC的记录，遗漏了XYZ的Partial记录
• Select * from manufacturer_status where target in ('Achieved', 'Partial')：会让ABC出现两条重复记录，不符合需求

解决方案 #

使用窗口函数给每条记录按Target优先级排序，再筛选每个组织的第一条记录，具体SQL如下：

WITH ranked_records AS (
    SELECT 
        *,
        -- 定义Target优先级：Achieved排1，Partial排2
        ROW_NUMBER() OVER (
            PARTITION BY Organization 
            ORDER BY CASE Target 
                WHEN 'Achieved' THEN 1 
                WHEN 'Partial' THEN 2 
                ELSE 3 
            END
        ) AS rn
    FROM manufacturer_status
    -- 先过滤掉无用的Failed记录，减少计算量
    WHERE Target IN ('Achieved', 'Partial')
)
SELECT Organization, Year, Target
FROM ranked_records
WHERE rn = 1;

逻辑解释 #

1. 通过CASE语句给Target设置优先级，确保Achieved的优先级最高，Partial次之
2. 用ROW_NUMBER()窗口函数按Organization分组，按优先级排序，每个组内的第一条记录（rn=1）就是符合需求的目标记录
3. 提前过滤掉Failed记录，能提升查询效率

执行后将得到预期结果：

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