MySQL
Dynamische Un-Pivot-Tabelle mit Prepared-Anweisung

Deaktivieren Sie einen dynamischen Satz von Spalten basierend auf den Bedingungen

Das folgende Beispiel ist eine sehr nützliche Grundlage, wenn Sie versuchen, Transaktionsdaten aus Gründen des BI / Reporting in nicht geschwenkte Daten zu konvertieren, wobei die nicht geschwenkten Dimensionen über eine dynamische Spaltengruppe verfügen.

In unserem Beispiel nehmen wir an, dass die Rohdatentabelle Mitarbeiterbeurteilungsdaten in Form markierter Fragen enthält.

Die Rohdatentabelle ist folgende:

create table rawdata

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

Die Rohdatentabelle ist eine temporäre Tabelle im Rahmen des ETL-Verfahrens und kann eine unterschiedliche Anzahl von Fragen haben. Ziel ist es, für eine beliebige Anzahl von Fragen das gleiche Pivotierungsverfahren zu verwenden, d. H.

Unten ist ein Spielzeugbeispiel für eine Rohdatentabelle:

Die bekannte, statische Methode zum Aufheben der Pivotierung der Daten in MySQL ist die Verwendung von 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 unserem Fall möchten wir einen Weg definieren, um eine beliebige Anzahl von QuestionId-Spalten aufzuheben. Dazu müssen wir eine vorbereitete Anweisung ausführen, die eine dynamische Auswahl der gewünschten Spalten ist. Um auswählen zu können, welche Spalten nicht geschwenkt werden müssen, verwenden wir eine GROUP_CONCAT-Anweisung und wählen die Spalten aus, für die der Datentyp auf 'int' gesetzt ist. In der GROUP_CONCAT enthalten wir auch alle zusätzlichen Elemente unserer auszuführenden SELECT-Anweisung.

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;

Bei einer anderen Gelegenheit hätten wir Spalten auswählen können, deren Spaltenname beispielsweise einem Muster entspricht

DATA_TYPE = 'Int'

benutzen

COLUMN_NAME LIKE 'Question%'

oder etwas geeignetes, das durch die ETL-Phase gesteuert werden kann.

Die vorbereitete Aussage wird wie folgt abgeschlossen:

set @temp3 = null;

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

select @temp3;

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

Die unpivoteddata-Tabelle sieht folgendermaßen aus:

SELECT * FROM unpivoteddata

Das Auswählen von Spalten nach einer Bedingung und das Erstellen einer vorbereiteten Anweisung ist eine effiziente Methode, um Daten dynamisch zu entfernen.