MySQL
Tabella non pivot dinamica con istruzione preparata

Annulla la rotazione di un insieme dinamico di colonne in base alle condizioni

L'esempio seguente è una base molto utile quando si tenta di convertire i dati della transazione in dati non imperniati per ragioni di BI / reporting, in cui le dimensioni che non devono essere pivot possono avere un insieme dinamico di colonne.

Per il nostro esempio, supponiamo che la tabella dei dati grezzi contenga dati di valutazione dei dipendenti sotto forma di domande contrassegnate.

La tabella dei dati non elaborati è la seguente:

create table rawdata

(
 PersonId VARCHAR(255)
,Question1Id INT(11)
,Question2Id INT(11)
,Question3Id INT(11)
)  

La tabella rawdata è una tabella temporanea come parte della procedura ETL e può avere un numero variabile di domande. L'obiettivo è quello di utilizzare la stessa procedura non pivot per un numero arbitrario di domande, vale a dire colonne che non saranno imperniate.

Di seguito è riportato un esempio di tabella rawdata:

Il noto modo statico per sbloccare i dati, in MYSQL, è utilizzando UNION ALL:

create table unpivoteddata

(

 PersonId VARCHAR(255)
,QuestionId VARCHAR(255)
,QuestionValue INT(11)

);

INSERT INTO unpivoteddata SELECT PersonId, 'Question1Id' col, Question1Id 
FROM rawdata
UNION ALL
SELECT PersonId, 'Question2Id' col, Question2Id 
FROM rawdata
UNION ALL
SELECT PersonId, 'Question3Id' col, Question3Id 
FROM rawdata; 

Nel nostro caso vogliamo definire un modo per annullare l'annullamento di un numero arbitrario di colonne QuestionId. Per questo abbiamo bisogno di eseguire un'istruzione preparata che sia una selezione dinamica delle colonne desiderate. Per poter scegliere quali colonne debbano essere non pivot, useremo un'istruzione GROUP_CONCAT e sceglieremo le colonne per le quali il tipo di dati è impostato su "int". Nel GROUP_CONCAT includiamo anche tutti gli elementi aggiuntivi della nostra istruzione SELECT da eseguire.

set @temp2 = null; 

SELECT GROUP_CONCAT(' SELECT ', 'PersonId',',','''',COLUMN_NAME,'''', ' col     ',',',COLUMN_NAME,' FROM rawdata' separator ' UNION ALL' ) FROM     INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'rawdata' AND DATA_TYPE = 'Int' INTO     @temp2;

select @temp2;

In un'altra occasione avremmo potuto scegliere colonne che il nome della colonna corrisponda a un modello, ad esempio invece di

DATA_TYPE = 'Int'

uso

COLUMN_NAME LIKE 'Question%'

o qualcosa di adatto che può essere controllato attraverso la fase ETL.

La dichiarazione preparata è finalizzata come segue:

set @temp3 = null;

select concat('INSERT INTO unpivoteddata',@temp2) INTO @temp3;

select @temp3;

prepare stmt FROM @temp3;
execute stmt;
deallocate prepare stmt;

La tabella unpivoteddata è la seguente:

SELECT * FROM unpivoteddata

Selezionare le colonne in base a una condizione e quindi creare una dichiarazione preparata è un modo efficace per disattivare dinamicamente i dati.