참고 : 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가 본격적으로 사용되면 활용 예제가 더 많이 소개될 것으로 기대합니다.
오라클 11G 기준 재귀쿼리입니다. 이하 버전에서는 안돌아 갑니다.
WITH t1 AS
(
SELECT '20120321' dt, 2 dy, '01' ct FROM dual
UNION ALL SELECT '20120322', 5, '02' FROM dual
UNION ALL SELECT '20120323', 4, '03' FROM dual
)
, t2 AS
(
SELECT '01' ct, '20120322' dt FROM dual
UNION ALL SELECT '01', '20120323' FROM dual
UNION ALL SELECT '02', '20120323' FROM dual
UNION ALL SELECT '03', '20120326' FROM dual
)
, t3(dt, dy, ct) AS
(
SELECT *
FROM t1
UNION ALL
SELECT TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'yyyymmdd') dt
, a.dy
- CASE WHEN b.dt IS NOT NULL THEN 0
WHEN TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'd') IN ('1','7') THEN 0
ELSE 1 END AS dy
, a.ct
FROM t3 a
LEFT JOIN t2 b
ON a.ct = b.ct
AND b.dt = TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'yyyymmdd')
WHERE a.dy > 0
)
SELECT ct, dt
FROM t3
WHERE dy = 0
ORDER BY ct, dt
;