[SQL] 계층형 쿼리

계층형 쿼리

SELECT expr1, expr2, ...
FROM 테이블
WHERE 조건
START WITH  [최상위 조건]
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]

• START WITH 조건
  계층형 구조에서 최상위 계층의 로우를 식별하는 조건을 명시
• CONNECT BY 조건
  계층형 구조가 어떤 식으로 연결되는지를 기술하는 부분

  CONNECT BY parent_ID = PRIOR department_ID라고 적을 수도 있음.

• LEVEL
  계층형 쿼리에서만 사용할 수 있는 의사 컬럼으로 계층형 구조에 따른 레벨 값을 자동으로 반환

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

1. 조인이 있으면 조인을 먼저 처리한다.
2. START WITH절을 참조해 최상위 계층 로우를 선택한다.
3. CONNECT BY절에 명시된 구문에 따라 계층형 관계(부모-자식관계)를 파악해 자식 로우를 차례로 선택한다.
   최상위 로우를 기준으로 자식 로우를 선택하고, 이 자식 로우에 대한 또 다른 자식 로우가 있으면 선택하는 식으로 계속 조건에 맞는 로우를 찾는다.
4. 자식 로우 찾기가 끝나면 조인을 제외한 WHERE 조건에 해당하는 로우를 걸러내는데, 로우별로 조건에 맞지 않는 건을 걸러낸다.

• ORDER SIBLINGS BY
  계층형 구조를 보존하면서 레벨이 같은 형제 로우에 한해서 정렬을 수행하는 구문

• CONNECT_BY_ROOT
  최상위 로우를 반환하는 연산자

• CONNECT_BY_ISLEAF
  해당 로우가 최하위 자식 로우면 1을, 아니면 0을 반환하는 의사 컬럼

• ROW = NODE / ROOT NODE: 최상위 노드 / LEAF NODE: 최하위 노드

• SYS_CONNECT_BY_PATH(colm,char)
  루트 노드에서 시작해서 자신의 행까지 연결된 경로 정보를 반환
  (colm,char) -> 첫번째 파라미터: 컬럼, 두번째 파라미터: 컬럼 간 구분자 (※ 컬럼 값에 포함된 문자는 사용할 수 없다.)

• CONNECT_BY_ISCYCLE
  오라클의 계층형 쿼리는 루프(반복)알고리즘을 사용.
  현재 로우가 자식을 갖고있는데 동시에 그 자식 로우가 부로 로우면 1을 아니면, 0을 반환

SELECT JOB_NAME
        , LPAD(' ', (LEVEL-1)*5) || JOB_NAME 계층구조
        , LPAD(' ', (LEVEL-1)*2.5) || DEPT_TITLE 계층구조2
        , SYS_CONNECT_BY_PATH(EMP_NAME, '> ')
FROM EMP, JOB, DEPT
WHERE EMP.JOB_CODE = JOB.JOB_CODE
AND DEPT_CODE = DEPT_ID
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID
;

with절

WITH 별칭1 AS (SELECT문), 
	별칭2 AS (SELECT문)
...
SELECT
FROM 별칭1, 별칭2 ...

별칭으로 사용한 SELECT문의 FROM절에 다른 SELECT 구문의 별칭 참조가 가능하다.

분석함수

• RANK(), DENSE_RANK()
  RANK 함수는 파티션별 순위를 반환한다.