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