Google BigQuery中SQL父子层级全量映射关系查询问题

原有查询问题说明 #

你之前的写法仅通过一次join最多只能获取两层关联关系，且字段映射顺序错误，没有循环遍历多层级结构，因此无法得到跨三层及以上的祖先-后代配对，比如A到E1、A到D1这类跨层级的映射都会缺失。

正确查询语句 #

WITH RECURSIVE ancestor_descendant AS (
  -- 锚点成员：取直接父子关联作为第一层映射
  SELECT 
    PARENT AS ancestor,
    CHILD AS descendant
  FROM `TREE`
  
  UNION ALL
  
  -- 递归成员：逐层关联更深层级的后代
  SELECT 
    a.ancestor,
    t.CHILD AS descendant
  FROM ancestor_descendant a
  INNER JOIN `TREE` t 
    ON a.descendant = t.PARENT
)
-- 转换为需求要求的输出列格式
SELECT 
  descendant AS CHILD,
  ancestor AS PARENT
FROM ancestor_descendant
ORDER BY PARENT, CHILD;

逻辑说明 #

• 锚点成员先加载所有直接的父子配对，作为递归的起始数据
• 递归部分每次将上一轮查询得到的后代，作为下一轮的父节点关联原表，匹配到更深一层的后代后，继承上一轮的祖先节点，以此类推直到没有更深的后代可以匹配
• 最终输出的结果和你期望的全层级映射关系完全一致

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