태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'connect by'에 해당되는 글 2건

  1. 2010.12.01 Recursive Subquery Factoring의 한가지 활용 예 (5)
  2. 2008.02.28 가로를 세로로 바꾸는 SQL 작성 방법 (9)

Recursive Subquery Factoring의 한가지 활용 예

오라클/SQL 튜닝 2010.12.01 17:46
Oracle 11gR2부터 Recursive Subquery Factoring이라는 기능을 제공합니다. 이 기능을 이용하면 Connect By 구문을 대신할 수 있죠. 아래에 간단한 사용법이 있습니다.
SQL>  select lpad(' ', 2 * level - 2, ' ') || ename as ename
  2        , empno
  3        , mgr
  4        , level
  5     from emp
  6  connect by mgr = prior empno
  7    start with mgr is null
  8  /
SQL> with emps (ename,empno,mgr,lvl) as
  2  ( select ename
  3         , empno
  4         , mgr
  5         , 1
  6      from emp
  7     where mgr is null
  8     union all
  9    select emp.ename
 10         , emp.empno
 11         , emp.mgr
 12         , emps.lvl + 1
 13      from emp
 14           join emps on (emp.mgr = emps.empno)
 15  ) search depth first by empno set a
 16  select lpad(' ', 2 * lvl - 2, ' ') || ename as ename
 17       , empno
 18       , mgr
 19       , lvl
 20    from emps
 21    order by a
 22  /
굳이 Connect By로 잘 사용하고 있었던 것을 왜 다시 Recursive Subquery Factoring을 사용해야 하는지를 고민해보면 딱히 떠오르는 것이 없는데요. 다음과 같은 유형의 쿼리에서 유용하게 사용할 수 있을 것 같습니다.

1. 다음과 같은 두 개의 테이블 T_MATERIAL과 T_COMPOSE가 있습니다. 테이블 T_MATERIAL은 "약"(material_type=Med) 또는 "약의 성분"(material_type=Mat) 데이터를 가지고 있습니다. 테이블 T_COMPOSE는 하나의 약이 어떤 하위 성분과 하위 약으로 이루어져 있는지의 관계를 나타냅니다. 하위 약은 다시 하위 약 또는 하위 성분을 가지므로 계층 구조가 됩니다. 그리고 각 하위 약 또는 하위 성분이 몇 %를 구성하고 있는지의 정보(contain_pct)를 가지고 있습니다.

SQL> create table t_material(
  2  	material_id		number,
  3  	material_name	varchar2(10),
  4  	material_type	varchar2(3) -- Med = medicine, Mat = material
  5  );

Table created.

SQL> 
SQL> create table t_compose (
  2  	medicine_id			number,
  3  	material_id			number,
  4  	contain_pct			number
  5  );

Table created.
이제 다음과 같이 데이터를 생성합니다.
 
SQL> begin
  2  	insert into t_material values(1, 'medicine1', 'Med');
  3  	insert into t_material values(2, 'medicine2', 'Med');
  4  	insert into t_material values(3, 'material1', 'Mat');
  5  	insert into t_material values(4, 'medicine3', 'Med');
  6  	insert into t_material values(5, 'material2', 'Mat');
  7  	insert into t_material values(6, 'medicine4', 'Med');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  	insert into t_compose values(1, 2, 0.3); -- Med
  3  		insert into t_compose values(2, 6, 0.5);
  4  			insert into t_compose values(6, 3, 0.8);
  5  			insert into t_compose values(6, 5, 0.2);
  6  		insert into t_compose values(2, 5, 0.5);
  7  	insert into t_compose values(1, 3, 0.3); -- Mat
  8  	insert into t_compose values(1, 4, 0.2); -- Med
  9  		insert into t_compose values(4, 3, 0.7);
 10  		insert into t_compose values(4, 5, 0.3);
 11  	insert into t_compose values(1, 5, 0.2); -- Mat
 12  end;
 13  /

PL/SQL procedure successfully completed.
1번 약은 (2번 약 30% + 3번 성분 30% + 4번 약 20% + 5번 성분 20%) 으로 이루어져있죠. 2번 약은 (6번 약 50% + 5번 약 50%)로 이루어져 있고, 6번 약은 (3번 성분 80% + 5번 성분 20%)로 이루어져 있습니다. 이런 식으로 계층 구조를 이루고 있습니다.

