[SQL] PIVOT/UNPIVOT 함수 없이 PIVOT/UNPIVOT 하기

0. 들어가며

SQL을 이용하다보면 특정 RDB 제품에서만 지원하는 함수들이 있습니다.

저는 업무를 할 때 SparkSQL 을 사용하는데 사용하고 싶은데 지원하지 않는 기능들이 몇 가지 있더라구요..

오늘은 그 중 하나인 PIVOT/UNPIVOT 함수를 이야기해보려 합니다.

 

 

0.1. PIVOT 이란?

우선 시작하기 전 일단 PIVOT 이 뭔지를 먼저 알아봅시다.

R 에서 tidyr 이라는 패키지를 공부하신 분이라면 조금 더 익숙할 것이라 생각합니다.

tidyr 의 다양한 기능 중 데이터의 포맷(format)을 Long/Wide format 으로 변환시켜주는 기능이 있는데, 그 기능이 떠올리시면 됩니다.

이러한 기능을 PIVOT/UNPIVOT 이라 부르고 이는 일부 RDB에서도 지원하는 함수입니다.

PIVOT 과 UNPIVOT

PIVOT 의 정의는 다음과 같습니다.

PIVOT
 테이블에서 하나의 컬럼에 들어있는 행값들을 펼쳐 각각을 하나의 컬럼으로 만들어 주는 것.
 위 그림에서는 ID2 컬럼에 들어있는 Col1, Col2, Col3 이라는 값들을 펼쳐 각각의 컬럼으로 만들어주었습니다.
 즉, 컬럼의 행에 들어있는 값들을 회전시켜 열로 만들어준다고 생각하시면 되겠습니다.

PIVOT 의 원리를 그림으로 나타내봤습니다

UNPIVOT
 UNPIVOT 은 PIVOT 과 반대로 열로 펼쳐진 값들을 다시 하나의 열로 모아주는 것을 말합니다.
 PIVOT 의 반대의 개념이라고 생각하시면 됩니다.

 

 

1. 실습 데이터 살펴보기

먼저 실습에 사용한 테이블을 소개하겠습니다.

select * from person;

실습 테이블

" 이름 | 나이 | 성별 | 키 | 몸무게 " 의 레이아웃을 가지고 있는 테이블입니다.

이 데이터를 이용해서 성별/연령대그룹별 평균키, 평균몸무게를 구해보려합니다.

 

1.1. 연령대별로 그룹화하기

select name, age
	, case 
		when age < 20 then '어린이'
		when age between 20 and 69 then '성인'
		when age >= 70 then '노인'
		else null end as age_group
	, gender
    , height, weight

from person;

 

1.2. 그룹별 평균키, 평균 몸무게 구하기

select gender, age_group
	, avg(height) avg_height
	, avg(weight) avg_weight

from (
	select name, age
	, case 
		when age < 20 then '어린이'
		when age between 20 and 69 then '성인'
		when age >= 70 then '노인'
		else null end as age_group
	, gender
	, height, weight

	from person
) a

group by 1,2
order by 1,2;

이제 진짜 실습에 사용할 데이터가 만들어졌습니다.

위 데이터의 gender 열에 있는 'M', 'W' 값들을 각각의 열로 만들어주는 PIVOT을 진행해보도록 하겠습니다.

 

 

2. PIVOT 함수 없이 PIVOT 하기

PIVOT 을 할 때의 핵심은 Case When 식을 이용하는 것입니다.

일단 실습의 편리함을 위해 위 결과값을 view로 저장한 뒤 진행했습니다.

create view sample
as
select gender, age_group
	, avg(height) avg_height
	, avg(weight) avg_weight

from (
	select name, age
	, case 
		when age < 20 then '어린이'
		when age between 20 and 69 then '성인'
		when age >= 70 then '노인'
		else null end as age_group
	, gender
	, height, weight

	from person
) a

group by 1,2
order by 1,2;

 

