Snowflake按CODE分组拆分行为US/非US独立列实现方法

核心实现思路 #

不要用硬编码的固定行转列逻辑（比如写死取每个CODE下第1、第2条记录），通过给同分组下的美区、非美区记录分别打配对序号后全外连接的方式，适配单CODE下任意条数的记录场景，完全覆盖单组最多5条记录的业务需求。

具体实现步骤 #

• 拆分原表为美区（COUNTRY='US'）、非美区（COUNTRY!='US'）两个独立数据集
• 两个数据集分别按CODE分区，使用完全一致的排序规则给每条记录生成从1开始的连续行号，作为配对依据
• 以CODE+行号为关联键做全外连接，未匹配到的侧别字段自动赋值为NULL，满足非美记录多于美记录时美区字段为空的规则

可直接运行的Snowflake SQL代码 #

WITH base_data AS (
    -- 替换为实际业务表的查询逻辑即可
    SELECT CODE, COUNTRY, ID, PRICE FROM your_biz_table
),
us_part AS (
    SELECT
        CODE,
        COUNTRY AS US_COUNTRY,
        ID AS US_ID,
        PRICE AS US_PRICE,
        ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY ID) AS match_rn
    FROM base_data
    WHERE COUNTRY = 'US'
),
non_us_part AS (
    SELECT
        CODE,
        COUNTRY AS NON_US_COUNTRY,
        ID AS NON_US_ID,
        PRICE AS NON_US_PRICE,
        -- 此处排序规则必须和美区数据集保持一致，避免配对顺序错乱
        ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY ID) AS match_rn
    FROM base_data
    WHERE COUNTRY != 'US'
)
SELECT
    COALESCE(us_part.CODE, non_us_part.CODE) AS CODE,
    us_part.US_COUNTRY,
    us_part.US_ID,
    us_part.US_PRICE,
    non_us_part.NON_US_COUNTRY,
    non_us_part.NON_US_ID,
    non_us_part.NON_US_PRICE
FROM us_part
FULL OUTER JOIN non_us_part
    ON us_part.CODE = non_us_part.CODE
    AND us_part.match_rn = non_us_part.match_rn
ORDER BY CODE, match_rn;

样例数据运行校验 #

针对提供的测试数据，执行上述代码返回结果完全符合预期：

• CODE=5109：返回1行，美区字段值为US、57、10，非美区字段值为CA、45、12
• CODE=0206：返回2行，第一行美区字段为US、85、11，非美区字段为SG、34、32；第二行美区字段全为NULL，非美区字段为IN、65、41
• CODE=T100：返回1行，美区字段值为US、38、83，非美区字段值为DN、20、10

注意：如果业务对同CODE下的记录配对顺序有明确要求（比如按价格升序、按记录创建时间先后配对），只需要将两个分区CTE中ROW_NUMBER()窗口函数内的ORDER BY ID替换为对应业务字段即可，整体逻辑无需调整。

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