Pivot and Unpivot

다양한 통계정보를 조회해야 하는 경우 다음과 같이 사용할 수 있다.

SQL> SELECT deptno

          , SUM(DECODE(job, 'CLERK', sal)) AS "Clerk"

          , SUM(DECODE(job, 'MANAGER', sal)) AS "Manager"

          , SUM(DECODE(job, 'SALESMAN', sal)) AS "Salesman"

       FROM emp

      GROUP BY deptno;

DEPTNO Clerk Manager Salesman

------ ----- ------- --------

30      950  2850    5600

20     1900  2975

10     1300  2450

DECODE는 행의 제한을 두지 않는 범위 내에서 다양한 조건식을 평가 할 수 있고 그에 따른 다양한 표현식을 추출 할 수 있으므로 그 사용의 범위가 다양할 수 있다. 하지만 위와 같은 형태로 복잡한 조건식을 평가 하거나 여러 컬럼을 이용하여 조건식을 평가 해야 하는 경우 문장의 복잡성과 성능에 문제점을 가지고 있다.

Oracle 11g 부터는 PIVOT과 UNPIVOT 절을 통해서 보다 쉽게 위의 결과를 만들어 사용할 수 있다.

SQL> SELECT *

       FROM (SELECT deptno

                  , job

                  , sal

               FROM emp) PIVOT (SUM(sal) FOR job IN ( 'CLERK' AS "Clerk"

                                                    , 'MANAGER' AS "Manager"

                                                    , 'SALESMAN' AS "Salesman"));

DEPTNO CLERK MANAGER SALESMAN

------ ----- ------- --------

30      950  2850    5600

20     1900  2975

10     1300  2450

다양한 PIVOT, UNPIVOT의 사용법을 확인하고 차이를 확인 해보자.

PIVOT

실습에 사용할 sales_view를 생성한다. Oracle DB를 설치시 생성된 Sample Schema SH 계정의 테이블을 사용한다.

SQL> CREATE OR REPLACE VIEW sales_view AS

     SELECT prod_name AS product

          , country_name AS country

          , channel_id AS channel

          , SUBSTR(calendar_quarter_desc, 6, 2) AS quarter

          , SUM(amount_sold) AS amount_sold

          , SUM(quantity_sold) AS quantity_sold

       FROM sh.sales

          , sh.times

          , sh.customers

          , sh.countries

          , sh.products

      WHERE sales.time_id = times.time_id

        AND sales.prod_id = products.prod_id

        AND sales.cist_id = customers.cust_id

        AND customers.country_id = countries.country_id

      GROUP BY prod_name, country_name, channel_id, SUBSTR(calendar_quarter_desc, 6, 2);

생성된 view의 결과를 확인 해본다.

SQL> SELECT product

          , country

          , channel

          , quarter

          , amount_sold

          , quantity_sold

       FROM sales_view;

PRODUCT COUNTRY                  CHANNEL QUARTER QUANTITY_SOLD

------- ------------------------ ------- ------- -------------

Y Box   United States of America 9       01        1

Y Box   Germany                  3       03      100

Y Box   United States of America 3       02      475

Y Box   Germany                  4       02       27

Y Box   Italy                    4       02       17

SALES_VIEW는 제품별 판매 수량을 나라별, 채널별, 분기별로 표시한다.

위의 결과를 아래와 같이 표시하면,

PRODUCT                  '01' '02' '03' '04'

------------------------ ---- ---- ---- ----

Y Box                    1455 1766 1716 1992

Xtend Memory             3146 4121 4122 3802

Unix/Windows 1-user pack 4259 3887 4601 4049

Standard Mouse           3376 1699 2654 2427

제품별 판매 수량을 분기별로 하나의 행으로 표현하고자 한다면 PIVOT 절을 이용하면 된다. 즉, 여러 행으로 표현 되는 데이터의 집합을 하나의 행으로 묶어서 표현 하고자 할 때 PIVOT을 사용한다.

SQL> SELECT *

       FROM (SELECT product

                  , quarter

                  , quantity_sold

               FROM sales_view) PIVOT (SUM(quantity_sold) FOR quarter IN ('01', '02', '03', '04'))

      ORDER BY product DESC;

PIVOT 절은 GROUP Function을 포함한 계산식을 정의하며 FOR 절은 값을 구분할(DECODE 사용시 조건식이 정의될) 컬럼 이름과 IN으로 값을 정의한다. 이때 IN 연산자는 Sub Query를 포함 할 수 없다. Alias 정의도 가능하다.

SQL> SELECT *

       FROM (SELECT product

                  , quarter

                  , quarntity_sold

               FROM sales_view) PIVOT (SUM(quantity_sold) FOR quarter IN ('01' AS Q1, '02' AS Q2, '03' AS Q3, '04' AS Q4))

      ORDER BY product DESC;

조건식의 평가는 여러 컬럼을 사용할 수 있다.

SQL> SELECT *

       FROM (SELECT product

                  , channel

                  , quarter

                  , quantity_sold

               FROM sales_view) PIVOT (SUM(quantity_sold) FOR (channel, quarter) IN ((3, '01') AS direct_sales_q1, (4, '01') AS internet_sales_q1))

      ORDER BY product DESC;

PRODUCT                  DIRECT_SALES_Q1 INTERNET_SALES_Q1

------------------------ --------------- -----------------

Y Box                     771             253

Xtend Memory             1935             350

Unix/Windows 1-user pack 2544             397

Standard Mouse           2326             256

GROUP Function 또한 여러 개 사용할 수 있다.

