MySQL
Tableau dynamique de pivotement à l'aide de l'instruction préparée

Dé-pivoter un ensemble dynamique de colonnes en fonction de la condition

L'exemple suivant est très utile lorsque vous tentez de convertir des données de transaction en données non pivotées pour des raisons de BI / de génération de rapports, où les dimensions à désassembler peuvent avoir un ensemble dynamique de colonnes.

Pour notre exemple, nous supposons que la table de données brutes contient des données d'évaluation d'employés sous la forme de questions marquées.

La table de données brutes est la suivante:

create table rawdata

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

La table rawdata est une table temporaire faisant partie de la procédure ETL et peut comporter un nombre variable de questions. Le but est d'utiliser la même procédure de non-pivotement pour un nombre arbitraire de Questions, à savoir les colonnes qui vont être non pivotées.

Voici un exemple de jouet de la table rawdata:

La méthode statique connue pour décomposer les données dans MYSQL consiste à utiliser 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; 

Dans notre cas, nous voulons définir un moyen de décomposer un nombre arbitraire de colonnes QuestionId. Pour cela, nous devons exécuter une instruction préparée qui est une sélection dynamique des colonnes souhaitées. Afin de pouvoir choisir quelles colonnes doivent être non pivotées, nous utiliserons une instruction GROUP_CONCAT et nous choisirons les colonnes pour lesquelles le type de données est défini sur 'int'. Dans GROUP_CONCAT, nous incluons également tous les éléments supplémentaires de notre instruction SELECT à exécuter.

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;

À une autre occasion, nous aurions pu choisir des colonnes indiquant que le nom de la colonne correspond à un motif, par exemple au lieu de

DATA_TYPE = 'Int'

utilisation

COLUMN_NAME LIKE 'Question%'

ou quelque chose qui peut être contrôlé par la phase ETL.

La déclaration préparée est finalisée comme suit:

set @temp3 = null;

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

select @temp3;

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

La table unpivoteddata est la suivante:

SELECT * FROM unpivoteddata

La sélection de colonnes en fonction d'une condition et la création d'une instruction préparée constituent un moyen efficace de désynchroniser dynamiquement les données.