[ORACLE] 향상된 데이터 조회를 위한 정규표현식과 계층화 쿼리 1

in Study on Oracle
[ORACLE] 향상된 데이터 조회를 위한 정규표현식과 계층화 쿼리 1

향상된 데이터 조회를 위한 정규표현식과 계층화 쿼리 1

계층화 쿼리(Hierarchical query)

  • 계층화 쿼리
    • 계층화 쿼리를 사용하면 테이블에 있는 행 간의 자연적 계층 관계(level)에 준하여 데이터를 검색할 수 있다.
      • 자연적 계층 관계 = 가상 칼럼
    • 관계형 데이터베이스는 레코드를 계층 방식으로 저장하지 않는다.
      • 따라서 대신에 계층적 모델을 만든다.
    • 그러나 한 테이블의 행 사이에 계층 관계가 존재하면 트리 탐색이라는 프로세스를 사용하여 계층을 구성할 수 있다.
    • 계층화 쿼리는 조회를 통한 보고의 한 방법으로, 특전 순서로 된 트리 분기가 있다.
    • 테이블의 행 사이에 관계를 통해 계층 표현이 가능하다.
    • 계층 트리는 계보(가계도), 가축류(품종 개량 목적), 기업 관리(관리 계층-조직도), 제조(제품 블리), 진화론 연구(종의 진화), 과학 연구 등의 여러 분야에 사용할 수 있다.

사원 테이블의 테이블 구조

  • 자신의 사번은 employee_id, 상사의 사번은 manager_id로 표현한다.
  • 데이터 모델의 재귀적 관계인 경우 계층 쿼리 사용 가능
    • 자기 자신과 관계를 맺음으로서 자기 자신의 기본키가 자기 자신에게 Foregin key로 전이되는 관계이다.
  • 사번과 상사의 사번의 값을 통해 자연 계층이 성립하여 계층화 쿼리가 사용 가능하게 된다.
    1. 상사와 부하직원 간의 계층 관계
    2. 상품 분류 체계
    3. 계층형 게시판

    Untitled

    Untitled

    Untitled

    Level 1 은 root ,

    Level 2 는 branch ,

    Level 3 은 branch ,

    Level 4 는 leaf .

    Level 2 와 Level 3 은 밑에 leaf 가 있기 때문에 branch 가 된다.

    즉 Level 칼럼은 employee_id, manager_id 두 개의 칼럼을 통해서 깊이에 의해 값을 읽어들일 수 있다.

계층화 쿼리 문법

SELECT  [LEVEL], column, expr...
  FROM. table
[WHERE. condition(s)]
[START  WITH conditon(s)] -- 시작위치
[CONNECT BY PRIOR condition(s)];
  • 시작 위치 : 충족해야 하는 조건을 지정
...START WITH last_name = 'Kochhar'

트리의 시작 위치라고 생각하면 된다.

WHERE 절 조건처럼 굳이 이름이 아니더라도 값을 나타낼 수 있으면 된다.

  • 예
...START WITH manager_id IS NULL
...START WITH employee_id = (SELECT  employee_id
                               FROM. employees
                              WHERE. last_name = 'Kochhar')

서브쿼리를 넣어도 된다.

  • 트리 탐색
CONNECT BY PRIOR column1 = column2

...CONNECT BY PRIOR employee_ID = manager_id -- 내 사번을 중심으로 상사의 사번을 읽어들임
...CONNECT BY PRIOR manager_id = employee_id
...CONNECT BY employee_id = PRIOR manager_id

탑-다운(하향식)으로 할 것인지, 바텀-업(상향식)으로 탐색을 할 것인지를 결정하는 것이 CONNECT BY 절의 핵심이다.

그 위치 값을 정하는 것이 PRIOR 이다.

PRIOR 가 붙은 칼럼이 부모 키가 된다고 생각하면 된다.

만약에 두 칼럼의 관계가 좀 애매하다고 생각이 된다면, 한 번은 왼쪽에, 한 번은 오른쪽에 두고 관찰해보자.

쉽게 관계를 파악할 수 있을 것이다.

계층화 쿼리 활용

SELECT. last_name || '는 ' || PRIOR last_name || '의 소속직원이다.' 
    AS  emp_report
  FROM. employees
 START. WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
 EMP_REPORT
1King는 의 소속직원이다.
2Kochhar는 King의 소속직원이다.
3Whalen는 Kochhar의 소속직원이다.
4Higgins는 Kochhar의 소속직원이다.
……

SELECT 절에서 PRIOR 가 붙은 last_name 이 상사가 된다.

CONNECT BY 절에서 employee_id 에 PRIOR 가 붙었으므로 해당 칼럼이 부모 키가 된다.

