'Pivot' 태그의 글 목록

'Pivot'에 해당되는 글 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을 이해하고 사용해야 할 시기가 도래한 것인가.

'오라클' 카테고리의 다른 글

IN (...) 절에 Item을 무제한으로 넣고 싶다. (2)	2008.12.18
불필요한 Histogram만 삭제하기 (1)	2008.12.17
Pivot 절의 비밀 - 세로를 가로로? (3)	2008.12.15
Grant와 Cursor Invalidation (0)	2008.12.11
Oracle에 대한 이상한 오해들 (0)	2008.12.09

tags : Pivot

Trackbacks 2 : Comments 3

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

우수인력 2015.05.08 15:52 Modify/Delete Reply
관리자의 승인을 기다리고 있는 댓글입니다

우수인력 2015.05.08 15:52 Modify/Delete Reply
관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

: name

: password

: homepage secret mode

◀ PREV : [1] : NEXT ▶

태터데스크 관리자

태터데스크 메시지

욱짜의 블로그

'Pivot'에 해당되는 글 1건

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

'오라클' 카테고리의 다른 글

Write a comment

티스토리 툴바