如何在BigQuery的SQL中解析缩进式层级分类列

问题描述 #

现有BigQuery表数据如下：

其中分类通过前置缩进定义层级关系，层级数量不固定。需要将category列展平为完整的层级路径，期望输出如下：

SQL解决方案 #

可以通过递归CTE实现该需求，利用缩进长度判断层级关系，逐步拼接路径：

WITH preprocessed AS (
  SELECT
    id,
    category,
    TRIM(category) AS clean_category,
    -- 计算前置空格数，用于判断层级
    LENGTH(category) - LENGTH(TRIM(category)) AS indent_length
  FROM your_table -- 替换为你的实际表名
),
recursive_hierarchy AS (
  -- 初始化：选取所有根节点（无前置空格的行）
  SELECT
    id,
    clean_category AS category_flatten,
    indent_length,
    id AS root_id
  FROM preprocessed
  WHERE indent_length = 0

  UNION ALL

  -- 递归逻辑：匹配当前节点的直接子节点并拼接路径
  SELECT
    p.id,
    CONCAT(r.category_flatten, ' > ', p.clean_category) AS category_flatten,
    p.indent_length,
    r.root_id
  FROM preprocessed p
  JOIN recursive_hierarchy r
    ON p.id > r.id
    -- 子节点缩进必须大于父节点
    AND p.indent_length > r.indent_length
    -- 确保父节点是当前节点之前最近的层级更高的节点（无中间层级节点）
    AND NOT EXISTS (
      SELECT 1
      FROM preprocessed p2
      WHERE p2.id BETWEEN r.id AND p.id
        AND p2.indent_length < p.indent_length
        AND p2.indent_length > r.indent_length
    )
)
-- 按id排序输出最终结果
SELECT id, category_flatten
FROM recursive_hierarchy
ORDER BY id;

代码说明 #

1. 预处理阶段：清理分类名称（去除前后空格），计算每行的前置空格数，以此作为层级判断的依据。
2. 递归CTE初始化：选取所有没有前置空格的根节点，初始路径为自身的干净分类名称。
3. 递归匹配：通过JOIN找到每个节点的直接子节点——子节点的ID必须在父节点之后，缩进长度更大，且两者之间没有层级介于中间的节点（保证是直接父子关系），然后将父节点的路径与当前节点名称拼接。
4. 最终输出：按ID排序，得到展平后的层级路径。

该方案支持动态层级数量，只要缩进的相对关系正确即可正常工作。

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