계층 구조를 지니면서 성분의 함량(contain_pct) 정보가 존재합니다. 여기서 이런 쿼리가 필요해집니다. 1번 약을 구성하는 각 성분의 함량은 어떻게 되는가? 즉, 1번 약을 구성하는 성분인 3번 성분(material1)과 5번 성분(material2)는 각각 몇 %인가?

위와 같은 쿼리가 까다로운 것은 계층 구조를 따라 모든 노드의 값(여기서는 contain_pct)를 알아야하기 때문입니다. 간단하게 계산해보면 3번 성분(material1)의 함량을 구하려면 계층 구조를 따라가면서 0.3*0.5*0.8 + 0.3 + 0.2*0.7 = 0.56 = 56%와 같은 계산이 필요합니다.

Connect By 구문에서는 현재 값과 이전 값(부모 값)만을 알 수 있습니다. 이 한계를 극복하기 위해 나온 것이 SYS_CONNECT_BY_PATH같은 함수죠. 아래와 같이 각 노드의 모든 함량 정보를 얻을 수 있습니다.

SQL> col pholder format a10
SQL> col pct_path format a20
SQL> select
  2  	lpad('-',level,'-') as pholder,
  3  	medicine_id,
  4  	material_id,
  5  	contain_pct,
  6  	sys_connect_by_path(contain_pct,'/') as pct_path
  7  from
  8  	t_compose
  9  connect by medicine_id = prior material_id
 10  start with medicine_id = 1
 11  ;

PHOLDER    MEDICINE_ID MATERIAL_ID CONTAIN_PCT PCT_PATH
---------- ----------- ----------- ----------- --------------------
-                    1           2          .3 /.3
--                   2           5          .5 /.3/.5
--                   2           6          .5 /.3/.5
---                  6           3          .8 /.3/.5/.8
---                  6           5          .2 /.3/.5/.2
-                    1           3          .3 /.3
-                    1           4          .2 /.2
--                   4           3          .7 /.2/.7
--                   4           5          .3 /.2/.3
-                    1           5          .2 /.2

10 rows selected.
위의 값을 실제로 계산하려면 다음과 같이 별도의 함수를 이용한 로직이 필요하게 됩니다.
SQL> create or replace function get_total_pct(pct_path in varchar2)
  2  return number
  3  is
  4  	v_idx1		number := 0;
  5  	v_idx2		number;
  6  	v_temp		number;
  7  	v_total		number := 1;
  8  begin
  9  	v_idx1 := instr(pct_path, '/');
 10  
 11  	loop
 12  
 13  		v_idx2 := instr(pct_path, '/', v_idx1+1);
 14  		if v_idx2 = 0 then
 15  			v_idx2 := length(pct_path)+1;
 16  		end if;
 17  
 18  		v_temp := to_number(substr(pct_path, v_idx1+1, v_idx2-v_idx1-1));
 19  		v_total := v_total * v_temp;
 20  
 21  		v_idx1 := v_idx2;
 22  
 23  		exit when v_idx1 > length(pct_path);
 24  
 25  	end loop;
 26  
 27  	return v_total;
 28  end;
 29  /

Function created.
CONNECT BY 구문과 SYS_CONNECT_BY_PATH 함수, 그리고 위에서 정의한 함수 GET_TOTAL_PCT를 이용하면 다음과 같이 원하는 값을 얻을 수 있습니다.
SQL> with c as (
  2  	select
  3  		material_id,
  4  		get_total_pct(sys_connect_by_path(contain_pct,'/')) as comp_pct
  5  	from
  6  		t_compose
  7  	connect by medicine_id = prior material_id
  8  	start with medicine_id = 1
  9  )
 10  select
 11  	m.material_name,
 12  	sum(c.comp_pct) as total_pct
 13  from
 14  	c,
 15  	t_material m
 16  where
 17  	c.material_id = m.material_id
 18  	and m.material_type = 'Mat'
 19  group by
 20  	m.material_name
 21  ;

MATERIAL_N  TOTAL_PCT
---------- ----------
material1         .56
material2         .44
(음... 더 멋진 방법이 있을 듯... )

