如何在BigQuery中按指定规则排序父子行？

问题说明 #

需要在BigQuery中实现子项在前、父项在后的父子行排序，具体要求参考以下输入和预期输出：

输入数据 #

Child | Parent |
YA011 | YA010 | 
YA012 | YA010 |
YA022 | YA020 |
YA030 | YA020 |
YA021 | YA020 |
YA190 | YA000 |
YA200 | YA190 |
YA010 | YA200 |
YA020 | YA200 |

预期输出 #

Child | Parent | 
YA011 | YA010 |              1st child of YA010
YA012 | YA010 |              2nd child of YA010
YA010 | YA200 |              Then YA010
YA021 | YA020 |              1st child of YA020
YA022 | YA020 |              2nd child of YA020
YA030 | YA020 |              3rd child of YA020
YA020 | YA200 |              Then YA020
YA200 | YA190 |              Then parent of YA010 and YA020 i.e., YA200
YA190 | YA000 |              Then parent of YA200 i.e, YA190

原查询的问题在于仅通过level和child排序，无法实现子项优先于父项的顺序逻辑，需要调整递归路径的生成方式。

正确实现查询 #

WITH RECURSIVE hierarchy AS (
  -- 定位所有叶子节点（无下属子节点的节点）
  SELECT 
    child, 
    parent,
    [child] AS sort_path,
    TRUE AS is_leaf
  FROM temp2 t1
  WHERE NOT EXISTS (SELECT 1 FROM temp2 t2 WHERE t2.parent = t1.child)
  
  UNION ALL
  
  -- 递归向上遍历父节点，将父节点追加到排序路径末尾
  SELECT 
    t.child,
    t.parent,
    h.sort_path || [t.child] AS sort_path,
    FALSE AS is_leaf
  FROM hierarchy h
  JOIN temp2 t ON h.parent = t.child
),
-- 补充未被递归覆盖的非叶子节点
additional_nodes AS (
  SELECT 
    child, 
    parent,
    [child] AS sort_path
  FROM temp2 t
  WHERE NOT EXISTS (SELECT 1 FROM hierarchy h WHERE h.child = t.child)
),
-- 合并所有节点的排序路径
all_nodes AS (
  SELECT child, parent, sort_path FROM hierarchy
  UNION ALL
  SELECT child, parent, sort_path FROM additional_nodes
),
-- 选取每个节点的最长排序路径（确保覆盖完整层级）
final_nodes AS (
  SELECT 
    child, 
    parent,
    sort_path
  FROM all_nodes
  QUALIFY ROW_NUMBER() OVER (PARTITION BY child ORDER BY ARRAY_LENGTH(sort_path) DESC) = 1
)
-- 按生成的排序路径实现子项在前的排序
SELECT child, parent
FROM final_nodes
ORDER BY sort_path;

逻辑说明 #

1. 叶子节点递归：从没有子节点的叶子节点出发，向上递归遍历父节点，生成「子节点→父节点」的路径，保证排序时子节点优先级高于父节点。
2. 补充非叶子节点：对那些本身有子节点但未被叶子节点递归到的节点（如YA200），单独生成排序路径。
3. 最长路径筛选：确保每个节点使用覆盖完整层级的最长路径，避免排序逻辑出错。
4. 路径排序：直接按sort_path排序，自然实现子项在前、父项在后的顺序，同时同一父节点下的子节点会按名称顺序排列。

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