如何按ID分组，依据Class优先级填充新增的Final Class列？

问题描述 #

现有如下结构的数据集：

ID  Class
11111   A
11111   C
11111   B
22222   B
22222   C
33333   A
33333   D
33333   C
33333   B
44444   A

需要新增一列「Final Class」，规则是：对同一ID下的Class按 A＞B＞C＞D 的优先级，将该ID所有行的「Final Class」设为对应最高优先级的Class。最终效果示例：

ID  Class   Final Class
11111   A   A
11111   C   A
11111   B   A
22222   B   B
22222   C   B
33333   A   A
33333   D   A
33333   C   A
33333   B   A
44444   A   A

解决方案 #

核心思路是先给每个Class按优先级赋予排序权重，再通过分组计算同一ID下的最高优先级类别，最后关联回原表。以下是几种通用实现方式：

方法1：使用窗口函数 FIRST_VALUE #

利用窗口函数按ID分组，同时按自定义优先级排序，取每组的第一个值作为Final Class：

SELECT 
    ID,
    Class,
    FIRST_VALUE(Class) OVER (
        PARTITION BY ID 
        ORDER BY 
            CASE Class 
                WHEN 'A' THEN 1 
                WHEN 'B' THEN 2 
                WHEN 'C' THEN 3 
                WHEN 'D' THEN 4 
            END ASC
    ) AS `Final Class`
FROM your_table_name;

这里通过CASE给优先级高的Class分配更小的排序值，ORDER BY ASC会让A排在最前，FIRST_VALUE就会取到该ID下的最高优先级类别。

方法2：使用聚合函数+子查询 #

先按ID分组计算每个ID的最高优先级类别，再和原表关联：

WITH class_priority AS (
    SELECT 
        ID,
        MAX(CASE 
            WHEN Class = 'A' THEN 4 
            WHEN Class = 'B' THEN 3 
            WHEN Class = 'C' THEN 2 
            WHEN Class = 'D' THEN 1 
        END) AS max_priority
    FROM your_table_name
    GROUP BY ID
),
priority_map AS (
    SELECT 'A' AS Class, 4 AS priority UNION ALL
    SELECT 'B' AS Class, 3 AS priority UNION ALL
    SELECT 'C' AS Class, 2 AS priority UNION ALL
    SELECT 'D' AS Class, 1 AS priority
)
SELECT 
    t.ID,
    t.Class,
    pm.Class AS `Final Class`
FROM your_table_name t
JOIN class_priority cp ON t.ID = cp.ID
JOIN priority_map pm ON cp.max_priority = pm.priority;

这种方法先计算每个ID的最高优先级数值，再通过映射表转回对应的Class值，适合不支持窗口函数的老版本数据库。

方法3：使用窗口函数 MAX 结合自定义排序 #

直接在SELECT中通过CASE转换为优先级数值，取最大值后再转回Class：

SELECT 
    ID,
    Class,
    CASE 
        WHEN MAX(CASE Class WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 END) OVER (PARTITION BY ID) = 4 THEN 'A'
        WHEN MAX(CASE Class WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 END) OVER (PARTITION BY ID) = 3 THEN 'B'
        WHEN MAX(CASE Class WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 WHEN 'D' THEN 1 END) OVER (PARTITION BY ID) = 2 THEN 'C'
        ELSE 'D'
    END AS `Final Class`
FROM your_table_name;

这种方式无需子查询，直接在主查询中完成计算，逻辑直观。

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