[SQL] PIVOT/UNPIVOT 함수 없이 PIVOT/UNPIVOT 하기
by 소라고동_0. 들어가며
SQL을 이용하다보면 특정 RDB 제품에서만 지원하는 함수들이 있습니다.
저는 업무를 할 때 SparkSQL 을 사용하는데 사용하고 싶은데 지원하지 않는 기능들이 몇 가지 있더라구요..
오늘은 그 중 하나인 PIVOT/UNPIVOT 함수를 이야기해보려 합니다.
0.1. PIVOT 이란?
우선 시작하기 전 일단 PIVOT 이 뭔지를 먼저 알아봅시다.
R 에서 tidyr 이라는 패키지를 공부하신 분이라면 조금 더 익숙할 것이라 생각합니다.
tidyr 의 다양한 기능 중 데이터의 포맷(format)을 Long/Wide format 으로 변환시켜주는 기능이 있는데, 그 기능이 떠올리시면 됩니다.
이러한 기능을 PIVOT/UNPIVOT 이라 부르고 이는 일부 RDB에서도 지원하는 함수입니다.
PIVOT 의 정의는 다음과 같습니다.
PIVOT
테이블에서 하나의 컬럼에 들어있는 행값들을 펼쳐 각각을 하나의 컬럼으로 만들어 주는 것.
위 그림에서는 ID2 컬럼에 들어있는 Col1, Col2, Col3 이라는 값들을 펼쳐 각각의 컬럼으로 만들어주었습니다.
즉, 컬럼의 행에 들어있는 값들을 회전시켜 열로 만들어준다고 생각하시면 되겠습니다.
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 식 + 집계함수을 이용해 간단히 집계를 할 수 있음.
혹시 글을 읽어보시고 더 좋은 방법이 있다면 댓글 부탁드립니다!!😋
'#1 언어 노트 > #1.1. SQL 노트' 카테고리의 다른 글
[SQL] MEDIAN 함수 없이 중앙값 구하기 (2) | 2022.07.24 |
---|---|
[SQL] SQL에서 정규표현식 활용하기 (3) | 2022.06.10 |
[SQL] 윈도우 함수(Window Function)의 소중함을 느껴보자 (6) | 2021.12.19 |
[SQL] 성능 관점에서의 서브쿼리(Subquery) (11) | 2021.09.26 |
[SQL] 성능 관점에서 보는 결합(Join) (15) | 2021.08.13 |
블로그의 정보
고동의 데이터 분석
소라고동_