19.[오라클]계층형쿼리

계층형 쿼리 : 2차원형태의 테이블에 저장된 데이터를 계층 구조로 반환하는 쿼리

계층형 구문

SELECT exp1,exp2,exp3.....

 FROM [테이블]

WHERE 조건 = 조건

START WITH [최상위에 대한 조건]

CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

START WITH	계층형에서 최상위 계층의 로우를 식별하는 조건을 명시. START WITH는 시작한다는 의미로 조건에 맞는 로우부터 시작
CONNECT BY	계층형이 어떤식으로 연결되는지를 기술하는 부분으로 상위에 대한 정보를 CONNECT BY PRIOR [부서] = [상위]로 기술한다. PRIOR는 계층형 쿼리에서만 사용할수 있는 연산자로 '앞서의,직진의'란 뜻. 'CONNECT BY [부서]=PRIOR [상위]' 형식으로 PRIOR 위치 변경이 가능하다.

예시)

SELECT 
    department_id
  , LPAD(' ' , 3 * (LEVEL-1)) || department_name
  , LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id  = parent_id;

 

계층형 쿼리의 내부적 처리절차

• 조인이 있으면 먼저 조인을 처리
• START WITH절을 참조하여 최상위 로우를 선택.
• CONNECT BY절의 구문에 따라 계층형에 대한 부모와 자식관계를 파악하여 자식 로우를 차례로 선택. 최상위 로우를 기준으로 자식로우를 선택하고 자식로우에 대한 또 다른 자식로우가 있을시 선택하는 형식으로 계속 조건에 맞는 로우를 찾는다.
• 자식 로우 찾기가 끝날 경우 조인을 제외한 WHERE조건에 해당 하는 로우를 걸러내고 맞지않는건 버린다.

※계층형 쿼리에서 사용되는 쿼리들

사용법	설명	예시
ORDER SIBLINGS BY [칼럼]	- 정렬됨과 동시에 계층형 구조까지 보존 - 레벨이 같은 형제로우에 한해 정렬	SELECT      department_id   , LPAD(' ' , 3 * (LEVEL-1)) || department_name   , LEVEL FROM departments START WITH parent_id IS NULL CONNECT BY PRIOR department_id  = parent_id ORDER SIBLINGS BY department_name;
CONNECT_BY_ROOT	계층형 쿼리에서 최상위 로우를반환하는 연산자	SELECT      department_id   , LPAD(' ' , 3 * (LEVEL-1)) || department_name   , LEVEL   , CONNECT_BY_ROOT department_name FROM departments START WITH parent_id IS NULL CONNECT BY PRIOR department_id  = parent_id;
CONNECT_BY_ISLEAF	CONNECT BY 조건에 정의된 관계에 따라 해당 로우가 최하위 자식로우이면1을 아닐 경우 0을 반환  - 최상위 로드 : 루트노드  - 최하위로드 : 리프로드 최상위인지 최하위인지 확인하는 용도로 쓰인다.	SELECT    department_id  , LPAD(' ' , 3 * (LEVEL-1)) || department_name  , LEVEL  , CONNECT_BY_ISLEAF FROM departments START WITH parent_id IS NULL CONNECT BY PRIOR department_id = parent_id;
SYS_CONNECT_BY_PATH	루트노드에서 시작하여 자신의 행까지 연결된 정보를 반환	SELECT      department_id   , LPAD(' ' , 3 * (LEVEL-1)) || department_name   , LEVEL   , SYS_CONNECT_BY_PATH( department_name, '>') FROM departments START WITH parent_id IS NULL CONNECT BY PRIOR department_id  = parent_id;
CONNECT_BY_ISCYCLE	현재로우가 자식을 갖고 있는데 동시에 선택한 자식로우가 부모 로우일 경우 1을 그렇지 않은 경우 0을 반환한다.	SELECT      department_id   , LPAD(' ' , 3 * (LEVEL-1)) || department_name AS depname   , LEVEL   , CONNECT_BY_ISCYCLE IsLoop   , parent_id FROM departments START WITH department_id = 30 CONNECT BY NOCYCLE PRIOR  department_id  = parent_id;

 

※계층형 응용 부분 정리(샘플로 테스트)

SELECT      ROWNUM seq    , '2014' || LPAD(CEIL(ROWNUM/1000) , 2, '0' ) month   , ROUND(DBMS_RANDOM.VALUE (100, 1000)) amt FROM DUAL CONNECT BY LEVEL <= 80000;	CONNECT BY LEVEL <= 80000 : LEVEL을 위와 같이 사용할 경우 등비수열 합의 공식으로 공비 r=1인경우           -> S = a*n 공비 r=1이 아닐 경우  -> S = a(1-rⁿ) / (1-r) 형식으로 생성 및 반환을 하게 된다.
SELECT DEPARTMENT_ID,          LISTAGG(EMP_NAME, ',') WITHIN         GROUP (ORDER BY EMP_NAME) AS EMPNAMES   FROM EMPLOYEES  WHERE DEPARTMENT_ID IS NOT NULL   GROUP BY DEPARTMENT_ID;	로우를 컬럼으로 변환할때 쓰인다.(LISTAGG는 11g버전부터 쓰인다.) 콤마 단위로 한줄로 나눠서 쓰인다.
SELECT REPLACE(SUBSTR(empnames, start_pos, end_pos - start_pos), ',', '') FROM (     SELECT        empnames      ,DECODE(level, 1, 1, INSTR(empnames, ',', 1, level-1)) start_pos --시작위치      ,INSTR(empnames, ',', 1, LEVEL) end_pos--끝위치      ,LEVEL as lvl    FROM (    SELECT empnames || ',' as empnames,              LENGTH(empnames) ori_len,--원문자열 길이              LENGTH(REPLACE(empnames, ',', '')) new_len --콤마를 제외한 길이         FROM ex_2    )   CONNECT BY LEVEL <= ori_len - new_len + 1 ) ;	컬럼으로 되어있는 부분을 로우로 변환을 한다. 간단히 설명하자면 한 컬럼에 콤마단위로 만들어 놓고 콤마에 대한 길이를 구하여 첫위치와 끝위치에 대해 계산하여 하나하나 빼서 로우 형식으로 만든다.

※밑에는 ex에 대한 샘플

CREATE TABLE ex_2 AS
  SELECT department_id,
         listagg(emp_name, ',') WITHIN GROUP (ORDER BY emp_name) as empnames
  FROM employees
 WHERE department_id IS NOT NULL
  GROUP BY department_id;