使用BigQuery按列中job#出现次数拆分指标为等份的实现方案

需求：针对BigQuery中的数据表，根据指定列内"job#"字符串的出现次数，将该行所有指标数值拆分为对应数量的等份，生成与次数一致的行数，每行指标值为原数值除以出现次数。

输入输出样例 #

输入表（示例表名job_metrics） #

输出结果 #

实现SQL #

WITH job_processing AS (
  SELECT
    *,
    -- 计算"job#"的出现次数
    ARRAY_LENGTH(SPLIT(job_column, 'job#')) - 1 AS job_count,
    -- 拆分出单个job条目数组
    SPLIT(job_column, ', ') AS job_items
  FROM `your-project.your-dataset.job_metrics`
)
SELECT
  id,
  -- 指标值均分，保留两位小数可按需调整
  ROUND(metric_value / job_count, 2) AS metric_value,
  job_column,
  job_item AS split_job
FROM job_processing,
UNNEST(job_items) AS job_item
-- 过滤拆分后可能出现的空值
WHERE job_item != ''

关键逻辑说明 #

• 统计job次数：通过SPLIT(job_column, 'job#')将字符串按"job#"拆分，数组长度减1即为"job#"的出现次数。
• 展开多行：用SPLIT把job列拆分为单个条目的数组，再通过UNNEST将数组展开为多行，每行对应一个job条目。
• 指标均分计算：将原指标值除以job次数，用ROUND控制小数精度，可根据业务需求调整保留位数。

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