BigQuery跨引用身份识别无层级限制SQL查询方法咨询

假设你的表路径为your_project.your_dataset.identity_relation，查询语句如下：

WITH RECURSIVE identity_hierarchy AS (
    -- 锚点：筛选所有未被其他主标识引用的最终masterIdentity作为递归起点
    SELECT DISTINCT
        m.masterIdentity AS root_master,
        m.masterIdentity AS current_identity,
        0 AS depth
    FROM `your_project.your_dataset.identity_relation` m
    WHERE m.masterIdentity NOT IN (
        SELECT DISTINCT secondIdentity 
        FROM `your_project.your_dataset.identity_relation`
        WHERE secondIdentity IS NOT NULL
    )

    UNION ALL

    -- 递归遍历：逐层匹配所有直接/间接关联的二级标识
    SELECT
        ih.root_master,
        r.secondIdentity AS current_identity,
        ih.depth + 1 AS depth
    FROM identity_hierarchy ih
    JOIN `your_project.your_dataset.identity_relation` r
        ON ih.current_identity = r.masterIdentity
    -- 规避循环引用导致的无限递归
    WHERE r.secondIdentity NOT IN (
        SELECT current_identity 
        FROM identity_hierarchy 
        WHERE root_master = ih.root_master
    )
)

-- 输出最终结果：去重后每个最终主标识对应的所有关联标识
SELECT DISTINCT
    root_master AS final_master_identity,
    current_identity AS related_identity
FROM identity_hierarchy
-- 若不需要包含最终主标识自身，可取消下面注释
-- WHERE current_identity != root_master
ORDER BY root_master, current_identity;

逻辑说明 #

• 最终主标识筛选：通过判断masterIdentity未出现在任何行的secondIdentity字段中，保证该标识是没有被其他主标识关联的顶层标识
• 无层级递归：从顶层主标识出发，逐层向下关联所有子标识，没有递归深度限制
• 循环防护：递归条件中加入重复值校验，避免A→B、B→A这类循环关联场景导致的无限递归
• 结果说明：final_master_identity为你需要的最终主标识，related_identity包含该主标识本身及所有直接/间接关联的二级标识

性能优化建议 #

• 大表查询时将锚点部分的NOT IN替换为NOT EXISTS，可大幅提升查询效率：

WHERE NOT EXISTS (
    SELECT 1 FROM `your_project.your_dataset.identity_relation` r2 
    WHERE r2.secondIdentity = m.masterIdentity
)

• 提前为表的masterIdentity和secondIdentity字段设置聚类规则，可加速关联查询速度
• 若数据量极大且允许近似结果，可设置递归最大深度限制，添加WHERE ih.depth < 100这类条件控制递归次数

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