Come creare colonne variabili fittizie per migliaia di categorie in Google BigQuery?

Ho una semplice tabella con 2 colonne: UserID e Category, e ogni UserID può ripetere con alcune categorie, in questo modo:

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

Voglio “dummify” questa tabella: cioè creare una tabella di output che abbia una colonna univoca per ogni categoria composta da variabili dummy (0/1 a seconda che l’ID utente appartenga a quella particolare categoria):

 UserID ABC ------ -- -- -- 1 1 1 0 2 0 0 1 3 1 1 1 

Il mio problema è che ho MIGLIAIA di categorie (non solo 3 come in questo esempio) e quindi questo non può essere realizzato in modo efficiente usando l’istruzione CASE WHEN.

Quindi le mie domande sono:

1) C’è un modo per “dummify” della colonna Category in Google BigQuery senza utilizzare migliaia di istruzioni CASE WHEN.

2) Si tratta di una situazione in cui la funzionalità UDF funziona bene? Sembra che sarebbe il caso, ma non sono abbastanza familiare con UDF in BigQuery per risolvere questo problema. Qualcuno sarebbe in grado di dare una mano?

Grazie.

Puoi usare sotto “technic”

Prima esecuzione della query n. 1. Produce la query (query n. 2) che è necessario eseguire per ottenere i risultati necessari. Per favore, continua a considerare i commenti di Mosha prima di diventare “selvaggio” con migliaia di categorie: o)

Query # 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 ) 

Il risultato sarà come sotto – Query # 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 

naturalmente per tre categorie: potresti farlo manualmente, ma per migliaia sicuramente farà il giorno per te !!

Il risultato della query n. 2 avrà l’aspetto previsto:

 UserID ABC 1 1 1 0 2 0 0 1 3 1 1 1