Thë 굽은ㄴr무™

 

※ 주요모바일 기기 User-agent

※ IE8, IE9 User-Agent 변경하기
※ Opera User-Agent 변경하기
※ FireFox User-Agent 변경하기
※ Safari User-Agent 변경하기
※ Chrome User-Agent 변경하기

참고 : http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries003.htm


1. 샘플 데이터 생성

CREATE TABLE EMPLOYEES
(
  EMPLOYEE_ID     NUMBER(6),
  FIRST_NAME      VARCHAR2(20 BYTE),
  LAST_NAME       VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
  EMAIL           VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
  PHONE_NUMBER    VARCHAR2(20 BYTE),
  HIRE_DATE       DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
  JOB_ID          VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
  SALARY          NUMBER(8,2),
  COMMISSION_PCT  NUMBER(2,2),
  MANAGER_ID      NUMBER(6),
  DEPARTMENT_ID   NUMBER(4)
);
CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES(DEPARTMENT_ID);
CREATE UNIQUE INDEX EMP_EMAIL_UK ON EMPLOYEES(EMAIL);
CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES(EMPLOYEE_ID);
CREATE INDEX EMP_JOB_IX ON EMPLOYEES(JOB_ID);
CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES(MANAGER_ID);
CREATE INDEX EMP_NAME_IX ON EMPLOYEES(LAST_NAME, FIRST_NAME);
ALTER TABLE EMPLOYEES ADD (
  CONSTRAINT EMP_SALARY_MIN
  CHECK (salary > 0),
  CONSTRAINT EMP_EMP_ID_PK
  PRIMARY KEY
  (EMPLOYEE_ID)
  USING INDEX EMP_EMP_ID_PK,
  CONSTRAINT EMP_EMAIL_UK
  UNIQUE (EMAIL)
  USING INDEX EMP_EMAIL_UK);
ALTER TABLE EMPLOYEES ADD (
  CONSTRAINT EMP_MANAGER_FK
  FOREIGN KEY (MANAGER_ID)
  REFERENCES EMPLOYEES (EMPLOYEE_ID));
INSERT INTO EMPLOYEES
SELECT *
  FROM HR.EMPLOYEES;
SELECT * FROM EMPLOYEES;

2. 테스트 쿼리

-- SYS_CONNECT_BY_PATH
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , LEVEL
         , LTRIM(SYS_CONNECT_BY_PATH(LAST_NAME, '/'), '/') "PATH"
         , CONNECT_BY_ROOT LAST_NAME ROOT_LAST_NAME
         , CONNECT_BY_ISLEAF
      FROM EMPLOYEES
  START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY LAST_NAME

-- START WITH가 없을 경우 LEVEL의 값에 따라 ROW 중복 생성 (LEVEL = 2일 경우 LEVEL 값만 1,2로 다르고 값은 똑같은 ROW가 2개 생김)
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , LEVEL
      FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY EMPLOYEE_ID

-- DEPARTMENT_ID = 110 의 사원별로 자신의 상관 검색
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , SYS_CONNECT_BY_PATH (LAST_NAME, '/') "PATH"
         , CONNECT_BY_ROOT (LAST_NAME) ROOT_LAST_NAME
         , LEVEL LVL
      FROM EMPLOYEES
     WHERE DEPARTMENT_ID = 110
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
  ORDER BY EMPLOYEE_ID, MANAGER_ID, LVL DESC, "PATH"

-- 사원부터 자신의 상관 검색
    SELECT EMPLOYEE_ID
         , LAST_NAME
         , MANAGER_ID
         , SYS_CONNECT_BY_PATH (LAST_NAME, '/') "PATH"
         , SYS_CONNECT_BY_PATH (LAST_NAME, CHR(27)) "PATH2"
         , CONNECT_BY_ROOT (LAST_NAME) ROOT_LAST_NAME
         , LEVEL LVL
      FROM EMPLOYEES
START WITH EMPLOYEE_ID = 103
CONNECT BY EMPLOYEE_ID = PRIOR MANAGER_ID
  ORDER BY LVL DESC

-- LEVEL 별로 순번
  SELECT A.*
       , LPAD (' ', 5 * (LVL - 1), ' ') || NVL2 (MANAGER_ID, ROW_NUMBER () OVER (PARTITION BY MANAGER_ID ORDER BY LAST_NAME), NULL) TREE
       , COUNT(*) OVER(PARTITION BY MANAGER_ID) CNT
    FROM (           SELECT EMPLOYEE_ID
                          , LAST_NAME
                          , MANAGER_ID
                          , LEVEL LVL
                          , LTRIM (SYS_CONNECT_BY_PATH (LAST_NAME, '/'), '/') "PATH"
                          , SYS_CONNECT_BY_PATH (LAST_NAME, '/') PATH2
                       FROM EMPLOYEES
                 START WITH EMPLOYEE_ID = 100
                 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
          ORDER SIBLINGS BY LAST_NAME) A
ORDER BY ROWNUM


-- LEVEL 한단계 부모의 값 조회
  SELECT A.*
       , DECODE (LVL
               , 1, NULL
               , SUBSTR (PATH2, INSTR (PATH2, '/', 1, LVL - 1)+ 1
                                         , INSTR (PATH2, '/', 1, LVL) - INSTR (PATH2, '/', 1, LVL - 1) - 1)) LVL_PARENT
       , LPAD (' ', 5 * (LVL - 1), ' ') || NVL2 (MANAGER_ID, ROW_NUMBER () OVER (PARTITION BY MANAGER_ID ORDER BY ROWNUM), NULL) TREE
       , NVL2 (MANAGER_ID, ROW_NUMBER () OVER (PARTITION BY MANAGER_ID ORDER BY ROWNUM), NULL) TREE2
       , COUNT (*) OVER (PARTITION BY MANAGER_ID) CNT
    FROM (           SELECT EMPLOYEE_ID
                          , LAST_NAME
                          , MANAGER_ID
                          , LEVEL LVL
                          , LTRIM (SYS_CONNECT_BY_PATH (LAST_NAME, '/'), '/') "PATH"
                          , SYS_CONNECT_BY_PATH (LAST_NAME, '/') PATH2
                       FROM EMPLOYEES
                 START WITH EMPLOYEE_ID = 100
                 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
          ORDER SIBLINGS BY LAST_NAME) A
ORDER BY ROWNUM

3. Javascript에서 CHR(27) 구분하기

"메뉴1\u001b서브메뉴1".split(String.fromCharCode(27));

=======================================================================================


Recursive Subquery 사용으로 Connect by 처리

출처 : https://ukja.tistory.com/m/359


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가 본격적으로 사용되면 활용 예제가 더 많이 소개될 것으로 기대합니다.