如何在BigQuery中基于分类标签数组长度生成多分类结果？

以下是符合需求的SQL代码，核心思路是先拆分字段为数组，再根据数组长度生成对应的分类类型和名称，最后通过UNNEST将多列转为多行：

WITH processed_data AS (
  SELECT
    -- 拆分category_tags为数组，处理空值避免报错
    SPLIT(IFNULL(category_tags, ''), '|') AS category_array,
    -- 计算数组实际长度
    ARRAY_LENGTH(SPLIT(IFNULL(category_tags, ''), '|')) AS arr_length
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的实际表路径
)
SELECT
  category_type,
  category_name
FROM processed_data,
UNNEST(
  CASE arr_length
    -- 数组长度为0时，生成parent类型的archaeology记录
    WHEN 0 THEN [STRUCT('parent' AS category_type, 'archaeology' AS category_name)]
    -- 数组长度为1时，生成parent类型的单值记录
    WHEN 1 THEN [STRUCT('parent' AS category_type, category_array[OFFSET(0)] AS category_name)]
    -- 数组长度为2时，生成child和parent两条记录
    WHEN 2 THEN [
      STRUCT('child' AS category_type, category_array[OFFSET(0)] AS category_name),
      STRUCT('parent' AS category_type, category_array[OFFSET(1)] AS category_name)
    ]
    -- 数组长度为3时，生成grandchild、parent、child三条记录
    WHEN 3 THEN [
      STRUCT('grandchild' AS category_type, category_array[OFFSET(0)] AS category_name),
      STRUCT('parent' AS category_type, category_array[OFFSET(1)] AS category_name),
      STRUCT('child' AS category_type, category_array[OFFSET(2)] AS category_name)
    ]
    -- 数组长度为4时，生成greatgrandchild、parent、child、grandchild四条记录
    WHEN 4 THEN [
      STRUCT('greatgrandchild' AS category_type, category_array[OFFSET(0)] AS category_name),
      STRUCT('parent' AS category_type, category_array[OFFSET(1)] AS category_name),
      STRUCT('child' AS category_type, category_array[OFFSET(2)] AS category_name),
      STRUCT('grandchild' AS category_type, category_array[OFFSET(3)] AS category_name)
    ]
    -- 处理其他意外长度（可根据需求调整逻辑）
    ELSE []
  END
) AS categories;

代码说明 #

• processed_data 公共表表达式：统一处理category_tags字段，拆分出数组并计算长度，用IFNULL规避空值拆分时的异常。
• UNNEST结合CASE：根据数组长度生成对应结构的数组，再展开为单行单类型的记录，完全匹配你要求的输出格式。
• 表路径替换：请将代码中的your-project.your-dataset.your-table替换为你实际的BigQuery表路径。

示例验证 #

假设表中有一条记录category_tags = 'orcas|Animals|marine mammal|dolphins'，运行代码后会生成4行结果：

若有一条空值记录（category_tags为NULL或空字符串），则生成：

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