BigQuery递归聚合实现：按父ID聚合子ID数组的方法

没问题，我来帮你用BigQuery的函数实现这个需求。这里因为涉及到递归的父子层级关系（比如subscription_id=4的父是3，而3的父是1，所以4是1的间接子），我们需要用递归CTE来遍历整个层级，再聚合结果。

具体实现步骤 #

咱们可以通过三个核心步骤完成：

1. 用递归CTE找出每个根节点（subscription_source_id为NULL的节点）的所有直接/间接子节点
2. 提取所有根节点作为基础数据集
3. 关联层级数据，聚合子节点生成目标格式的结果

完整SQL代码 #

WITH recursive_hierarchy AS (
  -- 第一步：获取所有直接子节点，关联到对应的父节点
  SELECT
    subscription_source_id AS root_id,
    subscription_id AS child_id
  FROM
    `your_project.your_dataset.your_table`  -- 替换成你的表路径
  WHERE
    subscription_source_id IS NOT NULL

  UNION ALL

  -- 第二步：递归遍历，找到所有间接子节点（比如4是3的子，3是1的子，所以4也是1的子）
  SELECT
    rh.root_id,
    t.subscription_id AS child_id
  FROM
    recursive_hierarchy rh
  JOIN
    `your_project.your_dataset.your_table` t
  ON
    rh.child_id = t.subscription_source_id
),
-- 第三步：提取所有根节点（没有父节点的subscription_id）
root_nodes AS (
  SELECT subscription_id AS root_id
  FROM `your_project.your_dataset.your_table`
  WHERE subscription_source_id IS NULL
)

-- 最后聚合生成目标结果
SELECT
  rn.root_id AS subscription_id,
  -- 用空格分隔子ID，没有子节点时自动返回NULL
  STRING_AGG(DISTINCT CAST(rh.child_id AS STRING), ' ' ORDER BY rh.child_id) AS subscription_children
FROM
  root_nodes rn
LEFT JOIN
  recursive_hierarchy rh
ON
  rn.root_id = rh.root_id
GROUP BY
  rn.root_id
ORDER BY
  rn.root_id;

结果说明 #

运行这段代码后，你会得到和期望完全一致的输出：

• subscription_id=1对应的subscription_children是3 4
• subscription_id=2对应的是5
• subscription_id=6对应的是NULL

可选调整：返回数组格式 #

如果你希望子节点以数组而非字符串形式返回，只需要把STRING_AGG替换成ARRAY_AGG即可：

ARRAY_AGG(DISTINCT rh.child_id ORDER BY rh.child_id) AS subscription_children

这样返回的是INT64类型的数组，没有子节点时同样会显示NULL。

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