CODEWITHSUNDEEP
sqlgoogle-bigquery
Tom Fishman
Tom Fishman

Reputation: 1826

google bigquery the count of each string into one table

We want to query this table:

Row bucket  action  count    
1   1407974 aaaaaa  15   
2   1407974 bbbbbb  67   
3   1407369 aaaaaa  89   
4   1407369 bbbbbb  501

to this output:

Row bucket  actionA actionB  
1   1407974 15      67   
3   1407369 89      501

What is the cheapest way to query with google bigquery?

Upvotes: 1

Views: 99

Answers (1)

sutoh
sutoh

Reputation: 36

SELECT 
  bucket, MAX(actionA) actionA, MAX(actionB) actionB
FROM (
  SELECT 
    bucket,
    CASE WHEN REGEXP_MATCH(action, 'aaaaaa') THEN count END actionA, 
    CASE WHEN REGEXP_MATCH(action, 'bbbbbb') THEN count END actionB
  FROM [tmp.a]
) GROUP BY bucket

Upvotes: 2

Related Questions