sql - Transponer filas en columnas en BigQuery(implementación dinámica)

google-bigquery google-cloud-platform

Question

Quiero generar una nueva tabla y colocar todos los pares de valores clave con claves como nombres de columna y valores como sus respectivos valores usando BigQuery.

Ejemplo:

**Key** **Value**
channel_title Mahendra Guru
youtube_id ugEGMG4-MdA
channel_id UCiDKcjKocimAO1tV
examId 72975611-4a5e-11e5
postId 1189e340-b08f
channel_title Ab Live
youtube_id 3TNbtTwLY0U
channel_id UCODeKM_D6JLf8jJt
examId 72975611-4a5e-11e5
postId 0c3e6590-afeb

Quiero convertirlo a:

**channel_title youtube_id channel_id examId postId**
Mahendra Guru ugEGMG4-MdA UCiDKcjKocimAO1tV 72975611-4a5e-11e5 1189e340-b08f
Ab Live 3TNbtTwLY0U UCODeKM_D6JLf8jJt 72975611-4a5e-11e5 0c3e6590-afeb

¿Cómo hacerlo usando BigQuery?

Answer 1

BigQuery aún no admite funciones pivotantes
Todavía puede hacer esto en BigQuery usando el siguiente enfoque

Pero primero, además de dos columnas en los datos de entrada, debe tener una columna más que especifique grupos de filas en la entrada que deben combinarse en una fila en la salida

Entonces, supongo que su tabla de entrada (yourTable) se ve a continuación

**id** **Key** **Value**
1 channel_title Mahendra Guru
1 youtube_id ugEGMG4-MdA
1 channel_id UCiDKcjKocimAO1tV
1 examId 72975611-4a5e-11e5
1 postId 1189e340-b08f
2 channel_title Ab Live
2 youtube_id 3TNbtTwLY0U
2 channel_id UCODeKM_D6JLf8jJt
2 examId 72975611-4a5e-11e5
2 postId 0c3e6590-afeb 

Entonces, primero debes ejecutar debajo de la consulta

SELECT ''SELECT id, '' +
GROUP_CONCAT_UNQUOTED(
''MAX(IF(key = "'' + key + ''", value, NULL)) as ['' + key + '']''
)
+ '' FROM yourTable GROUP BY id ORDER BY id''
FROM (
SELECT key
FROM yourTable
GROUP BY key
ORDER BY key
) 

El resultado de la consulta anterior será una cadena que (si formatea) se verá a continuación

SELECT
id,
MAX(IF(key = "channel_id", value, NULL)) AS [channel_id],
MAX(IF(key = "channel_title", value, NULL)) AS [channel_title],
MAX(IF(key = "examId", value, NULL)) AS [examId],
MAX(IF(key = "postId", value, NULL)) AS [postId],
MAX(IF(key = "youtube_id", value, NULL)) AS [youtube_id]
FROM yourTable
GROUP BY id
ORDER BY id

ahora debe copiar el resultado anterior (nota: realmente no necesita formatearlo, lo hice solo para presentar) y ejecutarlo como consulta normal

El resultado será el esperado

id channel_id channel_title examId postId youtube_id
1 UCiDKcjKocimAO1tV Mahendra Guru 72975611-4a5e-11e5 1189e340-b08f ugEGMG4-MdA
2 UCODeKM_D6JLf8jJt Ab Live 72975611-4a5e-11e5 0c3e6590-afeb 3TNbtTwLY0U 

Tenga en cuenta: puede omitir el Paso 1 si puede construir la consulta adecuada (como en el paso 2) usted mismo y el número de campos pequeños y constantes o si se trata de una sola vez. ¡Pero el Paso 1 es solo un paso de ayuda que lo hace por usted, para que pueda crearlo rápidamente en cualquier momento!

Si está interesado, puede ver más sobre pivotar en mis otras publicaciones.

¿Cómo escalar Pivoting en BigQuery?
Tenga en cuenta que hay una limitación de 10K columnas por tabla, por lo que está limitado con organizaciones de 10K.
También puede ver a continuación ejemplos simplificados (si el anterior es demasiado complejo / detallado):
¿Cómo transponer filas a columnas con gran cantidad de datos en BigQuery / SQL?
¿Cómo crear columnas variables ficticias para miles de categorías en Google BigQuery?
Pivotar campos repetidos en BigQuery