在Snowflake中使用FLATTEN后为拆分记录分配ID的方法

你已经通过FLATTEN函数成功将逗号分隔的EMAIL_TO字段拆分为独立记录，现在只需借助窗口函数ROW_NUMBER()即可为每个ID分组下的拆分记录生成从1开始递增的NEW_DERIVED_ID。

源数据 #

期望结果 #

修改后的视图创建代码 #

CREATE OR REPLACE VIEW MYVIEW (
    ID,
    EMAIL_TO,
    NEW_DERIVED_ID
) AS (
    SELECT
        CM.ID,
        COLLATE(CAST(REPLACE(EMAIL_TO.VALUE, '"') AS VARCHAR), 'en-ci-trim') AS EMAIL_TO,
        -- 按ID分区，以FLATTEN返回的INDEX排序生成自增ID
        ROW_NUMBER() OVER (PARTITION BY CM.ID ORDER BY EMAIL_TO.INDEX) AS NEW_DERIVED_ID
    FROM MYSOURCE AS CM,
    LATERAL FLATTEN(INPUT => SPLIT(CM.EMAIL_TO, ',')) AS EMAIL_TO
    WHERE CM.EMAIL_TO IS NOT NULL
);

关键说明 #

• ROW_NUMBER() OVER (PARTITION BY CM.ID ORDER BY EMAIL_TO.INDEX)：
  • PARTITION BY CM.ID：确保每个ID分组单独计算序号
  • ORDER BY EMAIL_TO.INDEX：利用FLATTEN函数返回的INDEX字段（对应拆分前邮箱在原字符串中的位置）保证序号顺序和原逗号分隔顺序一致
• 修正了原代码中表别名与字段名的引用冲突问题

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