基于层级规则统一Snowflake表中ID对应Label的高效实现方案

现有如下数据表：

id  label   origin
1   G       Sales
2   K       Sales
3   G       Sales
4   K       Sales
1   K       Invoice
2   G       Invoice
3   K       Invoice
4   G       Invoice
5   G       Invoice
6   K       Invoice
1   K       Facts
2   G       Facts
3   G       Facts
4   K       Facts
6   G       Facts
7   G       Facts

需求：按照Sales > Invoice > Facts的层级优先级，为同一id的所有行统一分配label，最终结果如下：

id  label   origin
1   G       Sales
2   K       Sales
3   G       Sales
4   K       Sales
1   G       Sales
2   K       Sales
3   G       Sales
4   K       Sales
5   G       Invoice
6   K       Invoice
1   G       Sales
2   K       Sales
3   G       Sales
4   K       Sales
6   K       Invoice
7   G       Facts

实际表包含数百万行及大量不同id，使用CASE WHEN逐行判断的方式性能不足，需要在Snowflake中高效实现该需求。

在Snowflake中，利用窗口函数+关联查询的方式可以高效完成这个需求，避免逐行判断的性能损耗，适合大规模数据：

方法1：直接通过窗口函数筛选最高优先级Label #

这种方式代码简洁，适合固定优先级的场景：

WITH prioritized_records AS (
    SELECT
        id,
        label,
        -- 按优先级排序，给每个id的记录标记序号，最高优先级的记录序号为1
        ROW_NUMBER() OVER (
            PARTITION BY id
            ORDER BY CASE origin
                WHEN 'Sales' THEN 3
                WHEN 'Invoice' THEN 2
                WHEN 'Facts' THEN 1
            END DESC
        ) AS priority_rank
    FROM your_table_name
)
-- 关联原表，用每个id最高优先级的Label替换所有行的Label
SELECT
    t.id,
    pr.label AS label,
    t.origin
FROM your_table_name t
JOIN (
    SELECT id, label
    FROM prioritized_records
    WHERE priority_rank = 1
) pr ON t.id = pr.id
ORDER BY t.origin, t.id;

方法2：用优先级映射表实现（更易维护） #

如果后续优先级规则需要调整，建议用映射表来管理规则，扩展性更好：

-- 定义优先级映射，数值越大优先级越高
WITH origin_priority_map AS (
    SELECT 'Sales' AS origin, 3 AS priority UNION ALL
    SELECT 'Invoice' AS origin, 2 AS priority UNION ALL
    SELECT 'Facts' AS origin, 1 AS priority
),
top_label_per_id AS (
    SELECT
        id,
        -- 取每个id下优先级最高的Label
        FIRST_VALUE(t.label) OVER (
            PARTITION BY t.id
            ORDER BY opm.priority DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS unified_label
    FROM your_table_name t
    JOIN origin_priority_map opm ON t.origin = opm.origin
)
-- 关联原表替换Label
SELECT
    t.id,
    tl.unified_label AS label,
    t.origin
FROM your_table_name t
JOIN (SELECT DISTINCT id, unified_label FROM top_label_per_id) tl ON t.id = tl.id
ORDER BY t.origin, t.id;

性能说明 #

• 两种方法都通过分区窗口函数一次性计算出每个id的最高优先级Label，只需要扫描表1-2次，比CASE WHEN逐行判断的效率高得多。
• Snowflake的列存储和分布式计算架构会自动优化这类分组、窗口操作，即使是数百万行的数据也能快速处理。
• 如果id和origin字段有索引，查询性能会进一步提升。

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