MySQL
Prepared Statement를 사용한 동적 Un-Pivot 테이블

조건에 따라 동적 열 집합 해제

다음 예는 BI /보고를 위해 트랜잭션 데이터를 피벗 팅되지 않은 데이터로 변환하려고 할 때 매우 유용합니다. 피벗되지 않은 차원에 동적 열 집합이있을 수 있습니다.

예를 들어, 원시 데이터 테이블에는 표시된 질문 형태로 직원 평가 데이터가 포함되어 있다고 가정합니다.

원시 데이터 테이블은 다음과 같습니다.

create table rawdata

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

rawdata 테이블은 ETL 프로 시저의 일부로서 임시 테이블이며 다양한 수의 질문을 가질 수 있습니다. 목표는 임의의 수의 질문, 즉 피벗 해제 될 열에 대해 동일한 피벗 해제 프로 시저를 사용하는 것입니다.

다음은 rawdata 테이블의 장난감 예입니다.

MYSQL에서 데이터를 unpivot하는 잘 알려진 정적 인 방법은 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; 

여기서는 임의의 수의 QuestionId 열을 unpivot 할 방법을 정의하고자합니다. 이를 위해서는 원하는 열을 동적으로 선택하는 준비된 명령문을 실행해야합니다. 어떤 열을 피벗 해제 할 것인지 선택할 수 있으려면 GROUP_CONCAT 문을 사용하고 데이터 형식이 'int'로 설정된 열을 선택합니다. GROUP_CONCAT에는 실행될 SELECT 문의 추가 요소가 모두 포함됩니다.

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;

또 다른 경우에는 열 이름이 패턴과 일치하는 열을 선택할 수 있습니다. 예를 들어

DATA_TYPE = 'Int'

용도

COLUMN_NAME LIKE 'Question%'

또는 ETL 단계를 통해 제어 할 수있는 적합한 것입니다.

준비된 진술은 다음과 같이 마무리됩니다 :

set @temp3 = null;

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

select @temp3;

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

unpivoteddata 테이블은 다음과 같습니다.

SELECT * FROM unpivoteddata

조건에 따라 열을 선택하고 준비된 문을 작성하면 데이터를 동적으로 피벗 해제하는 효율적인 방법입니다.