Recursive Subquery Factoring에서는 위의 작업을 보다 직관적으로 처리할 수 있습니다. 다음과 같이 부모의 값을 받아서 함량(contain_pct)을 계속 곱해가면 최종 자식 노드의 함량을 알 수 있죠. 그 값을 SUM 하면 함량의 합이 됩니다. 즉, Recursive Subquery Factoring의 장점은 SYS_CONNECT_BY_PATH 같은 함수의 도움을 빌리지 않아도 모든 모드의 값을 이용할 수 있다는 것입니다.

SQL> with recur_mat(comp_pct, material_id)
  2  as (
  3  	select
  4  		contain_pct,
  5  		material_id
  6  	from
  7  		t_compose
  8  	where
  9  		medicine_id = 1
 10  	union all
 11  	select
 12  		p.comp_pct * c.contain_pct,  -- 부모 * 현재 
 13  		c.material_id
 14  	from
 15  		recur_mat p,
 16  		t_compose c
 17  	where
 18  		c.medicine_id = p.material_id
 19  )
 20  select
 21  	m.material_name,
 22  	sum(r.comp_pct) as total_pct
 23  from
 24  	recur_mat r,
 25  	t_material m
 26  where
 27  	r.material_id = m.material_id
 28  	and m.material_type = 'Mat'
 29  group by
 30  	m.material_name
 31  ;

MATERIAL_N  TOTAL_PCT
---------- ----------
material1         .56
material2         .44
아... 설명이 좀 구질구질했는데요. Recursive Subquery Factoring을 이용함으로써 좀 더 작업이 간편해지는 몇 안되는 예제 중 하나라서 올려봅니다.

앞으로 11gR2가 본격적으로 사용되면 활용 예제가 더 많이 소개될 것으로 기대합니다.

저작자 표시
신고
Trackbacks 7 : Comments 5
  1. 욱짜 2010.12.01 21:41 신고 Modify/Delete Reply

    첫번째 버전 쿼리에 비효율이 존재하는군요. 귀엽게 봐주세요.

  2. salvationism 2010.12.02 10:48 신고 Modify/Delete Reply

    오라클 성능 Q&A 책을 받았습니다. 얇을 줄 알았는데 생각보다 책이 두껍네요^^
    대리인 이라는 추천사?가 인상적이 였습니다.
    직접 테스트 하신분과 받아들이는 깊이가 다르겠고, 읽는 독자의 수준마다 받아 들이는 깊이가 다르겠지만
    덕분에 편안하고 쉽게 그리고 재밌게 잘 보겠습니다.
    감사합니다. ^^

    • 욱짜 2010.12.02 10:57 신고 Modify/Delete

      감사합니다. 추천사를 쓰신 분이 워낙 내공이 깊습니다. ^^;

      저도 추천사를 받고 반성하게 되더라구요. 더 잘 적을걸!!!

      책이 두꺼운 이유는 가능한 현장감있게 설명하기 위해 테스트 결과를 많이 넣었기 때문입니다. 보시기에 따라서는 불필요한 데이터를 많이 넣은 것은 아닌가하고 생각하실수도 있는데, 읽을 때 걸림이 없도록 하기 위해 시도한 것이라고 생각해주시면 좋겠습니다.

  3. Ejql 2011.01.28 14:30 Modify/Delete Reply

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

  4. more info here 2012.03.31 09:18 Modify/Delete Reply

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

Write a comment


가로를 세로로 바꾸는 SQL 작성 방법

오라클 2008.02.28 10:00
오동규 님의 블로그에 세로로 출력되는 데이터를 가로로 출력하는 몇 가지 재미있는 기법들 소개되고 있다.

즉 아래와 같은 데이터를

SELECT DEPTNO, ENAME
FROM EMP;

DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD


아래와 같이 출력하기를 원한다.

DEPTNO ENAME
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES


이런 요구사항은 매우 빈번하고 또 일반적이다.

이 블로그에서는 Oracle, MySQL, SQL Server 등에서 이런 출력을 가능하게 하는 다양한 방법을 소개하고 있다. Oracle에서 사용 가능한 방법은 다음과 같이 소개되어 있다.

