google bigquery - Pivotar campos repetidos en BigQuery

google-bigquery

Question

Mi esquema se parece a esto:

userid:string
timestamp:integer
params:nested/repeated field with 2 fields
  - name:string (possible values: "a", "b","c")
  - value:string

Quiero que mi consulta devuelva lo siguiente:

userid, timestamp, a, b, c
123, 1447799796, foo, bar, xyz
233, 1447799900, bob, xxx, yyy
:
:

¿Cuál es la forma más fácil de hacer esto?

Answer 1

Algo en este sentido:

SELECT 
  userid,
  timestamp,
  FIRST(name == "a", value, NULL) WITHIN RECORD a,
  FIRST(name == "b", value, NULL) WITHIN RECORD b,
  FIRST(name == "c", value, NULL) WITHIN RECORD c,
FROM t

Answer 2

cuando los valores posibles se conocen de antemano y no hay muchos de ellos para escribir manualmente SQL, puede usar a continuación:

SELECT 
  userid,
  ts,
  MAX(IF(params.name = "a", params.value, NULL)) WITHIN RECORD a,
  MAX(IF(params.name = "b", params.value, NULL)) WITHIN RECORD b,
  MAX(IF(params.name = "c", params.value, NULL)) WITHIN RECORD c
FROM yourTable 

Si los valores posibles son "desconocidos" de antemano y / o dinámicos de una ejecución a otra, puede usar el siguiente SQL auxiliar para generar el tipo de SQL anterior.

SELECT ''select userid, ts, '' + 
   GROUP_CONCAT_UNQUOTED(
      ''max(if(params.name = "'' + STRING(params.name) + ''", params.value, null)) 
       WITHIN RECORD as ['' + STRING(params.name) + '']''
   ) 
   + '' from yourTable ''
FROM (SELECT params.name FROM yourTable GROUP BY params.name)