Oracle PIVOT() 함수 사용

PIVOT() 함수 개요

위 그림 처럼 행을 열로 변환해주는 함수로 통계에 많이 사용된다고 한다. 실제로 회사에서 통계 페이지를 구성하는 쿼리에 사용되고 있었다. 행을 열로 바꾼다는 개념이 이해가 되지 않아서 사용법을 정리한다.

SELECT * FROM (피벗 할 쿼리문)
 PIVOT(
        그룹함수(칼럼)
        FOR 피벗 할 칼럼
        IN (항목1, 항목2, 항목3 ...)
      );

그룹함수는 SUM(), AVG(), COUNT() 등 컬럼을 묶어주는 함수들을 사용한다. 회사에서는 대부분 스킬별 통화 시간을 계산하는 용도로 사용되었기 때문에 SUM()이 사용된 쿼리문이 많았다.

신기하다고 생각했던건 SELECT * FROM (쿼리문) 의 형태로 내부에 서브쿼리를 작성하는 형태가 아니면 오류를 전달했다. 왜 바로 pivot 할 쿼리문을 전달할 수 없도록 만들었는지는 모르겠다.

굳이 추측하자면 pivot을 할 칼럼외 남은 행 또한 기준의 역할을 해서 아닐까 생각한다.

FOR 피벗 할 칼럼 IN (항목1, 항목2, 항목3 ...)
부분에서 항목값은 수동으로 넣어주어야 한다. 만약 테이블에 항목 값이 없는 경우는 NULL이 나온다.

예제 쿼리

오라클 EMP 스크립트로 테이블을 만들고 예제 쿼리를 만들어 보았다.

SELECT * FROM (SELECT job, deptno , sal   FROM emp )
PIVOT(
    SUM(sal) FOR deptno IN ('10', '20', '30')
)

맨처음 그림처럼 deptno와 job 별 sal의 합계를 출력한다. 만약에 deptno에 40을 추가하면
값이 없어 아래에는 모두 NULL이 표시되지만 컬럼에 표시는 된다. (값이 없더라도 쿼리에 적은 컬럼은 무조건 표시된다는 의미 )

PIVOT() 여러 칼럼 수행

PIVOT을 할때 한번에 여러 칼럼을 출력하는 것도 가능하다.
위에 쿼리문은 job별, deptno별 sal의 합을 출력해 주었는데 SAL의 평균도 같이 출력할수 있다.

SELECT * FROM (SELECT job, deptno , sal   FROM emp )
PIVOT(
    SUM(sal) AS 합계, AVG(sal) AS 평균  FOR deptno IN ('10', '20', '30', '40')
)

이런 식으로 그룹함수를 여러개 작성하면 PIVOT을 여러 칼럼으로 만들수 있다. 이떄 반드시 AS를 사용하여 별칭을 정해 주어야 한다. (별칭이 없으면 칼럼이 구분되지 않음으로)

칼럼을 하나만 하는게 아니라 여러개를 사용하는 PIVOT도 만들수 있다. 아래 쿼리는 job별 , deptno 별 sal의 합계와 누가 sal을 가장 많이 받는지 출력하는 쿼리문이다.

SELECT * FROM (SELECT job, deptno , sal, ename   FROM emp )
PIVOT(
    SUM(sal) AS 합계, MAX(ename) AS 최고연봉  FOR deptno IN ('10', '20', '30')
)

익숙해지려면 시간이 필요할듯...