如何在Snowflake中按最高层级匹配关联SQL分层数据表？

问题核心 #

Table A每行存储完整的三层层级数据，Table B则按层级拆分存储（低层级为空时代表匹配当前及以下所有子层级）。需要为Table A的每一行，匹配Table B中最细分的有效层级对应的Category，优先级为：Level3完全匹配 > Level2完全匹配（B的Level3为空） > Level1匹配（B的Level2、3为空）。

方案一：窗口函数筛选最高优先级匹配 #

通过建立所有有效匹配，再按优先级排序取最优结果，适合数据量较大的场景，性能更稳定。

完整SQL代码 #

WITH matched_data AS (
    SELECT 
        a.row AS a_row,
        a.level_1,
        a.level_2,
        a.level_3,
        b.category,
        -- 标记匹配层级的优先级：数字越大优先级越高
        CASE
            WHEN b.level_3 IS NOT NULL AND a.level_1 = b.level_1 AND a.level_2 = b.level_2 AND a.level_3 = b.level_3 THEN 3
            WHEN b.level_2 IS NOT NULL AND b.level_3 IS NULL AND a.level_1 = b.level_1 AND a.level_2 = b.level_2 THEN 2
            WHEN b.level_1 IS NOT NULL AND b.level_2 IS NULL AND b.level_3 IS NULL AND a.level_1 = b.level_1 THEN 1
            ELSE 0
        END AS match_priority
    FROM TABLE_A a
    LEFT JOIN TABLE_B b 
        ON a.level_1 = b.level_1 
        AND (a.level_2 = b.level_2 OR b.level_2 IS NULL)
        AND (a.level_3 = b.level_3 OR b.level_3 IS NULL)
    WHERE match_priority > 0
),
ranked_matches AS (
    SELECT 
        a_row,
        level_1,
        level_2,
        level_3,
        category,
        -- 按优先级降序排序，每个A行只留第一行（最高优先级匹配）
        ROW_NUMBER() OVER (PARTITION BY a_row ORDER BY match_priority DESC) AS rn
    FROM matched_data
)
SELECT 
    a_row AS row,
    level_1,
    level_2,
    level_3,
    category
FROM ranked_matches
WHERE rn = 1
ORDER BY row;

代码说明 #

1. matched_data CTE：筛选所有符合层级规则的有效匹配，并为每个匹配标记优先级，排除无效匹配。
2. ranked_matches CTE：用ROW_NUMBER()窗口函数按A行分组，按优先级降序排序，确保最高优先级的匹配排在第一位。
3. 最终查询：提取每个A行的第一行结果，即为所需的最高层级Category。

方案二：COALESCE嵌套子查询 #

代码更简洁，适合数据量较小的场景，逻辑直观但性能略逊于窗口函数方案。

完整SQL代码 #

SELECT
    a.row,
    a.level_1,
    a.level_2,
    a.level_3,
    COALESCE(
        -- 优先匹配Level3全层级
        (SELECT b.category FROM TABLE_B b WHERE b.level_1 = a.level_1 AND b.level_2 = a.level_2 AND b.level_3 = a.level_3),
        -- 其次匹配Level2层级（B的Level3为空）
        (SELECT b.category FROM TABLE_B b WHERE b.level_1 = a.level_1 AND b.level_2 = a.level_2 AND b.level_3 IS NULL),
        -- 最后匹配Level1层级（B的Level2、3为空）
        (SELECT b.category FROM TABLE_B b WHERE b.level_1 = a.level_1 AND b.level_2 IS NULL AND b.level_3 IS NULL)
    ) AS category
FROM TABLE_A a
ORDER BY a.row;

代码说明 #

利用COALESCE函数的特性：从左到右依次执行子查询，返回第一个非NULL的结果，正好对应从高到低的层级匹配优先级。

验证结果 #

两种方案都能得到预期输出：

Row    Level 1     Level 2     Level 3     Category
---------------------------------------------------
1      Animal      Mammal      Dog         D
2      Animal      Mammal      Cat         M
3      Animal      Reptile     Lizard      L
4      Animal      Reptile     Snake       R
5      Tree        Oak         Live Oak    LO
6      Tree        Elm         Cedar Elm   T 

内容的提问来源于stack exchange，提问作者j.smith