[SQL] 데이터 PIVOT 하기 (presto, athena)

위 그림에서 파란색의 화살표와 같이 속성(attributtes)과 값(values) 들이 쌓여있는 형태에서,

속성들을 열(컬럼)의 형태로 변형하는 것을 피봇(pivot) 한다고 얘기한다. 

그리고 그 반대의 과정을 언피봇(unpivot) 한다고 얘기한다.

 

이 포스팅에서는 presto (또는 athena) 에서 SQL로 피봇하는 것을 성명하려고 한다.

 

(1) 아래와 같은 형태의 테이블이 있다.

실행을 위해서 sql 로 데이터를 아래와 같이 만들었다.

with 
list (user_id, subject, score) as ( values 
    (1, '국어', 90)
    , (1, '영어', 85)
    , (1, '수학', 60)
    , (2, '국어', 100)
    , (2, '영어', 50)
    , (2, '수학', 75)
    , (3, '국어', 77)
    , (3, '영어', 88)
    , (3, '수학', 99)
    )
select * from list

여기서 list 라는 테이블은 위와 같은 결과를 갖는다.

 

(2) subject 라는 속성값들을 가로로 배치하기 (pivot) 위해서 case when 구문을 이용하여 옆으로 펼친다.

with 
list (user_id, subject, score) as ( values 
    (1, '국어', 90)
    , (1, '영어', 85)
    , (1, '수학', 60)
    , (2, '국어', 100)
    , (2, '영어', 50)
    , (2, '수학', 75)
    , (3, '국어', 77)
    , (3, '영어', 88)
    , (3, '수학', 99)
    )
select user_id
    , case when subject = '국어' then score end as korean
    , case when subject = '영어' then score end as english 
    , case when subject = '수학' then score end as math 
from list

위 쿼리가 실행되면 아래와 같은 결과를 얻을 수 있다.

하지만 사이 사이 null 값이 존재하는 내용이다.

 

(3) 이를 user_id 별로 합쳐주는 마지막 과정이 필요하다.

with 
list (user_id, subject, score) as ( values 
    (1, '국어', 90)
    , (1, '영어', 85)
    , (1, '수학', 60)
    , (2, '국어', 100)
    , (2, '영어', 50)
    , (2, '수학', 75)
    , (3, '국어', 77)
    , (3, '영어', 88)
    , (3, '수학', 99)
    )
select user_id
    , min(case when subject = '국어' then score end) as korean
    , min(case when subject = '영어' then score end) as english 
    , min(case when subject = '수학' then score end) as math 
from list 
group by user_id

이렇게 user_id 로 group by 연산을 하여 하나의 값을 가져오도록 한다. 

각 user_id 와 subject 별로 값이 하나이기 때문에 min 함수이던 max 함수이던 같은 결과를 얻는다. 

결과는 아래와 같다.

참고로, 피봇의 과정에서 사용한 문법적인 내용이 

표준 sql 인 case when 구문과 group by 구문이기 때문에 어떠한 DB에서도 사용할 수 있다. 

 

다음 글에서는 unpivot 하는 내용에 대해서 알아볼 것이다.

https://walkingfox.tistory.com/167

[SQL] 데이터 UN-PIVOT 하기 (presto, athena)