select deptno,
ltrim(sys_connect_by_path(ename,','),',') ename
from (
select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over (partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1 ;


analytic function과 connect by를 적절히 이용하는 강력한 기법이다. (MySQL는 간단한 함수 하나로 이것을 할 수 있다는게 부러울 따름이다!)

이에 덧붙여 Oracle에서 비슷한 효과를 낼 수 있는 간단한 기법 하나를 소개하고자 한다.
간단한 기법이란 아래와 같이 Cursor 함수를 사용하는 것이다.

SCOTT@ukja10> select
  2  e.deptno, cursor(select ename from emp where deptno = e.deptno)
  3  from emp e
  4  group by e.deptno
  5  ;


출력 결과는 다음과 같다.

    DEPTNO CURSOR(SELECTENAMEFR
---------- --------------------
        30 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
------------------------------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

        20 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
------------------------------
SMITH
JONES
SCOTT
ADAMS
FORD

        10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
------------------------------
CLARK
KING
MILLER


엄밀하게 말하면 가로로 출력하는게 아니라 Cursor를 컬럼처럼 리턴받아서 이 Cursor에 대해 Fetch를 하는 방식이다. JDBC 등에서 이를 출력받아서 Fetch 하면서 디스플레이하면 마치 가로로 출력한 것과 동일한 효과를 얻을 수 있다. 이 방법의 장점 하나는 매우 복잡한 요구 사항도 쉽게 처리가 가능하다는 것이다.

Oracle의 PL/SQL은 매우 강력해서 우리가 생각한 것보다 훨씬 많은 일을 할 수 있다. 잘 활용하면 원하는 작업을 보다 간단하게 처리할 수 있는 기회가 있을 것이다.



신고
Trackback 0 : Comments 9
  1. nokarma 2008.02.28 13:03 신고 Modify/Delete Reply

    개발자 입장에서 볼 때, 이걸 어디다가 써먹으면 좋을지 얼른 와닫지 않는군요.
    데이타 포맷팅은 어플리케이션 프로그램에서 하지 왜 데이타베이스 CPU돌려가면서 해야할까란 생각이 먼저 듭니다.

    SQL/PLUS 주로 쓰는 DBA들에게는 유용한 테크닉인가 보군요.

  2. 욱짜 2008.02.28 13:53 신고 Modify/Delete Reply

    문제를 해결하는데 생각보다 다양한 기법들이 있다... 이렇게 이해하면 가장 정확할거 같습니다. 어떤 경우는 (가령) Java에서, 어떤 경우에는 Database에서 하는 것이 더 유리한 경우가 있을 것이고, 이런 다양한 기법들을 이해하면 적절한 때에 적절한 기법을 쓸 수 있을 걸로 생각됩니다.

  3. oracler 2008.03.21 22:50 신고 Modify/Delete Reply

    데이타 포매팅은 application 에서 할수도 있지만
    반드시 SQL 에서 직접 해야 하는 경우도 있을수 있고
    이런 저런 다양한 기법을 알아놓는것은 분명 도움이 됩니다.

    잊을만 하면 툭 튀어나와 그때마다 필요로 하는것이 가로 세로 바꾸는것이고
    이런 TIP 류의 가려운점때문에 전세계 오라클 관련 개발자들이 시간낭비하고 있는데
    아직까지 공식적으로 쉬운 솔루션을 제공하지 않는것이 참 이상합니다.
    볼때마다 새롭고 볼때마다 헷갈려요..ㅎㅎ

  4. 욱짜 2008.04.01 22:15 신고 Modify/Delete Reply

    좋은 정보 감사합니다. 언제 사용법을 블로그에 올려주시면 여러 사람이 도움을 받을 수 있겠습니다. ^^

  5. oracler 2008.04.02 23:03 신고 Modify/Delete Reply

    pivot / unpivot 기능은 아래 :
    http://blog.naver.com/kittynbu?Redirect=Log&logNo=120048945452

  6. 욱짜 2008.04.16 09:46 신고 Modify/Delete Reply

    http://www.oracle-developer.net/display.php?id=506
    11g의 pivot/unpivot에 대해 가장 상세하고 정확한 설명이 올라왔네요. 확인해보시기 바랍니다.

  7. 오동규 2008.10.03 23:51 신고 Modify/Delete Reply

    Oracle 11g 부터 pivot 과 unpivot 기능이 추가 되었습니다. 참고 하세요^^

  8. 머여 2008.11.19 13:52 신고 Modify/Delete Reply

    머여 이게 가로를 세로로 만든거여 세로를 가로로 만든거구만

  9. 욱짜 2008.11.20 10:14 신고 Modify/Delete Reply

    ㅎㅎ 그러네요.

Write a comment

티스토리 툴바