Snowflake中基于颜色排名表重排每行颜色并取TOP3的SQL实现

实现思路 #

先把Color表的宽表结构转成每行对应单个颜色的长表，关联Rank表拿到每个颜色的排名值，再按Index分组对颜色按排名排序取前三，最后把结果转回宽表格式。

假设表结构 #

• Color表：Index INT, color1 VARCHAR, color2 VARCHAR, color3 VARCHAR, color4 VARCHAR, color5 VARCHAR
• Rank表：color VARCHAR, rank_val INT

具体SQL代码 #

WITH unpivoted_colors AS (
    -- 把Color表的多列颜色转为行数据
    SELECT 
        Index,
        color_name,
        rank_val
    FROM Color
    UNPIVOT (
        color_name FOR color_col IN (color1, color2, color3, color4, color5)
    ) AS unpvt
    -- 关联Rank表获取对应颜色的排名值
    JOIN Rank r ON unpvt.color_name = r.color
),
ranked_colors AS (
    -- 按Index分组，根据排名值升序排序，标记每个颜色的行内排名
    SELECT 
        Index,
        color_name,
        ROW_NUMBER() OVER (PARTITION BY Index ORDER BY rank_val ASC) AS rn
    FROM unpivoted_colors
)
-- 将前三排名的颜色转回宽表列
SELECT 
    Index,
    MAX(CASE WHEN rn = 1 THEN color_name END) AS Rank1,
    MAX(CASE WHEN rn = 2 THEN color_name END) AS Rank2,
    MAX(CASE WHEN rn = 3 THEN color_name END) AS Rank3
FROM ranked_colors
WHERE rn <= 3
GROUP BY Index
ORDER BY Index;

代码说明 #

1. Unpivot转换：用UNPIVOT把每行的color1到color5拆成独立行，让每个颜色单独成一条记录，方便后续关联排名数据。
2. 关联排名表：通过颜色字段关联Rank表，获取每个颜色对应的排名数值。
3. 行内排序：用ROW_NUMBER()窗口函数，按Index分组后，以rank_val升序（数值越小排名越靠前）给每个颜色标记行内排名。
4. 转回宽表：用条件聚合MAX(CASE...)把排名1、2、3的颜色分别映射到Rank1、Rank2、Rank3列，得到目标结果结构。

补充说明 #

• 如果Color表存在Rank表未收录的颜色，或Rank表有Color表没有的颜色，可根据需求把JOIN改成LEFT JOIN，并处理NULL值。
• 若有多个颜色排名相同的场景，可替换ROW_NUMBER()为RANK()或DENSE_RANK()，具体根据并列排名的处理规则选择。

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