mysql - ¿Cómo crear columnas variables ficticias para miles de categorías en Google BigQuery?

google-bigquery dummy-variable

Question

Tengo una tabla simple con 2 columnas: ID de usuario y Categoría, y cada ID de usuario puede repetirse con algunas categorías, así:

UserID   Category
------   --------
1         A
1         B
2         C
3         A
3         C
3         B

Quiero "dummify" esta tabla: es decir, crear una tabla de salida que tenga una columna única para cada Categoría que consista en variables ficticias (0/1 dependiendo de si el UserID pertenece a esa Categoría en particular):

UserID    A  B  C
------    -- -- --
1         1  1  0
2         0  0  1
3         1  1  1

Mi problema es que tengo MILES de categorías (no solo 3 como en este ejemplo) y, por lo tanto, esto no se puede lograr de manera eficiente usando la declaración CASO CUANDO.

Entonces mis preguntas son:

1) ¿Hay alguna manera de "dummificar" la columna Categoría en Google BigQuery sin usar miles de declaraciones CASO CUANDO?

2) ¿Es esta una situación en la que la funcionalidad UDF funciona bien? Parece que sería el caso, pero no estoy lo suficientemente familiarizado con UDF en BigQuery para resolver este problema. ¿Alguien podría ayudar?

Gracias.

Answer 1

Se puede utilizar a continuación "técnica"

Primero ejecute la consulta # 1. Produce la consulta (consulta # 2) que necesita ejecutar para obtener el resultado que necesita. Por favor, todavía considere los comentarios de Mosha antes de volverse "salvaje" con miles de categorías: o)

Consulta # 1:

SELECT ''select UserID, '' + 
   GROUP_CONCAT_UNQUOTED(
    ''sum(if(category = "'' + STRING(category) + ''", 1, 0)) as '' + STRING(category)
   ) 
   + '' from YourTable group by UserID''
FROM (
  SELECT category 
  FROM YourTable  
  GROUP BY category
)

El resultado será el siguiente: consulta n.º 2

SELECT
  UserID,
  SUM(IF(category = "A", 1, 0)) AS A,
  SUM(IF(category = "B", 1, 0)) AS B,
  SUM(IF(category = "C", 1, 0)) AS C
FROM
  YourTable
GROUP BY
  UserID

por supuesto, para tres categorías: puede hacerlo manualmente, ¡pero para miles definitivamente le hará el día perfecto!

El resultado de la consulta # 2 se verá como espera:

UserID  A   B   C    
1       1   1   0    
2       0   0   1    
3       1   1   1