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)