如何在PrestoDB中将顶层层级提取为独立列（非递归）

给定层级数据表，顶层节点为PL-135，其子节点包含21-001、210-002、PL-76；PL-76同时作为25-001、25-002的父节点，且数据可能存在超过2层的嵌套结构。需要在PrestoDB中为每个节点提取顶层父节点作为独立列，且不能使用递归（Presto不支持递归CTE）。

输入示例（假设表名为hierarchy_table） #

期望输出 #

解决方案1：多次自连接（适合已知最大层级的场景） #

通过逐层自连接的方式，为每一层级的节点关联顶层节点。如果层级更深，只需继续扩展层级CTE即可。

-- 定义各层级数据，逐层关联顶层节点
WITH top_level AS (
    SELECT 
        node,
        parent_node,
        node AS top_node  -- 顶层节点的顶层就是自身
    FROM hierarchy_table
    WHERE parent_node IS NULL  -- 假设顶层节点的父节点为NULL，若有其他标识请调整条件
),
level_2 AS (
    SELECT 
        ht.node,
        ht.parent_node,
        tl.top_node
    FROM hierarchy_table ht
    JOIN top_level tl ON ht.parent_node = tl.node
),
level_3 AS (
    SELECT 
        ht.node,
        ht.parent_node,
        l2.top_node
    FROM hierarchy_table ht
    JOIN level_2 l2 ON ht.parent_node = l2.node
)
-- 合并所有层级的结果
SELECT * FROM top_level
UNION ALL
SELECT * FROM level_2
UNION ALL
SELECT * FROM level_3;

说明： #

• 如果数据有N层，就需要创建N个层级CTE，直到覆盖最深的节点
• 若顶层节点的父节点不是NULL（比如父节点等于自身），请修改top_level中的WHERE条件

解决方案2：数组路径累积（适合层级深度有限的场景） #

利用Presto的数组函数，手动构建每个节点的完整路径，再提取路径的第一个元素作为顶层节点。这种方法无需多次自连接，但层级过深时SQL会比较冗长。

SELECT
    node,
    parent_node,
    element_at(node_path, 1) AS top_node
FROM (
    SELECT
        node,
        parent_node,
        -- 逐层构建节点路径，根据实际层级扩展CASE分支
        CASE
            -- 顶层节点：路径仅包含自身
            WHEN parent_node IS NULL THEN ARRAY[node]
            -- 第二层节点：路径为[顶层节点, 当前节点]
            WHEN (SELECT parent_node FROM hierarchy_table WHERE node = ht.parent_node) IS NULL THEN ARRAY[ht.parent_node, node]
            -- 第三层节点：路径为[顶层节点, 父节点, 当前节点]
            ELSE ARRAY[
                (SELECT parent_node FROM hierarchy_table WHERE node = (SELECT parent_node FROM hierarchy_table WHERE node = ht.parent_node)),
                ht.parent_node,
                node
            ]
        END AS node_path
    FROM hierarchy_table ht
) t;

注意事项 #

• 两种方案都依赖于预先知道数据的最大层级，如果层级深度不确定且非常深，建议先通过查询统计最大层级：

  -- 统计每个节点的层级深度（非递归方式，需手动扩展）
  SELECT
      node,
      CASE
          WHEN parent_node IS NULL THEN 1
          WHEN (SELECT parent_node FROM hierarchy_table WHERE node = ht.parent_node) IS NULL THEN 2
          ELSE 3
      END AS depth
  FROM hierarchy_table ht;
  

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