MySQL
Dynamiczna tabela rozproszona za pomocą instrukcji Prepared

Odznacz dynamiczny zestaw kolumn w zależności od warunków

Poniższy przykład jest bardzo przydatną podstawą, gdy próbujesz przekonwertować dane transakcji na dane nieobrotowe ze względów BI / raportowania, gdzie wymiary, które mają być nieobrotowe, mogą mieć dynamiczny zestaw kolumn.

W naszym przykładzie przypuszczamy, że tabela surowych danych zawiera dane oceny pracowników w postaci zaznaczonych pytań.

Tabela surowych danych jest następująca:

create table rawdata

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

Tabela rawdata jest tabelą tymczasową w ramach procedury ETL i może mieć różną liczbę pytań. Celem jest zastosowanie tej samej procedury rozpinania dla dowolnej liczby pytań, a mianowicie kolumn, które zostaną rozparte.

Poniżej znajduje się zabawkowy przykład tabeli rawdata:

Dobrze znanym, statycznym sposobem na rozproszenie danych w MYSQL jest użycie 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; 

W naszym przypadku chcemy zdefiniować sposób cofnięcia przestawienia dowolnej liczby kolumn QuestionId. W tym celu musimy wykonać przygotowane polecenie, które jest dynamicznym wyborem żądanych kolumn. Aby móc wybrać kolumny, które mają zostać odwrócone, użyjemy instrukcji GROUP_CONCAT i wybierzemy kolumny, dla których typ danych jest ustawiony na „int”. W GROUP_CONCAT uwzględniamy również wszystkie dodatkowe elementy naszej instrukcji SELECT, która ma zostać wykonana.

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;

Przy innej okazji moglibyśmy wybrać kolumny, których nazwa kolumny pasuje do wzorca, na przykład zamiast

DATA_TYPE = 'Int'

posługiwać się

COLUMN_NAME LIKE 'Question%'

lub coś odpowiedniego, co można kontrolować przez fazę ETL.

Przygotowane oświadczenie jest finalizowane w następujący sposób:

set @temp3 = null;

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

select @temp3;

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

Niepodzielona tabela danych jest następująca:

SELECT * FROM unpivoteddata

Wybranie kolumn zgodnie z warunkiem, a następnie spreparowanie przygotowanej instrukcji to skuteczny sposób na dynamiczne odwracanie danych.