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

 

지난 포스팅에서는 데이터를 PIVOT 하는 내용에 대해서 알아보았었다.

https://walkingfox.tistory.com/166

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

 

언피봇(un-pivot) 이란,  위 그림에서처럼 속성(attributes)들이 컬럼으로 되어있는 형태에서 속성들을 행(row)으로 쌓여있는 형태로 만드는 것이다.

 

이번 포스팅에서는 presto (또는 athena) 에서 un-pivot 하는것에 대해서 설명하려고 한다.

 

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

실행을 위해서 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)
    )
, list1 as (
    select user_id
        , sum(score) as sum
        , avg(score) as avg 
        , min(score) as min 
        , max(score) as max
    from list 
    group by user_id 
    )
select * from list1

list1 이라는 테이블은 위의 그림과 같은 데이터를 갖는 테이블이다. (위 쿼리는 그 데이터를 만들기 위한 과정일 뿐이다.)

 

(2) 약간(?) 복잡해 보일 수 있는데, 원리는 cross join 과 unnest 를 이용하여 밑으로 펼쳐 내리는 것이다. 

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)
    )
, list1 as (
    select user_id
        , sum(score) as sum
        , avg(score) as avg 
        , min(score) as min 
        , max(score) as max
    from list 
    group by user_id 
    )
select t1.user_id, t2.subject, t2.score
from list1 t1
cross join unnest (
    array['sum', 'average', 'min value', 'max value'], 
    array[sum, avg, min, max]
) t2 (subject, score)
order by 1, 2

위 쿼리가 실행되면 아래와 같이 밑으로 un-pivot 된 데이터를 확인 할 수 있다. 

위에서 핵심은 아래부터 7줄에 해당하는 select 문장이다. 그 윗 부분은 그냥 데이터를 만들기 위한 부분이다.

 

이 un-pivot 과정에서는 unnest 구문과 array 라는 배열로 만들어주는 함수를 사용했는데, 위 구분이 지원되는 DB에서 사용이 가능하다.