BigQuery中如何按订单ID分组聚合数组成员得到可用位置并集

该需求可直接通过BigQuery原生函数实现，无需开发自定义函数。

核心聚合逻辑 #

• 商品总数统计：按Order id分组后，使用COUNT(*)统计每组内的记录条数，单条记录对应1件商品，统计结果即为要求的Item_count字段
• 可用位置数组合并：需求要求保留重复值，直接使用ARRAY_CONCAT_AGG聚合函数即可，该函数会逐行拼接数组类型字段的所有值，自动保留全部重复元素，最终输出合并后的完整数组。

可直接运行的SQL示例 #

以下SQL先构造了你给出的样例数据，执行后可直接看到匹配需求的输出结果，实际使用时将FROM后的样例表替换为你自己的业务表路径即可：

-- 构造样例数据，实际使用时可删除该CTE段
WITH sample_table AS (
  SELECT 1 AS `Order id`, 'sku_001' AS Item_id, ['F1','F2'] AS Availability, '11-10-2021' AS Date
  UNION ALL
  SELECT 1 AS `Order id`, 'sku_002' AS Item_id, ['F1','F3'] AS Availability, '11-10-2021' AS Date
  UNION ALL
  SELECT 2 AS `Order id`, 'sku_003' AS Item_id, ['F2','F3'] AS Availability, '13-10-2021' AS Date
)
-- 正式聚合逻辑
SELECT
  `Order id`,
  COUNT(*) AS Item_count,
  ARRAY_CONCAT_AGG(Availability) AS merged_availability
FROM sample_table
-- 实际使用时替换为你的表路径，格式为 `项目ID.数据集名.表名`
GROUP BY `Order id`

针对给出的样例数据，执行后返回结果如下：

特殊场景适配 #

如果你的Availability字段不是BigQuery原生数组类型，而是存储为(F1,F2)格式的字符串，需要先做格式转换再聚合，将聚合部分替换为以下写法即可：

ARRAY_CONCAT_AGG(
  SPLIT(REGEXP_REPLACE(Availability, r'^\(|\)$', ''), ',')
) AS merged_availability

该段逻辑会先剔除字段首尾的括号，再按逗号将字符串拆分为数组，之后完成拼接聚合。

如果后续需要对合并后的位置去重，只需要将聚合逻辑调整为以下写法即可（当前需求不需要）：

ARRAY(
  SELECT DISTINCT location 
  FROM UNNEST(ARRAY_CONCAT_AGG(Availability)) AS location
) AS unique_merged_availability

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