CASE문을 활용한 피벗

 

데이터 셰이핑

 데이터가 열과 행에 저장되는 형태를 조정하는 것을 데이터 셰이핑이라고 합니다.

• 필요한 데이터의 세밀도를 파악한다.
• 데이터 평탄화(특정 개체에 대한 데이터를 나타내는 행의 수를 줄이는 것)를 통해 데이터를 요약한다.

깔끔한 데이터 개념 (1) 변수는 열로 구성한다 (2) 관측값은 행을 구성한다 (3) 행과 열이 교차하는 셀을 값으로 한다

 

PT_NO을 기준으로 그룹화를 수행하고, 환자별 진료 예약수를 구해봅시다. (데이터 set : Programmers 진료과별 총 예약횟수 구하기)

SELECT PT_NO AS Patient, sum(APNT_NO) AS Appointment
FROM APPOINTMENT
GROUP BY 1;

 

피벗테이블 만들기

이 때, 특정 속성 값을 기준으로 새로운 열을 추가해볼 수 있습니다. CASE문을 사용하여 날짜(APNT_YMD), 진료과별 환자의 수를 구해봅시다.

진료과는 총 7개 (CS, NP, OS, FM, GS, OB, DR)가 있는 것을 확인했습니다.

SELECT DATE_FORMAT(APNT_YMD,'%y-%m-%d') AS Date,
SUM(CASE WHEN MCDP_CD = 'CS' THEN APNT_NO ELSE 0 END) AS CS,
SUM(CASE WHEN MCDP_CD = 'NP' THEN APNT_NO ELSE 0 END) AS NP,
SUM(CASE WHEN MCDP_CD = 'OS' THEN APNT_NO ELSE 0 END) AS OS,
SUM(CASE WHEN MCDP_CD = 'FM' THEN APNT_NO ELSE 0 END) AS FM,
SUM(CASE WHEN MCDP_CD = 'GS' THEN APNT_NO ELSE 0 END) AS GS,
SUM(CASE WHEN MCDP_CD = 'OB' THEN APNT_NO ELSE 0 END) AS OB,
SUM(CASE WHEN MCDP_CD = 'DR' THEN APNT_NO ELSE 0 END) AS DR
FROM APPOINTMENT
GROUP BY 1;

ELSE 0를 사용하면, 결과 데이터에 null값이 삽입되는 것을 방지할 수 있습니다. 다만, 집계를 위해 COUNT를 사용할 경우, 0이면 값이 존재한다고 인지함으로 주의해야 합니다.

 

UNION을 활용한 언피벗

열로 저장한 데이터를 행으로 변환해야하는 경우가 있습니다. 이러한 연산을 언피벗이라고 합니다. UNION연산을 활용하여 데이터를 행으로 변환해볼 수 있으며, 각 쿼리의 열 갯수가 통합하려는 쿼리의 열의 갯수와 동일해야 합니다.

SELECT country, '1980' AS year, year_1980 AS population FROM country_populations
UNION ALL
SELECT country, '1990' AS year, year_1990 AS population FROM country_populations
UNION ALL
SELECT country, '2000' AS year, year_2000 AS population FROM country_populations
UNION ALL
SELECT country, '2010' AS year, year_2010 AS population FROM country_populations;

 

피벗함수를 지원하는 경우, 다음과 같이 작성하면 됩니다.

SELECT *
FROM country_populations
	unpivott(population for year in (year_1980, year_1990, year_2000, year_2010));

 

 

***

책 <SQL로 시작하는 데이터 분석>을 참고하여 정리했습니다.