BigQuery to split the all the metrics into n equal parts depending on the number of times a certain string value appears in a column

Question

I am not sure how to explain the problem. I will share the sample i/p and o/p below. Note that it's not fixed how many times "job#" appears in a single row.

Input

Output

Please refer https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Ananth MK, Aug 26 '22 at 09:46
@AnanthMK I also need to split the metrics equally. How can I do that? — Priya Nair, Aug 26 '22 at 11:17
by equally do you mean if there were 3 jobs divide the metrics by 3? — Daniel Zagales, Aug 26 '22 at 13:03

Daniel Zagales · Accepted Answer · 2022-08-26T13:05:47.133

Try using the regexp_extract_all function like the following:

with sample_data as (
  SELECT 'camp1' as camp, '01/08/2022' as date, 'job#12' as job, 23 as a1, 34 as a2, 21 as a3 UNION ALL
  SELECT 'camp2', '01/08/2022', 'job#14 & job#15', 20, 30, 30 UNION ALL
  SELECT 'camp3', '01/08/2022', 'job#11 job#13 job#20', 21, 30, 21 union all
  select 'camp4', '01/08/2022', 'job#21 & job#22 & job#23 & job#24', 40, 12, 8
)

SELECT camp,
  date,
  job_ex,
  a1,
  a2,
  a3,
  a1/ count(job_ex) OVER (PARTITION BY camp) a1_split,
  a2/ count(job_ex) OVER (PARTITION BY camp) a2_split,
  a3/ count(job_ex) OVER (PARTITION BY camp) a3_split,
FROM sample_data,
  UNNEST(regexp_extract_all(job, r'job\#\d+')) as job_ex

It produces the following results

score 0 · Answer 2 · answered Aug 26 '22 at 16:56

Consider below approach

select camp, date, job, 
  a1/jobs_count as a1, 
  a2/jobs_count as a2, 
  a3/jobs_count as a3
from your_table, 
unnest([struct(regexp_extract_all(job, r'job#\d+') as jobs_arr)]), 
unnest([array_length(jobs_arr)]) jobs_count,
unnest(jobs_arr) job

if applied to sample data in your question - output is

BigQuery to split the all the metrics into n equal parts depending on the number of times a certain string value appears in a column

2 Answers2