태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'Pivot'에 해당되는 글 1건

  1. 2008.12.15 Pivot 절의 비밀 - 세로를 가로로? (3)

Pivot 절의 비밀 - 세로를 가로로?

오라클 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)
)
;

        C1                                                                                                                                                                                             
----------                                                                                                                                                                                             
C2_XML                                                                                                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         4                                                                                                                                                                                             
<PivotSet><item><column name = "C2">1</column><column name = "SUM(C3)">1348538</column></item><item><column name = "C2">2</column><column name = "SUM(C3)">1560112</column></item><item><column name = "
C2">3</column><column name = "SUM(C3)">1289342</column></item><item><column name = "C2">4</column><column name = "SUM(C3)">1582098</column></item><item><column name = "C2">5</column><column name = "SU
M(C3)">1229042</column></item></PivotSet>                                                                                                                                                              
                                                                                                                                                                                                       
         5                                                                                                                                                                                             
<PivotSet><item><column name = "C2">1</column><column name = "SUM(C3)">1693139</column></item><item><column name = "C2">2</column><column name = "SUM(C3)">1320481</column></item><item><column name = "
C2">3</column><column name = "SUM(C3)">1293505</column></item><item><column name = "C2">4</column><column name = "SUM(C3)">1196280</column></item><item><column name = "C2">5</column><column name = "SU
M(C3)">1145482</column></item></PivotSet>                                                                                                                                                              
                                                                                                                                                                                                       
         6                                                                                                                                                                                             
<PivotSet><item><column name = "C2">1</column><column name = "SUM(C3)">1316159</column></item><item><column name = "C2">2</column><column name = "SUM(C3)">1573010</column></item><item><column name = "
C2">3</column><column name = "SUM(C3)">1498494</column></item><item><column name = "C2">4</column><column name = "SUM(C3)">1288933</column></item><item><column name = "C2">5</column><column name = "SU
M(C3)">1303779</column></item></PivotSet>                                                                                                                                                              
...


하지만 Oracle이 제공하는 XML Query 기능을 이용하면 원하는 대로 데이터를 추출할 수 있으므로 큰 문제는 아니라고 할 수 있다. 이제 누구나 XML을 이해하고 사용해야 할 시기가 도래한 것인가.





신고
tags :
Trackbacks 2 : Comments 3
  1. extremedb 2008.12.16 00:49 신고 Modify/Delete Reply

    참고로 XMLAgg,XMLElement 기능을 잘 이용하면 위와 같은 효과(MY-SQL) 과 같은 효과를 낼수 있습니다.
    물론 컬럼으로 분리는 안됩니다만...콤마(comma) 등으로 분리하여 파일을 만들때 유용하더군요.

  2. 우수인력 2015.05.08 15:52 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  3. 우수인력 2015.05.08 15:52 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바