BigQuery中特定条件下父子关系排序问题及优化需求

需求说明 #

• 条件1：若后继父节点的Type字段为"Y"，则前驱节点成为父节点；
• 条件2：所有子节点先按升序输出，随后其父节点按升序输出，依此类推至上级父节点。

表结构与输入数据 #

表结构 #

Child | Parent | Type

输入数据 #

已完成步骤（实现条件1） #

已通过以下查询创建TEMP2表，实现条件1的逻辑：

CREATE TABLE TEMP2 AS (WITH RECURSIVE generation AS (
  SELECT child, parent, [parent] parents FROM sample_table
   UNION ALL
  SELECT g.child, t.parent, parents || [t.parent]
    FROM generation g
    JOIN sample_table t ON g.parent = t.child AND IFNULL(t.type, 'N') <> 'Y'
)
SELECT child, ARRAY_REVERSE(parents)[SAFE_OFFSET(0)] parent
  FROM generation
QUALIFY ROW_NUMBER() OVER (PARTITION BY child ORDER BY ARRAY_LENGTH(parents) DESC) = 1)

当前问题（条件2未实现） #

尝试用以下查询实现条件2，但输出不符合预期：

WITH RECURSIVE generation AS (
  SELECT child, parent, [parent] parents, 0 as level FROM temp2
   UNION ALL
  SELECT g.child, t.parent, parents || [t.parent] , level +1 as level
    FROM generation g
    JOIN temp2 t ON g.parent = t.child AND level <=9
), temp as (
SELECT child, ARRAY_REVERSE(parents)[SAFE_OFFSET(0)] parent, level
  FROM generation
QUALIFY ROW_NUMBER() OVER (PARTITION BY child ORDER BY ARRAY_LENGTH(parents) DESC) = 1 ) 
select * from temp
Order by level, child

当前输出 #

问题点 #

当前输出先列出所有子节点，再统一列父节点，但需求是每个父节点的所有子节点升序输出后，再输出该父节点，比如YA010应在其子节点YA011、YA012之后输出。

期望输出 #

解决方案 #

要实现条件2的输出顺序，需要在递归过程中生成排序路径，路径中先包含父节点的排序标识，再追加子节点的标识，确保排序时子节点先于父节点出现。

修改后的查询如下：

WITH RECURSIVE hierarchy AS (
  -- 从叶子节点开始（没有子节点的节点）
  SELECT 
    child, 
    parent,
    -- 生成排序键：父节点标识+分隔符+当前节点标识，避免冲突
    CONCAT(parent, '|', child) AS sort_key,
    TRUE AS is_leaf
  FROM TEMP2 t
  WHERE NOT EXISTS (SELECT 1 FROM TEMP2 WHERE parent = t.child)
  
  UNION ALL
  
  -- 向上递归处理父节点
  SELECT 
    t.child, 
    t.parent,
    -- 父节点排序键基于子节点的最小排序键前缀，保证父节点在子节点之后
    CONCAT(t.parent, '|', MIN(h.sort_key)),
    FALSE AS is_leaf
  FROM TEMP2 t
  JOIN hierarchy h ON t.child = h.parent
  GROUP BY t.child, t.parent
  
  -- 补充根节点（无父节点的节点）
  UNION ALL
  
  SELECT 
    child, 
    parent,
    CONCAT(parent, '|', child) AS sort_key,
    FALSE AS is_leaf
  FROM TEMP2
  WHERE parent NOT IN (SELECT child FROM TEMP2)
),
-- 整合所有节点（叶子+非叶子）
all_nodes AS (
  SELECT child, parent, sort_key, is_leaf FROM hierarchy
  UNION ALL
  SELECT child, parent, CONCAT(parent, '|', child) AS sort_key, FALSE AS is_leaf
  FROM TEMP2
  WHERE child NOT IN (SELECT child FROM hierarchy)
)
-- 最终排序输出
SELECT 
  child, 
  parent
FROM all_nodes
QUALIFY ROW_NUMBER() OVER (PARTITION BY child ORDER BY sort_key) = 1
ORDER BY 
  sort_key,
  -- 叶子节点优先于父节点
  CASE WHEN is_leaf THEN 0 ELSE 1 END,
  -- 子节点内部按升序排列
  child;

逻辑说明 #

1. 递归从叶子节点启动，为每个叶子节点生成包含父节点和自身的排序键；
2. 向上处理父节点时，父节点的排序键基于其子节点的最小排序键，确保父节点排在所有子节点之后；
3. 最后按排序键排序，同时让叶子节点优先于父节点，子节点内部按升序排列，完全匹配需求的输出顺序。

内容的提问来源于stack exchange，提问作者A.K.S