2.1. Case When 식을 이용해 PIVOT 진행하기

select age_group
	, case when gender = 'M' then avg_height end as m_avg_height
	, case when gender = 'W' then avg_height end as w_avg_height
	, case when gender = 'M' then avg_weight end as m_avg_weight
	, case when gender = 'W' then avg_weight end as w_avg_weight
from sample;

Case When 식을 이용해서 gender = 'M' 일때의 avg_height 를 m_avg_height 열로 만들어주었습니다.

다른 열도 똑같은 원리를 이용해서 만들었습니다.

그런데 뭔가 조금 아쉽습니다. 

중간중간에 값이 다 채워지지않고 null 값이 들어가있습니다.

그래서 아래의 그림처럼 null 값을 채워넣어야합니다.

 

2.2. Case When 식 + 집계함수를 통해 PIVOT 마무리

위 그림처럼 null 값을 채우기 위해서는 집계함수를 이용해주면 됩니다.

sum, avg, min, max 등 어떤 집계함수를 사용해도 상관은 없지만 저는 max 집계함수를 이용했습니다.

select age_group
	, max(case when gender = 'M' then avg_height end) as m_avg_height
	, max(case when gender = 'W' then avg_height end) as w_avg_height
	, max(case when gender = 'M' then avg_weight end) as m_avg_weight
	, max(case when gender = 'W' then avg_weight end) as w_avg_weight

from sample
group by 1;

이렇게 원하는 모습으로 데이터의 포맷이 변경되었습니다.~

 

 

 

3. UNPIVOT 함수 없이 UNPIVOT 하기

이번에는 UNPIVOT을 진행해보도록 하겠습니다.

이것저것 찾아보다 이 방법을 발견해서 사용하고 있지만 혹시 더 좋은 방법이 있다면 댓글 부탁드립니다!!

우선 PIVOT 이 완료된 테이블을 sample2 라는 view로 저장한 뒤 진행했습니다.

 

3.1. 열에 들어갈 항목으로 테이블 만들기

우선 UNPIVOT 할 때 다시 열로 뭉쳐질 값들로 테이블을 하나 만들어줍니다.

select 'M' as gender union all
select 'W'

 

3.2. 만들어진 테이블과 UNPIVOT할 테이블 CROSS JOIN

방금 만든 테이블을 UNPIVOT 시킬 테이블과 CROSS JOIN 해줍니다.

select *
from sample2 a
cross join (
  select 'M' as gender union all
  select 'W'
) b

CROSS JOIN 을 했기 때문에 모든 sample2 의 행에 gender 라는 컬럼이 붙고, 행 수도 2배로 늘어났습니다..

 

3.3. Case When 식으로 포맷 바꿔주기

저 상태에서 Case When 식을 이용해 UNPIVOT을 마무리해줍니다.

select age_group, gender
	, case 
		when gender = 'M' then m_avg_height 
		when gender = 'W' then w_avg_height 
		end as height
	, case 
		when gender = 'M' then m_avg_weight 
		when gender = 'W' then w_avg_weight 
		end as weight

from sample2 a
	cross join (
		select 'M' as gender union all
		select 'W'
	) b

이렇게 다시 Long Foramt 으로 돌아온 것을 확인할 수 있습니다.

 

 

4. 끝마치며

이렇게 이번에는 PIVOT/UNPIVOT에 대해서 알아봤습니다.

이번 포스팅에서 기억하면 좋을 내용을 정리하면,

1. PIVOT과 UNPIVOT의 개념

2. PIVOT/UNPIVOT을 할 땐(포맷을 바꿔줄 땐) 기준이 되는 열이 필요함
   = UNPIVOT 시 기준이 되는 열이 없어 CROSS JOIN 을 통해 이를 만들어주었음.

3. Case When 식 + 집계함수을 이용해 간단히 집계를 할 수 있음.

 

혹시 글을 읽어보시고 더 좋은 방법이 있다면 댓글 부탁드립니다!!😋