MySQL
Dynamisk Un-Pivot-tabell med förberett uttalande

Lossa upp en dynamisk uppsättning kolumner baserat på villkor

Följande exempel är en mycket användbar grund när du försöker konvertera transaktionsdata till icke-svängd data av BI / rapporteringsskäl, där dimensionerna som ska avmarkeras kan ha en dynamisk uppsättning kolumner.

För vårt exempel antar vi att rådatatabellen innehåller uppgifter om anställdas utvärdering i form av markerade frågor.

Råttatabellen är följande:

create table rawdata

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

Rawdata-tabellen är en tillfällig tabell som en del av ETL-proceduren och kan ha ett varierande antal frågor. Målet är att använda samma un-pivoting-procedur för ett godtyckligt antal frågor, nämligen kolumner som kommer att vara un-pivot.

Nedan är ett leksaksexempel på rawdata-tabellen:

Det välkända, statiska sättet att ta bort data i MYSQL är att använda 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; 

I vårt fall vill vi definiera ett sätt att avmarkera ett godtyckligt antal fråga-kolumner. För det måste vi utföra ett förberedt uttalande som är ett dynamiskt urval av de önskade kolumnerna. För att kunna välja vilka kolumner som ska avaktiveras använder vi ett GROUP_CONCAT-uttalande och vi väljer de kolumner som datatypen är inställd på 'int' för. I GROUP_CONCAT inkluderar vi också alla ytterligare element i vårt SELECT-uttalande som ska utföras.

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;

Vid ett annat tillfälle kunde vi ha valt kolumner som kolumnnamnet matchar ett mönster, till exempel istället för

DATA_TYPE = 'Int'

använda sig av

COLUMN_NAME LIKE 'Question%'

eller något lämpligt som kan styras genom ETL-fasen.

Det förberedda uttalandet slutförs enligt följande:

set @temp3 = null;

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

select @temp3;

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

Tabellen unpivoteddata är följande:

SELECT * FROM unpivoteddata

Att välja kolumner enligt ett villkor och sedan skapa ett förberett uttalande är ett effektivt sätt att dynamiskt avaktivera data.