[오라클, Oracle] PIVOT 절과 UNPIVOT 절 - 활용 예제

 

[동적 PIVOT]

동적 PIVOT은 쿼리 수행 시점에 동적으로 열이 변경되는 PIVOT을 말한다.

쿼리는 수행 전에 열의 개수가 결정되어야 하기 때문에 정적(static) 쿼리로는 동적 PIVOT이 불가능하다.

XML 키워드를 사용하면 반환되는 열이 1개로 고정되므로 동적 PIVOT이 가능하지만 활용도는 높지 않다.

결국 동적(Dynamic) 쿼리를 사용해야 동적 PIVOT을 수행할 수 있다.

 

아래 쿼리는 동적 PIVOT을 수행한다.

SQL*Plus는 대체 변수로 동적 쿼리를 생성할 수 있다.

DEF[INE] 명령어로 대체 변수를 선언하고, & 변수 형식으로 대체 변수를 참조했다.

 

DEF V_AF = 'SUM (SAL) AS SAL';

DEF V_IL = 'DEPTNO IN (10 AS D10, 20 AS D20)';

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (&V_AF FOR &V_IL)

ORDER BY 1;

 

아래 쿼리는 대체 변수를 변경하여 동적 PIVOT을 수행한다.

 

DEFINE V_AF = 'SUM (SAL) AS SAL, COUNT (*) AS CNT';

DEFINE V_IL = 'DEPTNO IN (10 AS D10)';

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (&V_AF FOR &V_IL)

ORDER BY 1;

 

[전체 열 UNPIVOT]

테이블의 전체 열을 UNPIVOT하여 값을 조회하거나 집계할 수 있다.

 

아래 쿼리는 테이블의 전체 열을 UNPIVOT한다.

열이 많은 행을 조회할 때 유용하다.

IN 절에 입력되는 값은 데이터 타입이 모두 동일해야 한다.

 

WITH W1 AS (

SELECT TO_CHAR (EMPNO) AS EMPNO, ENAME, JOB, TO_CHAR (MGR) AS MGR

, TO_CHAR (HIREDATE, 'YYYY-MM-DD') AS HIREDATE, TO_CHAR (SAL) AS SAL

, TO_CHAR (COMM) AS COMM, TO_CHAR (DEPTNO) AS DEPTNO

FROM EMP

WHERE EMPNO = 7788)

SELECT *

FROM W1

UNPIVOT (VALUE FOR COLUMN_NAME IN (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO));

 

UNPIVOT 절을 사용하면 열 값의 분포를 계산할 수 있다.

아래 쿼리는 JOB 열과 DEPTNO 열 값의 분포를 계산한다.

 

SELECT COLUMN_NAME, VALUE, COUNT (*) AS CNT

FROM (SELECT JOB, TO_CHAR (DEPTNO) AS DEPTNO FROM EMP)

UNPIVOT (VALUE FOR COLUMN_NAME IN (JOB, DEPTNO))

GROUP BY COLUMN_NAME, VALUE

ORDER BY 1, 2;

 

[행령 전환 집계]

DW 시스템은 행을 열로 전환한 후 수식을 통해 값을 계산하고, 계산된 값을 다시 행으로 전환하는 집계 방식을 사용한다.

 

아래 쿼리는 DW 시스템에서 자주 사용하는 집계 쿼리 방식이다.

W1에서 부서별 SAL을 열로 PIVOT하고, W2에서 표현식으로 열을 계산한 후, 메인 쿼리에서 계산한 열을 행으로 UNPIVOT 한다.

20번 부서의 CLERK은 타 부서 SAL의 평균 대비 169% 수준의 SAL을 받는 것으로 계산된다.

 

WITH W1 AS (

SELECT JOB

, NVL (D10_SAL, 0) AS D10_SAL

, NVL (D20_SAL, 0) AS D20_SAL

, NVL (D30_SAL, 0) AS D30_SAL

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL

FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30)))

, W2 AS (

SELECT A.*

, D10_SAL / NVL (NULLIF (D20_SAL + D30_SAL, 0) / 2, D10_SAL) AS D10_RATIO

, D20_SAL / NVL (NULLIF (D10_SAL + D30_SAL, 0) / 2, D20_SAL) AS D20_RATIO

, D30_SAL / NVL (NULLIF (D10_SAL + D20_SAL, 0) / 2, D30_SAL) AS D30_RATIO

FROM W1 A)

SELECT JOB, DEPTNO, SAL, ROUND (RATIO, 2) * 100 AS RATIO

FROM W2

UNPIVOT ((SAL, RATIO) FOR DEPTNO IN ((D10_SA, D10_RATIO) AS 10

, (D20_SAL, D20_RATIO) AS 20

, (D30_SAL, D30_RATIO) AS 30))

ORDER BY 1, 2;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.