머리말
오라클에서 제공하는 리포팅 함수 중 Pivot 및 Unpivot 기능은 Oracle 11g 버전부터 제공하는 강력한 함수이다. Pivot 함수는 행을 열로 뒤집는 함수지만, 반대로 Unpivot 함수는 열을 행으로 뒤집는 함수다. 이러한 강력한 함수가 지원되기 이전에는 decode, case문을 이용해서 로직으로 구현했다. Unpivot 함수는 약간의 제약사항과 규칙만 지켜주면 훌륭한 함수로 사용할 수 있다. 본 포스팅에서 주의 깊게 생각해야 될 키워드는 칼럼명과 데이터 영역이므로 각 용어들에 대해서 반복적으로 언급할 것이다. 예제 데이터는 오라클에서 교육용으로 제공해 주는 EMP 테이블이며 자세한 데이터는 아래와 같다. EMP 테이블은 인터넷 검색을 통해 생성 스크립트를 쉽게 얻을 수 있으며 직접 생성하기에도 부담 없다. 예제로 사용할만한 테이블과 데이터가 있다면 굳이 오라클 학습용 EMP 테이블을 사용할 필요는 없다. 본 포스팅에서는 Oracle 11g 버전부터 제공되는 Unpivot 함수에 대해서 소개하도록 한다.
Unpivot 함수 사용법
Unpivot 함수는 결론부터 언급하자면 리포팅 함수다. 리포팅이란 의미 있는 분류로 데이터를 합산하거나 평균 또는 최대 값을 구하는 것과 같이 그룹함수의 결과를 의미한다. 그룹함수의 결과를 얻기 위해서는 GROUP BY 절을 이용해야 한다. 하지만 컬럼명 자체가 데이터로 취급되어서 리포팅 결과에 반영돼야 하는 경우가 간혹 발생하지만, 컬럼명 자체는 데이터로 취급하지 않기 때문에 GROUP BY 절의 집계 대상이 될 수 없다. 예를 들어 직업별 월급과 성과급 합계를 아래와 같은 결과로 변환되길 바라는 경우가 해당된다. 이러한 한계점을 극복하기 위해 컬럼명을 데이터 영역으로 끌어 내려서, 원하는 GROUP BY 쿼리를 수행하도록 돕는 역할을 Unpivot 함수로 이해하면 어렵지 않다.
예제로 사용할 테이블 및 데이터가 준비되었다면 필요한 데이터만 조회할 수 있도록 컬럼을 추려내야 한다. 오라클에서 제공한 EMP 테이블의 데이터를 그대로 사용할 경우 Unpivot에 대한 이해가 어렵고 복잡해진다. 그렇기 때문에 With 절을 사용해서 Unpivot 함수 학습에 사용할 컬럼만 추려내도록 한다. 본인의 경우 아래의 쿼리를 사용해서 조회되는 데이터를 기준으로 설명한다.
SELECT ENAME, SAL FROM EMP
지금부터 위에서 조회된 결과 값을 열을 행으로 뒤집을 것이다. 열을 행으로 뒤집는다니 Unpivot 함수의 수행 결과가 다양한 모습으로 상상되지만 아래와 같이 통째로 기울어진 모습으로 상상하면 곤란하다.
GROUP BY 절 없이 Unpivot 함수만 수행한 결과는 아래의 사진과 같이 컬럼명 자체를 데이터로 취급한 뒤, 각 컬럼에 대해 COL, VAL이라는 컬렴명을 붙여준 것으로 이해하면 어렵지 않다. 이제 컬럼명이 데이터 영역으로 편입되어서 데이터로써 취급을 받을 수 있게 되었다. 컬럼명이 데이터 영역으로 편입되었으니 GROUP BY 함수를 이용해 다양한 리포트를 출력할 수 있게 되었다. 본 포스팅에서는 COL 컬럼을 컬럼명 영역, VAL 컬럼을 데이터 영역으로 임의로 칭하도록 한다.
Unpivot 함수의 문법은 아래의 사진과 같이 UNPIVOT, UNPIVOT FOR, UNPIVOT IN 총 3개의 절이 필요하다. 각 절의 의미는 위에서 설명한 내용을 이해했다면 어렵지 않게 숙지할 수 있다.
UNPIVOT 절: 데이터 영역의 컬럼명을 정의
Unpivot 함수를 사용할 때는 가장 먼저 데이터로써 취급받기 원하는 컬럼명을 선택해야 한다. 선택할 컬럼명은 그룹 함수에서 사용이 가능한 숫자 타입 데이터만 가능하다. 여기서 데이터 영역의 컬럼명을 지정해 주는 곳이 UNPIVOT 절이다. 아래와 같은 결과를 얻기 위해서는 데이터 영역의 컬럼명을 정의해야 한다. 본인은 UNPIVOT 절에 DATA를 입력해서 아래결과를 출력할 수 있었다. 어떤 컬럼명을 데이터로써 취급받을지는 아직 정의하지 않았지만, 그 데이터를 표현할 컬럼 이름을 미리 정의한다고 이해하면 좋을 것 같다.
UNPIVOT FOR 절: 컬럼명 영역의 컬럼명을 정의
UNPIVOT 절을 이해했다면 UNPIVOT FOR 절은 어렵지 않다. Unpivot 함수를 수행하면 데이터 영역의 기존 컬럼명이 데이터로써 취급을 받을 예정이다. 이때 컬럼명 영역의 컬럼명을 정의해 주면 된다. 본인은 아래의 사진과 같이 UNPIVOT 절에 COL을 입력해서 정의했다.
UNPIVOT IN 절: 데이터 취급을 받고 싶은 컬럼명을 지정
UNPIVOT FOR 절을 이해했다면 UNPIVOT IN 절도 간단하게 이해할 수 있다. 데이터 취급을 받고 싶은 컬럼을 지정해 주면 된다. 아래와 같은 예제를 기준으로 설명하면, SAL 컬럼명을 데이터로써 취급받기 위해 UNPIVOT IN 절에 (SAL)을 입력한 결과다. 데이터로써 취급받기 위한 컬럼명은 복수개 지정 가능하니 직접 테스트해 보길 권한다.
지금까지 설명한 내용을 간략히 정리해 보자면 아래와 같이 풀어서 설명할 수 있으며 쿼리로 작성한다면 아래의 사진과 같이 작성할 수 있다.
SAL 컬럼명을 데이터로 취급한 뒤, 각각 COL, DATA 컬럼명으로 출력해 주세요.
여기까지가 Unpivot 관련된 이야기의 끝이다. 본 포스팅에서 설명하는 내용이 잘 전달되었다면 Unpivot 함수를 읽어 내리는데 큰 어려움이 없을 것이라 생각된다. 추가로 연습해 보자면 UNPIVOT IN 절에 숫자 데이터 형식의 컬럼을 두 개 넣은 예제는 아래와 같다. IN절에 정의한 컬럼들이 COL 컬럼으로 모두 출력되었으며 널값은 출력되지 않았다.
조금 더 심화된 내용을 연습해 보자면, 아래의 예제는 JOB 컬럼과 SAL 컬럼을 활용해서 직업별 월급의 합계를 UNPIVOT 함수로 수행한 결과다. With 절의 데이터를 JOB, SAL로 정제한 뒤 UNPIVOT 된 결과를 GROUP BY 절에서 활용했다. 무언가 이상한 점이 있을 것이다. 아래의 결과물은 굳이 Unpivot 함수를 활용하지 않아도 출력이 가능하다. 일반적으로 UNPIVOT FOR 절에서 정의한 컬럼명이 GROUP BY 절에서 사용돼야 의미 있는 데이터를 출력할 수 있다.
꼬리말
Unpivot 함수에 대해 비교적 쉬운 방법으로 접근하려고 많은 고민을 했다. 현재 당장은 어렵지 않게 이해할 수 있지만 시간이 흐름에 따라 현재의 기억은 흐려지기 마련이다. 그렇기 때문에 최대한 Unpivot 함수의 기본적인 개념을 이해할 수 있도록 설명하려 노력했다. 본인이 전달하려고 한 내용이 잘 전달되었을지 궁금한 마음이지만, 오랜 시간 고민하고 작성한 Oracle 11g 버전에서 제공하는 UNPIVOT 함수에 대한 설명을 이로써 마무리 짓도록 한다.
소중한 댓글 (0)