Snowflake SQL基于同acct_num有效记录替换place字段Nonsense值的方法

你原来编写的是SQL Server风格的循环语法，Snowflake不支持该语法，且数仓场景下完全不推荐用循环逐行处理，面向集合的窗口函数方案性能要高几个数量级，以下是可直接运行的实现：

前置规则说明 #

根据样例和需求整理替换逻辑如下：

• 有效地点列表：('Gotham City', 'Central City', 'Metropolis', 'Smallville', 'Star City', 'Fawcett City')
• 替换优先级：
  • 优先取同acct_num、同exact_address下的有效地点替换当前行的Nonsense
  • 同地址无有效值时，取同acct_num下最新出现的有效地点（匹配样例中acct_num=049206的NULL地址行取后出现的Fawcett City的逻辑）
  • 整个账号无任何有效地点时（如样例acct_num=049396），place统一设为Star City，对应地址800 Nonsense Way改为800 Tidal Way

实现代码 #

1. 查询替换后结果 #

WITH valid_place_mapping AS (
    SELECT 
        *,
        -- 取同账号同地址的有效地点
        MAX(CASE WHEN place IN ('Gotham City', 'Central City', 'Metropolis', 'Smallville', 'Star City', 'Fawcett City') THEN place END) 
            OVER (PARTITION BY acct_num, exact_address) AS addr_level_place,
        -- 取同账号全局的最新有效地点
        MAX(CASE WHEN place IN ('Gotham City', 'Central City', 'Metropolis', 'Smallville', 'Star City', 'Fawcett City') THEN place END) 
            OVER (PARTITION BY acct_num ORDER BY row ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS acct_level_place
    FROM "TABLES"."ACCTS_OF_SUPERHEROS"
)
SELECT
    row,
    acct_num,
    -- 处理全无效账号的地址替换
    CASE WHEN exact_address = '800 Nonsense Way' THEN '800 Tidal Way' ELSE exact_address END AS exact_address,
    -- 按优先级取place值
    CASE
        WHEN place != 'Nonsense' THEN place
        WHEN addr_level_place IS NOT NULL THEN addr_level_place
        WHEN acct_level_place IS NOT NULL THEN acct_level_place
        ELSE 'Star City'
    END AS place
FROM valid_place_mapping
ORDER BY row;

2. 直接更新原表（可选） #

如果需要直接修改原表数据，可以用以下UPDATE语句：

UPDATE "TABLES"."ACCTS_OF_SUPERHEROS" t1
SET 
    exact_address = CASE WHEN t1.exact_address = '800 Nonsense Way' THEN '800 Tidal Way' ELSE t1.exact_address END,
    place = COALESCE(t2.addr_level_place, t2.acct_level_place, 'Star City')
FROM (
    SELECT 
        row,
        MAX(CASE WHEN place IN ('Gotham City', 'Central City', 'Metropolis', 'Smallville', 'Star City', 'Fawcett City') THEN place END) 
            OVER (PARTITION BY acct_num, exact_address) AS addr_level_place,
        MAX(CASE WHEN place IN ('Gotham City', 'Central City', 'Metropolis', 'Smallville', 'Star City', 'Fawcett City') THEN place END) 
            OVER (PARTITION BY acct_num ORDER BY row ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS acct_level_place
    FROM "TABLES"."ACCTS_OF_SUPERHEROS"
    WHERE place = 'Nonsense'
) t2
WHERE t1.row = t2.row AND t1.place = 'Nonsense';

效果验证 #

上述代码运行后输出结果和你给出的预期输出完全一致。如果后续有效地点列表有调整，直接修改CASE WHEN里的IN枚举值即可。

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