如何在BigQuery中生成完整的父子实体关系层级路径？

要解决这个树形层级路径生成的问题，我们可以利用**递归CTE（Common Table Expressions）**来遍历实体间的父子关系，直到找到最终父节点（mapp表中type='app'的节点，或者没有父节点的节点）。以下是具体的实现方案：

解决方案代码 #

WITH RECURSIVE entity_paths AS (
    -- 锚点成员：初始化每个子实体的路径，记录原始子节点和当前节点
    SELECT
        r.child_id AS original_child,
        r.child_id AS current_node,
        r.parent_id AS next_parent,
        CAST(r.child_id AS VARCHAR) AS path
    FROM rell r
    UNION ALL
    -- 递归成员：遍历父节点，直到找到最终父节点或无父节点
    SELECT
        ep.original_child,
        ep.next_parent AS current_node,
        r.parent_id AS next_parent,
        CONCAT(ep.path, '>', ep.next_parent) AS path
    FROM entity_paths ep
    LEFT JOIN rell r ON ep.next_parent = r.child_id
    -- 终止条件：当前节点是最终父节点（type='app'），或者没有下一个父节点时停止递归
    WHERE (SELECT type FROM mapp WHERE item_id = ep.next_parent) != 'app'
        AND ep.next_parent IS NOT NULL
)
-- 最终查询：提取每个原始子节点的完整路径和最终父节点
SELECT
    original_child,
    -- 最终父节点是路径的最后一个节点
    SPLIT_PART(path, '>', -1) AS final_parent,
    path
FROM entity_paths
-- 只保留递归到最终节点的记录
WHERE (SELECT type FROM mapp WHERE item_id = current_node) = 'app'
    OR next_parent IS NULL
ORDER BY original_child;

代码解释 #

1. 递归CTE锚点：从rell表中取出所有子节点，初始化路径为子节点自身，同时跟踪原始子节点、当前节点和下一个父节点。
2. 递归CTE迭代：每次将当前节点的父节点追加到路径末尾，继续向上遍历，直到当前节点是type='app'的最终父节点，或者没有更多父节点为止。
3. 最终结果提取：从递归结果中筛选出到达最终节点的记录，通过SPLIT_PART提取路径的最后一个节点作为final_parent，并按原始子节点排序。

运行结果 #

执行上述代码后，会得到你期望的输出：

这个方案可以灵活处理任意深度的层级关系，同时准确识别最终父节点。

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