태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

세로 데이터를 가로로 출력하기 - 또 다른 아이디어

오라클 2008.10.04 21:29
세로 데이터를 가로로 출력하는 요구 사항이 종종 나온다.

즉, 이런 결과를

TYPE1                          AREA1                                 AMT
------------------------------ ------------------------------ ----------
type3                          area2                                1231
type10                         area2                                  81
type8                          area2                                 292
type2                          area5                                1015
type7                          area10                                625
type4                          area1                                1569
type8                          area1                                2379
type5                          area2                                 119
type10                         area3                                 615
type6                          area4                                 674
...

type1                          area6                                 971
type5                          area6                                 250
type8                          area8                                1574
type10                         area9                                 932
type8                          area9                                 202

이렇게 출력해달라는 것이다.

TYPE1                               area1      area2      area3      area4      area5      area6      area7      area8      area9     area10
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
type1                                            575        300                   766        971                   151         95        602
type2                                 291        799                             1015                   542       1492        620          
type3                                 813       1231                  1282        717                                         753       1654
type4                                1569                             1323        513        606        503                   500       2343
type5                                            119                  1169        649        250       1167       1709        899          
type6                                2101       1876         29        674        747        915         14                                
type7                                1691        395        211        124       2368        381                  2191                   625
type8                                2379        292                              597                             1574        202          
type9                                                                                        965                                         777
type10                               1525         81        615       1330                   533        154                   932          

여러 서적(가령 대용량데이터베이스 솔루션 2)이나 자료에서 이런 기법들에 대해 소개하고 있다. 그 방법들이 처음 접하는 사람들에게 대단히 복잡해 보이고 나중에 Customizing 하기가 쉽지 않은 경우도 있다.

여기서 한가지 문제는 type 값뿐만 아니라 area1 ~ area10은 고정된 값이 아니며 시간에 따라 늘어나거나 줄고 완전히 새로운 area가 등장하기도 한다는 것이다.

따라서 Static  SQL로 구현하기가 쉽지는 않다.

내가 알기로 이런 요구사항을 만족하는 가장 멋진 방법은 Oracle 10g에 소개된 Model 기능을 사용하는 것이다.
하지만 나 스스로가 이에 익숙치 못하고, 나에게 문의한 사람은 아마 고개를 도리도리 흔들었을 것이다.

아마 많은 분들이 스스로의 경험이나 고민에 의해 나름의 해법을 가지고 있을 것이다.

내가 문의한 사람에게 보낸 방법은 다음과 같다.

우선 필요한 Object를 생성한다.

set serveroutput on

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 int, amt int);
create table t2(c1 int, type1 varchar2(10));
create table t3(c1 int, area1 varchar2(10));

insert into t1
select
  mod(abs(dbms_random.random),10)+1,
  mod(abs(dbms_random.random),10)+1,
  mod(abs(dbms_random.random),1000)+1
from dual
connect by level <= 100
;

insert into t2
select level, 'type'||level
from dual
connect by level <= 10
;

insert into t3
select level, 'area'||level
from dual
connect by level <= 10
;


우리는 필연적으로 이런 Query를 사용할 수 밖에 없다.

select
  t2.type1, t3.area1, sum(t1.amt) as amt
from
  t1, t2, t3
where
  t1.c1 = t2.c1 and
  t1.c2 = t3.c1
group by
  t2.type1, t3.area1
;

그 결과는 이렇다.

TYPE1                          AREA1                                 AMT
------------------------------ ------------------------------ ----------
type3                          area2                                1231
type10                         area2                                  81
type8                          area2                                 292
type2                          area5                                1015
type7                          area10                                625


요구 사항을 만족시키기 위해서...
다음과 같은 Procedure를 통해 Dynamic SQL을 만들고 이에 해당하는 Ref cursor를 리턴한다.

create or replace procedure getcursor(p_cursor out sys_refcursor)
is
  v_sql  varchar2(10000);
  v_r   int := 1;
begin
  v_sql := 'select type1 ';
 
  for x in (
      select c1, area1 from t3) loop
   
    v_sql := v_sql || chr(10) || ', (select sum(amt) from t1 where c2 = ' ||
                x.c1 || ' and c1 = t2.c1) as "' || x.area1 || '"';
    v_r := v_r + 1;
  end loop;
 
  v_sql := chr(10) || v_sql || ' from t2';
 
  dbms_output.put_line(v_sql);
 
  open p_cursor for v_sql;
 
