MySQL
Dynamische Un-Pivot-tabel met voorbereide verklaring

Schakel een dynamische set kolommen uit op basis van voorwaarde

Het volgende voorbeeld is een zeer nuttige basis wanneer u transactiegegevens probeert te converteren naar niet-gedraaide gegevens om BI / rapportage, waarbij de dimensies die niet-gedraaid moeten worden een dynamische set kolommen kunnen hebben.

In ons voorbeeld veronderstellen we dat de tabel met onbewerkte gegevens beoordelingsgegevens van werknemers bevat in de vorm van gemarkeerde vragen.

De tabel met onbewerkte gegevens is de volgende:

create table rawdata

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

De tabel met onbewerkte gegevens is een tijdelijke tabel als onderdeel van de ETL-procedure en kan een wisselend aantal vragen hebben. Het doel is om dezelfde niet-draaiende procedure te gebruiken voor een willekeurig aantal vragen, namelijk kolommen die niet-draaien worden.

Hieronder is een voorbeeld van een stuk speelgoed van rawdata-tabel:

De bekende, statische manier om de gegevens te ontkoppelen in MYSQL is met behulp van 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; 

In ons geval willen we een manier definiëren om een willekeurig aantal QuestionId-kolommen te ontkoppelen. Daarvoor moeten we een voorbereide instructie uitvoeren die een dynamische selectie van de gewenste kolommen is. Om te kunnen kiezen welke kolommen niet moeten worden gedraaid, gebruiken we een GROUP_CONCAT-instructie en kiezen we de kolommen waarvoor het gegevenstype is ingesteld op 'int'. In de GROUP_CONCAT nemen we ook alle extra elementen op van onze SELECT-instructie die moet worden uitgevoerd.

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;

Bij een andere gelegenheid hadden we kolommen kunnen kiezen waarbij de kolomnaam overeenkomt met een patroon, bijvoorbeeld in plaats van

DATA_TYPE = 'Int'

gebruik

COLUMN_NAME LIKE 'Question%'

of iets geschikts dat kan worden bestuurd via de ETL-fase.

De voorbereide verklaring wordt als volgt afgerond:

set @temp3 = null;

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

select @temp3;

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

De tabel met niet-vermelde gegevens is de volgende:

SELECT * FROM unpivoteddata

Kolommen selecteren op basis van een voorwaarde en vervolgens een voorbereide verklaring opstellen, is een efficiënte manier om gegevens dynamisch te ontkoppelen.