CODEWITHSUNDEEP
google-bigqueryflatten
João
João

Reputation: 5

How to flatten values in a row in Google Big Query

guys

I've a problem with my data in big query. I have a dataset like pic #1 and I need to rank my users and group their ranking notes in a unique row (like pic #2). To be clear, my ranking column type is int, not an array.

Is it possible to handle this w/bigquery or do I need to transport this dataset to python and do this transformation there ?

PIC #1 ORIGINAL DATASET

PIC #1 - ORIGINAL DATASET

PIC #2 GROUPED AND FORMATED DATASET

PIC #2 - GROUPED AND FORMATED DATASET

Upvotes: 0

Views: 298

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' user, 10 ranking UNION ALL
  SELECT 'b',  2 UNION ALL
  SELECT 'a', 12 UNION ALL
  SELECT 'a', 14 UNION ALL
  SELECT 'c', 22 UNION ALL
  SELECT 'd', 21 
)
SELECT 
  user, 
  MAX(ranking) AS ranking_max,
  STRING_AGG(CAST(ranking AS STRING)) ranking_list
FROM `project.dataset.table`
GROUP BY user

with result 

Row user    ranking_max ranking_list     
1   a       14          10,12,14     
2   b       2           2    
3   c       22          22   
4   d       21          21

Note: if you need ranking_list to be ordered - you can use ORDER BY in STRING_AGG as in below 

STRING_AGG(CAST(ranking AS STRING) ORDER BY ranking) ranking_list

Upvotes: 1

Related Questions