계층화 쿼리를 이용한 계층 구조 표현

  • 최상위 레벨에서 시작하여 다음 레벨을 각각 들여 쓴 형태의 회사 관리 레벨이 표시(조직도, organization chart)된 보고서를 생성하시오.

      SELECT. LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')
      AS. org_chart
    FROM. employees
   START. WITH frist_name = 'Steven'
           AND.last_name = 'King'
  CONNECT BY PRIOR employee_id = manager_id;
    • LPAD(char1, n [,char2]) 는 길이 n의 왼쪽을 char2의 문자 시퀀스로 채운 char1을 반환
    • 인수 n은 터미널 화면에 표시되는 반환 값의 총 길이
    • LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') 는 표시 형식을 정의
    • char1은 LAST_NAME 이고, n은 반환 값의 총 길이로 LAST_NAME+(LEVEL*2)-2 이고, char2는 _ 이다.
     ORG_CHART
    1King
    2__Kochhar
    3____Whalen
    4____Higgins
    5____Gietz
    6__De Haan
    ……

  • 사원들의 이름, 부서명, 업무를 합쳐서 출력하고자 하는 계층형 쿼리 작성

      SELECT. LPAD(last_name, LENGTH(last_name)+(LEVEL*3)-2,'_')
          || ' job is ' || job_id || ', department_name is '
          || d.department_name
      AS. org_chart
    FROM. employees e, departments d
   WHERE. e.department_id = d.department_id
   START. WITH e.last_name = 'King'
  CONNECT.BY PRIOR e.employee_id = e.manager_id;
     ORG_CHART
    1_King job is AD_PRES, department_name is Executive
    2____Kochhar job is AD_VP, department_name is Executive
    3___Whalen job is AD_ASST, department_name is Administration
    4___Higgins job is AC_MGR, department_name is Accounting
    ……

계층화 쿼리 활용 : CONNECT_BY_ROOT() 함수

  • CONNECT_BY_ROOT 함수는 현재 조회된 행들의 최상의 값을 출력하는 Pseudo column.
SELECT. last_name || '는 ' || PRIOR last_name
        || '의 소속직원이다. ' AS emp_report,
        CONNECT_BY_ROOT(employee_id) AS rootid,
        CONNECT_BY_ROOT(last_name) AS rootname
  FROM. employees
 START. WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
 EMP_REPORTROOTIDROOTNAME
1King는 의 소속직원이다.100King
2Kochhar는 King의 소속직원이다.100King
3Whalen는 Kochhar의 소속직원이다.100King
4Higgins는 Kochhar의 소속직원이다.100King
5Gietz는 Higgins의 소속직원이다.100King
…………

계층화 쿼리의 고유 함수들은 계층 데이터가 있어야만 사용 가능하다.

즉 계층적 표현이 가능한 재귀 관계 모델에서만 사용할 수 있다.

계층화 쿼리 활용 : CONNECT_BY_ISLEAF() 함수

  • CONNECT_BY_ISLEAF 함수는 대상 행이 Leaf라면 1을, 아니면 0을 출력하는 함수
SELECT. last_name || '는 ' || PRIOR last_name
        || '의 소속직원이다.' AS emp_report,
        CONNECT_BY_ISLEAF AS leaf
  FROM. employees
 START. WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
 EMP_REPORTLEAF
1King는 의 소속직원이다.0
2Kochhar는 King의 소속직원이다.0
3Whalen는 Kochhar의 소속직원이다.1
4Higgins는 Kochhar의 소속직원이다.0
………

계층화 쿼리 활용 : SYS_CONNECT_BY_PATH() 함수

  • LEVEL 의사 열(Pseudo column)을 사용하지 말고, 각 사원의 전체 직속 계층을 표현하는 쿼리 작성 (예 : King-Kochhar-Greenberg-Faviet)
SELECT. substr(SYS_CONNECT_BY_PATH(last_name,'-'), 2)
    AS. name_path
  FROM. employees
 START. WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
 NAME_PATH
1King
2King-Kochhar
3King-Kochhar-Whalen
4King-Kochhar-Higgins
5King-Kochhar-Higgins-Gietz
……

SYS-CONNECT-BY-PATH(column, char) 함수는 계층적 쿼리에서만 유효하며, 칼럼의 절대 경로를 char로 지정한 문자(-)로 분리하여 반환한다.

  • 계층구조 전개를 부하직원은 사원번호 순으로 정렬하고자 한다
SELECT. employee_id
      , LEVEL lv
      , RPAD(' ', LEVEL*3-2) || last_name AS ename
      , SUBSTR(SYS_CONNECT_BY_PATH(last_name, '-'), 2) AS enames
  FROM. employees
 START. WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
 ORDER  BY employee_id;
 EMPLOYEE_IDLVENAMEENAMES
11001KingKing
21012KochharKing-Kochhar
31022De HaanKing-De Haan
41033HunoldKing-De Haan-Hunold
51044ErnstKing-De Haan-Hunold-Ernst
……………

ORDER BY 를 사용하여 정렬을 할 경우 결과가 이상하게 출력된다.

이 때는 SIBLINGS 를 추가하도록 하자.

ORDER SIBLINGS BY employee_id;
 EMPLOYEE_IDLVENAMEENAMES
11001KingKing
21012KochharKing-Kochhar
32003WhalenKing-Kochhar-Whalen
42053HigginsKing-Kochhar-Higgins
52064GietzKing-Kochhar-Higgins-Gietz
…………..

SIBLINGS 는 계층쿼리에서만 사용된다.

의도에 맞는 정렬을 수행할 수 있다.

MS-SQL로 구현

  • 계층화 쿼리에 사용되는 START WITH, CONNECT BY PRIOR 는 오라클 전용이다
  • MS-SQL로 구현 한다면?
    • 굉장히 길어진다 …

    Untitled

계층화 쿼리 활용 : 트리의 가지(분기) 제거

  • WHERE 절을 사용하여 노드를 제거
WHERE  last_name != 'Higgins'

가지인 Higgins 는 제거되지만, 그 자식 노드(Leaf)는 제거되지 않음에 유의.

  • CONNECT BY 절을 사용하여 분기를 제거
CONNECT BY PRIOR employee_id = manager_id
           AND last_name != 'Higgins'

WHERE 절을 사용했을때와는 다르게 가지인 Higgins 와, 그 자식 노드, 리프까지 모두 제외된다.

© 2022. All rights reserved. 신동민의 블로그