如何使用BigQuery拆分逗号分隔字符串为行并均分第二列数值

调整后可实现需求的查询语句 #

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '11710, 11830' id, 10 hours UNION ALL
  SELECT '11711, 11015, 10020', 9
)
SELECT 
  TRIM(uniq_id) AS id, 
  CAST(hours / ARRAY_LENGTH(SPLIT(id)) AS INT64) AS hours
FROM `project.dataset.table`,
UNNEST(SPLIT(id)) uniq_id

逻辑说明 #

• 用ARRAY_LENGTH(SPLIT(id))计算当前行id列拆分后的元素总数量，示例中第一行拆分后数量为2，第二行拆分后数量为3
• 原始hours数值除以元素总数量，即可得到均分后每一行对应的数值，这里加CAST转整数是为了和示例输出的整数格式完全匹配，不需要整数结果可以去掉转换逻辑
• TRIM(uniq_id)用于去除拆分后字符串首尾多余的空格，避免输出的id携带不必要的空白字符

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