google-bigquery - 在 Big Query 中将多列嵌套到一个数组中

Question

鉴于此 BQ 表

有 1.026 行，有 944 个唯一的 modemio_cat_ids

如何返回一个查询，该查询将所有非空列嵌套到 1 个单个数组，每个 modemio_cat_id 称为“ parent ”？

示例:对于 modemio_cat_id = 1111118

然后最后按 modemio_cat_id 分组 + 累积所有数组以防重复

错误的方法:这个查询总是为每个 modemutti_cat_id 返回相同的数组:

SELECT modemio_cat_id, ARRAY (
  SELECT AS STRUCT cat1_id, cat2_id FROM `modemutti-8d8a6.categorization.test`
  ) as parent
FROM `modemutti-8d8a6.categorization.test` 
group by modemio_cat_id

Answer 1

以下 BigQuery 标准 SQL 示例

#standardSQL
SELECT modemio_cat_id, 
  ARRAY_AGG(DISTINCT cat_id IGNORE NULLS) parents
FROM `modemutti-8d8a6.categorization.test`,
  UNNEST([cat1_id, cat2_id, cat3_id, cat4_id, cat5_id, cat6_id]) cat_id
GROUP BY modemio_cat_id