SQL> SELECT *

       FROM (SELECT product

                  , channel

                  , amount_sold

                  , quantity_sold

               FROM sales_view) PIVOT (SUM(amount_sold) AS sums, SUM(quantity_sold) AS sumq FOR channel IN (3 AS dir_sales, 4 AS int_sales))

      ORDER BY product DESC;

PRODUCT                  DIR_SALES_SUMS DIR_SALES_SUMQ INT_SALES_SUMS INT_SALES_SUMQ

------------------------ -------------- -------------- -------------- --------------

Y Box                    1081050.96     3552           382767.45      1339

Xtend Memory              217011.38     8562            40553.93      1878

Unix/Windows 1-user pack  153199.63     6140            28768.04      1195

Standard Mouse            174592.24     5106            27858.84       904

UNPIVOT

UNPIVOT은 PIVOT의 반대 되는 개념이다. 다음의 테이블을 생성하고 그 결과를 확인 해보자.

SQL> CREATE TABLE pivotedtable AS

     SELECT *

       FROM (SELECT product

                  , quarter

                  , amount_sold

                  , quarntity_sold

               FROM sales_view) PIVOT (SUM(quantity_sold) FOR quarter IN ('01' AS Q1, '01' AS Q2, '01' AS Q3, '01' AS Q4));

SQL> SELECT *

       FROM pivotedtable

      ORDER BY product DESC;

PRODUCT                  '01' '02' '03' '04'

------------------------ ---- ---- ---- ----

Y Box                    1455 1766 1716 1992

Xtend Memory             3146 4121 4122 3802

Unix/Windows 1-user pack 4259 3887 4601 4049

Standard Mouse           3376 1699 2654 2427

Smash up Boxing          1608 2127 1999 2110

생성된 pivotedtable은 제품별 판매 수량을 분기별로 표시하고 있다. 위의 결과를 아래와 같이 표시 할 수 있다.

SQL> SELECT *

       FROM pivotedtable UNPIVOT (quantity_sold FOR quarter IN (Q1, Q2, Q3, Q4))

      ORDER BY product DESC, quarter;

PRODUCT                  QU QUANTITY_SOLD

------------------------ -- -------------

Y Box                    Q1 1455

Y Box                    Q2 1766

Y Box                    Q3 1716

Y Box                    Q4 1992

Xtend Memory             Q1 3146

Xtend Memory             Q2 4121

Xtend Memory             Q3 4122

Xtend Memory             Q4 3802

UNPIVOT은 분기별 서로 다른 행에 제품의 수량을 표현 할 수 있다. 여러 컬럼을 기준으로 Pivoting 된 테이블에서도 Unpivot이 가능하다.

SQL> CREATE TABLE multi_col_pivot AS

     SELECT *

       FROM (SELECT product, channel, quantity_sold

               FROM sales_view) PIVOT (SUM(quantity_sold) FOR (channel, quarter) IN ((3, '01') AS direct_sales_Q1, (4, '01') AS insternet_sales_Q1))

      ORDER BY product DESC;

SQL> SELECT *

       FROM multi_col_pivot;

PRODUCT                  DIRECT_SALES_Q1 INTERNET_SALES_Q1

------------------------ --------------- -----------------

Y Box                     771             253

Xtend Memory             1935             350

Unix/Windows 1-user pack 2544             397

Standard Mouse           2326             256

Smash up Boxing          1114             129

SIMM-8MB PCMCIAII card   3439             335

SQL> SELECT *

       FROM multi_col_pivot UNPIVOT (quantity_sold FOR (channel, quarter) IN (direct_sales_q1 AS ('Direct', 'Q1'), internet_sales_q1 AS ('Internet', 'Q1'))

      ORDER BY product DESC, quarter;

PRODUCT                  CHANNEL  QU QUANTITY_SOLD

------------------------ -------- -- -------------

Y Box                    Internet Q1  253

Y Box                    Direct   Q1  771

Xtend Memory             Internet Q1  350

Xtend Memory             Direct   Q1 1935

Unix/Windows 1-user pack Internet Q1  397

Unix/Windows 1-user pack Direct   Q1 2544

SQL> CREATE TABLE multi_col_pivot AS

     SELECT *

       FROM (SELECT product, channel, quarter, quantity_sold, amount_sold

               FROM sales_view) PIVOT (SUM(quantity_sold) AS sumq, SUM(amount_sold) AS suma FOR channel IN (3 AS Direct, 4 AS internet))

      ORDER BY product DESC;

SQL> SELECT *

       FROM multi_agg_pivot;

PRODUCT                  QUATER DIRECT_SUMQ DIRECT_SUMA INTERNET_SUMQ INTERNET_SUMA

------------------------ ------ ----------- ----------- ------------- -------------

Y Box                    01     771         236748.39   253            73138.67

Y Box                    02     931         285325.84   295            84556.03

Y Box                    03     889         268265.8    322            91587.57

Y Box                    04     961         290710.93   469           133485.18

SQL> SELECT *

       FROM multi_agg_pivot UNPIVOT ((total_amount_sold, total_quantity_sold) FOR channel IN ((direct_sumq, direct_suma) AS 3, (internet_sumq, internet_suma) AS 4))

      ORDER BY product DESC, quarter, channel;

PRODUCT                  QUARTER CHANNEL TOTAL_AMOUNT TOTAL_QUANTITY

------------------------ ------- ------- ------------ --------------

Y Box                    01      3       771          236748.39

Y Box                    01      4       253           73138.67

Y Box                    02      3       931          285325.84

Y Box                    02      4       295           84556.03

Y Box                    03      3       889           268265.8

출처: Oracle DB와 BigData