Pivot queries (11g New Feature)

이번 칼럼은 11g에서 추가된 Pivot & Unpivot query에 대해 소개해보고자 합니다.

 우선 테스트를 위해 샘플 데이터를 만들어 보겠습니다.

이제 Pivot query가 어떤 건지 데이터를 통해 확인해 보겠습니다.

위의 조회 결과는 EMP 테이블에서 JOB, DEPTNO 데이터를 기준으로 SAL 값을 SUM한 내용인데, 이 데이터를 JOB을 기준으로 DEPTNO 데이터별로 결과를 조회하고자 할 경우, 10g 버전에서는 아래와 같이 SQL을 구성해야 했습니다.

즉, DEPTNO 값을 DECODE() 함수를 사용하여 가로로 나열한 후 SUM() 함수를 사용하여 SAL 데이터를 계산하였는데, 이 방식의 단점은 DEPTNO의 상수값을 미리 알고 있어야 하고, 이를 DECODE()를 통해 Select-List에 나열을 해야 해서, 직관적이지 못합니다. 

이를 보완하기 위해 11g에서는 Pivot 기능을 위한 query를 할 수 있는 구문을 추가 하였으며, 그 구문은 아래와 같습니다.

이제 아래 예제를 통해 사용방법을 살펴보겠습니다.

위의 예제는 Pivot 구문을 사용한 예제인데, EMP 테이블에서 JOB, DEPTNO, SAL 데이터를 인라인 뷰에서 가져온 후, PVIOT 구문을 사용하여 JOB을 기준으로 집계해야 하는 컬럼을 pivot_clause에, 그리고 pviot 대상 컬럼을 pivot_for_clause에 위치시킵니다.

그리고 PIVOT 구문의 IN 절에서는 FOR 에 위치한 컬럼값을 필터랑 하는 역할을 하며, 이 때, SUM()을 사용하기 위해 GROUP BY 구문을 사용할 필요는 없습니다.

여기서 JOB, DEPTNO, SAL 3개 컬럼만 사용하였는데, 나머지 컬럼들을 보고 싶을 경우 어떻게 해야 할까요?

만약 모든 컬럼을 보고 싶을 경우, Select-List에 모두 나열을 할 수 있으며, 다만 PIVOT 구문 안에 들어가는 컬럼들만 형태가 변합니다. 하지만 이렇게 SQL을 구현할 경우, SAL 컬럼을 SUM() 할 때 기준컬럼이 Unique 컬럼인 EMPNO도 들어가기 때문에 전체 건수가 다 나오므로 PIVOT을 사용하는 목적인 특정 컬럼을 GROUPING 하는 것은 못하게 되며, 10g일 경우 아래와 같을 것입니다.

 

3번째 예제는, PIVOT절로 구성된 데이터를 필터링하기 위해, WHERE절에서 기준컬럼 JOB에 조건을 추가할 수 있음을 확인하는 예제입니다.

4번째 예제는, 기초집합을 PIVOT_EMP_V 뷰로 생성한 후, 이를 가지고 PIVOT 데이터를 만드는 예제인데, 이전과 크게 다르지 않은 예제이지만, PIVOT 컬럼에 Alias를 줄 수 있음을 확인할 수 있습니다. 

Sample #3을 보면 Alias를 안 줄 경우 PIVOT 상수값을 컬럼 이름으로 출력하지만, 이번처럼 Alias를 줄 경우 출력 시 컬럼 이름이 부여됩니다.

5번째 예제는, PIVOT 절에 SUM() 이외에 COUNT() 함수를 추가한 예제인데, 이를 통해 PIVOT 절에는 한개 이상의 그룹함수 사용이 가능함을 알 수 있습니다.

6번째 예제는, 재미난 Case인데 PIVOT FOR절에 2개 이상의 컬럼을 대상으로 할 경우, 이를 가로로 출력을 합니다. 즉, 이전 예제에서는 JOB을 기준으로 SAL 값을 DEPTNO별로 나열을 하였는데, 이렇게 2개를 지정할 경우 해당 데이터만 출력을 하고 추가로 세로로 표현하는 것이 아닌 가로로 데이터를 출력해 줍니다.

7번째 예제는, 6번째 예제와 FROM절 이하 구문이 동일하지만 Select-List에서 대상 컬럼들을 직접 지정하여 2개 컬럼만 출력하는 예제입니다. 여기서 주의할 점은 PIVOT 절 안의 Alias를 잘 파악하여 Select-List에 명시를 해주어야 하며, 그렇게 않을 경우 당연한 이야기지만 구문에러가 납니다.

이에 대해 아래 예제에서 살펴보겠습니다.

8번째 예제의 첫 번째 SQL에서는 Select-List를 잘 못 기입하여 구문 에러가 난 것을 확인할 수 있으며, 두 번째 SQL에서 ‘D10_SUM’이라고 표기하여 에러가 나지 않고 정상적인 결과가 출력됨을 알 수 있습니다.

마지막 예제에서는, PIVOT 절에서 그룹 함수를 사용하지 않고 컬럼을 그냥 지정할 경우, 에러가 났음을 확인할 수 있으며, 이를 통해 반드시 그룹함수를 사용해야 함을 알 수 있습니다.

이상으로 11g에서 새롭게 소개된 PIVOT 함수에 대해 알아보았습니다. 다음 글에서는 PIVOT의 반대되는 개념인 UNPIVOT에 대해 소개하고자 하며, 이번 시간에는 여기서 글을 마치도록 하겠습니다.

reference site : http://www.oracle-developer.net/display.php?id=515