end;
/

이 Procedure에 의해 생성되는 SQL 문은 다음과 같다. Dynamic Column을 Dynamic SQL에 의해 Dynamic Scalar Subquery로 구현한 셈이다.

select type1                                                                
, (select sum(amt) from t1 where c2 = 1 and c1 = t2.c1) as "area1"          
, (select sum(amt) from t1 where c2 = 2 and c1 = t2.c1) as "area2"          
, (select sum(amt) from t1 where c2 = 3 and c1 =                            
t2.c1) as "area3"                                                           
, (select sum(amt) from t1 where c2 = 4 and c1 = t2.c1) as "area4"          
, (select sum(amt) from t1 where c2 = 5 and c1 = t2.c1) as "area5"          
, (select sum(amt) from t1 where c2 = 6 and c1 =                            
t2.c1) as "area6"                                                           
, (select sum(amt) from t1 where c2 = 7 and c1 = t2.c1) as "area7"          
, (select sum(amt) from t1 where c2 = 8 and c1 = t2.c1) as "area8"          
, (select sum(amt) from t1 where c2 = 9 and c1 =                            
t2.c1) as "area9"                                                           
, (select sum(amt) from t1 where c2 = 10 and c1 = t2.c1) as "area10"
from t2

다음과 같이 사용한다.

var c refcursor;

exec getcursor(:c);
/

print c

다음과 같이 결과가 출력된다. 정확하게 우리가 원하는 결과이다.

TYPE1                               area1      area2      area3      area4      area5      area6      area7      area8      area9     area10
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
type1                                            575        300                   766        971                   151         95        602
type2                                 291        799                             1015                   542       1492        620          
type3                                 813       1231                  1282        717                                         753       1654
type4                                1569                             1323        513        606        503                   500       2343
type5                                            119                  1169        649        250       1167       1709        899          
type6                                2101       1876         29        674        747        915         14                                
type7                                1691        395        211        124       2368        381                  2191                   625
type8                                2379        292                              597                             1574        202          
type9                                                                                        965                                         777
type10                               1525         81        615       1330                   533        154                   932          


출력해야할 area 수가 가변적이기 때문에 Dynamic SQL이 필요한데 이를 PL/SQL을 이용해 처리하도록 하는 것이다.
의의로 쉽고, 강력하면서, 누구나 손쉽게 Customizing 할 수 있다는 장점이 있다.
반면에 처리해야할 Data의 양이 많다면 성능에는 불리한 요소들이 있다는 것도 보인다.

다행히 이 문의 사항에서는 데이터의 양이 많지 않아서 Scalar Subquery를 통해 Table t1을 반복 액세스하는 부담이 문제가 되지 않았다.

Comment를 통해서 "나는 이런 방법이 더 낫다고 생각한다"나 "이렇게 했더니 더 좋더라" 이런 경험과 분석 내용이 공유되었으면 한다.


신고
Trackback 0 : Comments 4
  1. 욱짜 2008.10.11 13:07 신고 Modify/Delete Reply

    복잡한 집계성의 리포트 화면을 구현하다보면 아마 대부분의 경우 1) 매우 많은 수의 SQL을 수행하고 2) 코드에서 많은 분기문과 루프문을 통해 데이터를 처리하는 방식을 사용하게 됩니다. 이런 방법은 비효율적일 확률이 높고 에러 가능성이 높죠. Query를 튜닝하기도 번거롭습니다.

    그래서 가끔 나오는 요구 사항이 가능한 적은 수의 Tunable한 Query로 구현할 수 있느냐가 됩니다.

    이런 문제를 없애기 위해 많은 기법들을 사용가능할겁니다. 이제는 어느 정도 옛날 방식이 되어버린 대용량 시리즈에서 소개한 기법들이나 Analytic Function, Rollup/Cube, Model 등의 기능을 이용할 수 있을겁니다. 때로는 이런 기능을 사용해도 처리가 안되는 경우가 있기 하지만요. 위의 내용도 이런 예 정도로 보면 될 거 같습니다.

  2. 문수희 2011.02.10 14:24 Modify/Delete Reply

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

  3. vhaptfwdtv 2012.11.12 01:31 Modify/Delete Reply

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

  4. gyylcowntq 2012.11.12 21:16 Modify/Delete Reply

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

Write a comment

티스토리 툴바