pivot 기능(열을 행으로 처리) SQL

pivot 기능(열을 행으로 처리) SQL


SQL Server
   
    SELECT * FROM SALES PIVOT (SUM(QYANTITY) FOR CUST IN ([SMITH],[MIKE],[ALLEN],[SCOTT])) PVT
   

DB2

    SELECT S.YEAR,Q.QUARTER,Q.RESULTS
    FROM SALESAGG AS S
        ,TABLE(VALUES (1,S.Q1),
                                    (2,S.Q2),
                                    (3,S.Q3),
                                    (4,S.Q4))
            AS Q(QUARTER,RESULTS)                                
   
   
Oracle
   
    SELECT * FROM (SELECT QUANTITY,PRODUCT,CUST FROM SALES S)
    PIVOT (SUM(QUANTITY) FOR CUST IN ('SMITH','MIKE','ALLEN','SCOTT')
                           
-----------------------------------------------------------------------------------


Pivot 절의 비밀 - 세로를 가로로?
Advanced Oracle 2008/12/15 15:26


몇 달전에서 세로 데이터를 가로로 출력하는 기법에 대해서 잠깐 논의한 바 있다(아래...)
(한 분이 정확하게도 제목이 거꾸로 되었다는 것을 지적해주셨다)

2008/10/04 - [Advanced Oracle] - 세로 데이터를 가로로 출력하기 - 또 다른 아이디어

Oracle 11g의 신기능인 Pivot 절을 테스트하다가 Pivot의 구현 방식이 내가 위에서 설명한 것과 거의 동일한 것을 알았다. 아래 예제를 보자.


drop table t1 purge;

create table t1
as
select
  10 - mod(level, 7) as c1,
  mod(level, 5)+1 as c2,
  (1+abs(mod(dbms_random.random,100000))) as c3
from dual
connect by level <= 1000
;
   
alter session set statistics_level = all;


Pivot 절을 이용해서 Spread-Sheet 형태의 Report를 얻는다.


select
  *
from t1
pivot (
    sum(c3)
    for c2
    in (1,2,3,4,5)
)
;


        C1          1          2          3          4          5
---------- ---------- ---------- ---------- ---------- ----------
         6    1316159    1573010    1498494    1288933    1303779
         5    1693139    1320481    1293505    1196280    1145482
         4    1348538    1560112    1289342    1582098    1229042
         8    1431648    1538771    1293812    1295780    1670113
         7    1602281    1269428    1291983    1562826    1466569
         9    1385877    1437785    1589266    1448629    1320990
        10    1500509    1287280    1344653    1305496    1053597


Oracle은 어떻게 이런 결과를 얻을 수 있을까?
결론부터 말하면 간단한 Query Transformation 과정을 거친다.

아래와 같이 ALL 옵션을 이용해서 Plan을 조회하면 Column Projection 정보를 얻을 수 있다.


select * from table(dbms_xplan.display_cursor(null, null, 'allstats all last'));

--------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows |E-Bytes|
--------------------------------------------------------------
|   1 |  HASH GROUP BY PIVOT|      |      1 |   1000 | 39000 |
|   2 |   TABLE ACCESS FULL | T1   |      1 |   1000 | 39000 |
--------------------------------------------------------------
                                                             
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
                                                             
   1 - SEL$58A6D7F6                                          
   2 - SEL$58A6D7F6 / T1@SEL$1                                
                                                             
Column Projection Information (identified by operation id):  
-----------------------------------------------------------  
                                                             
   1 - "T1"."C1"[NUMBER,22],                                  
        SUM(CASE  WHEN ("C2"=1) THEN "C3" END )[22],          
        SUM(CASE  WHEN ("C2"=2) THEN "C3" END )[22],          
        SUM(CASE  WHEN ("C2"=3) THEN "C3" END )[22],          
        SUM(CASE  WHEN ("C2"=4) THEN "C3" END )[22],          
        SUM(CASE  WHEN ("C2"=5) THEN "C3" END )[22]          
   2 - "T1"."C1"[NUMBER,22], "C2"[NUMBER,22], "C3"[NUMBER,22]

                                                             
Column Project 정보를 보면 아뿔싸하는 생각이 들 것이다.
Oracle은 Pivot Query을 아래와 같이 변환(Transformation)하는 것이다.
(따라서 Pivot 절을 사용한다고 해서 성능이 더 개선되는 것은 아니라는 것을 짐작할 수 있다)


select
  c1,
  sum(case when (c2 = 1) then c3 end) as "1",
  sum(case when (c2 = 2) then c3 end) as "2",
  sum(case when (c2 = 3) then c3 end) as "3",
  sum(case when (c2 = 4) then c3 end) as "4",
  sum(case when (c2 = 5) then c3 end) as "5"
from t1
group by c1
;


위의 Query 형태는 내가 블로그에서 소개한 바 있는(하지만 이미 보편적으로 사용되고 있는) 것이다.

Pivot 절이 제공하는 기능을 이용하면 이제 더 이상 지저분한 Dynamic Query는 불필요한가?
기본적으로 그렇다. 단, 가로로 펼칠 Column을 동적으로 구성하려면 다음과 같이 XML 결과를 이용해야 한다는 점이 불편하다.


set long 10000  -- XML 결과 Display를 위해

select *
from t1
pivot xml (
  sum(c3)
  for c2
  in (any)
)
;


select *
from t1
pivot xml (
  sum(c3)
  for c2
  in (select distinct c2 from t1)
)
;