Splitting a string and count occurrence of components in BigQuery

Raymond Chau 0
Viewed 60

I have a dataset in format like this in my BigQuery table:

Keywords Count
test,abc,c 1
test 1
abc 2

How can I transform it to:

Keywords Count
test 2
abc 3
c 1

Thanks,

Raymond

google-bigquery
1 Answers

You can try below query

WITH sample_table AS (
  SELECT 'test,abc,c' AS Keywords, 1 AS Count UNION ALL
  SELECT 'test', 1 UNION ALL
  SELECT 'abc', 2
)
SELECT keyword, SUM(Count) AS Count 
  FROM sample_table, UNNEST(SPLIT(Keywords)) keyword
 GROUP BY 1;
Query results
+---------+-------+
| keyword | Count |
+---------+-------+
| test    |     2 |
| abc     |     3 |
| c       |     1 |
+---------+-------+
Jaytiger0
Related