Snowflake中提取每行排名前三颜色的SQL实现（规避CASE语句）

表结构与数据 #

颜色数据表（color_data）

颜色排名表（color_rank）

需求 #

对color_data的每一行，提取排名前三的颜色（rank值越小排名越高，并列时可任选其一），且无需手动编写大量CASE语句适配多列场景。

解决方案 #

核心思路是通过UNPIVOT将宽表转长表，关联排名后再PIVOT转回宽表，全程用SQL内置函数实现，避免硬编码列名：

WITH numbered_rows AS (
    -- 给原始数据每行加唯一标识，用于后续分组
    SELECT *, ROW_NUMBER() OVER (ORDER BY NULL) AS row_id
    FROM color_data
),
unpivoted_colors AS (
    -- 把宽表转成每行一个颜色的长表
    SELECT row_id, color
    FROM numbered_rows
    UNPIVOT (
        color FOR type_col IN (Type1, Type2, Type3, Type4, Type5)
    )
),
ranked_colors AS (
    -- 关联排名表，给每行的颜色按rank排序
    SELECT 
        uc.row_id,
        uc.color,
        cr.rank,
        -- 按rank升序排序，并列时随机选（ROW_NUMBER保证唯一顺序）
        ROW_NUMBER() OVER (PARTITION BY uc.row_id ORDER BY cr.rank ASC) AS color_order
    FROM unpivoted_colors uc
    JOIN color_rank cr ON uc.color = cr.color
)
-- 把排序后的前三个颜色转成目标宽表
SELECT 
    MAX(CASE WHEN color_order = 1 THEN color END) AS Highest,
    MAX(CASE WHEN color_order = 2 THEN color END) AS Second,
    MAX(CASE WHEN color_order = 3 THEN color END) AS Third
FROM ranked_colors
GROUP BY row_id
ORDER BY row_id;

说明 #

1. numbered_rows：生成行唯一标识，确保后续分组能对应到原始数据的每一行
2. unpivoted_colors：将Type1-Type5列统一转成color列，适配任意数量的Type列（只需修改UNPIVOT中的列名列表）
3. ranked_colors：关联排名后，给每行的颜色按rank排序，用ROW_NUMBER处理并列情况
4. 最后通过简单的CASE聚合，提取前三个排序后的颜色，避免了手动编写大量判断逻辑

执行结果 #

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