如何在Snowflake/SQL中为两列值关联关系分配原始ID

现有两组关联记录：第一组为111→222→333→444，第二组为1111→2222，输入表结构及数据如下：

期望输出为每组记录分配原始ID（即每组中ADJ_ID为NULL的CLAIM_ID），输出表如下：

实现方案：使用递归CTE追溯根节点 #

针对这种层级关联的记录，可通过Snowflake的递归公共表表达式（CTE）实现，核心思路是追溯每条记录的根节点（ADJ_ID为NULL的记录），并将根节点ID作为该组所有记录的ORG_ID。

示例SQL代码 #

WITH RECURSIVE claim_hierarchy AS (
    -- 锚点成员：提取所有根节点，ORG_ID为自身CLAIM_ID
    SELECT 
        CLAIM_ID AS ORG_ID,
        CLAIM_ID,
        ADJ_ID
    FROM your_table_name
    WHERE ADJ_ID IS NULL
    
    UNION ALL
    
    -- 递归成员：关联子节点，继承根节点的ORG_ID
    SELECT 
        ch.ORG_ID,
        t.CLAIM_ID,
        t.ADJ_ID
    FROM your_table_name t
    JOIN claim_hierarchy ch ON t.ADJ_ID = ch.CLAIM_ID
)
SELECT ORG_ID, CLAIM_ID, ADJ_ID
FROM claim_hierarchy
ORDER BY ORG_ID, CLAIM_ID;

代码说明 #

1. 锚点成员：筛选出所有ADJ_ID为NULL的记录，此时ORG_ID直接等于当前记录的CLAIM_ID，这就是每组的原始ID。
2. 递归成员：将原表中ADJ_ID与递归CTE中的CLAIM_ID关联，把所有子节点记录加入结果集，并继承对应根节点的ORG_ID。
3. 最终查询：从递归CTE中取出所有记录，按ORG_ID和CLAIM_ID排序后即可得到期望输出。

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