[SQL] 계층형 질의

계층형 질의란?

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용하는 것

계층형 데이터를 데이터베이스 마다 질의하는 방법이 다름

 - ORACLE

 - SQLSERVER

 

계층형 데이터

 - 동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어 있는 데이터

사원의 관리자는 하위 데이터이기 때문에 관리자는 A라는 것을 알 수 있다.

이를 상위 데이터와 하위데이터를 사원과 관리자를 서로 순환 관계 데이터라고 보며 이를 계층형 구조라고 한다.

 

- START WITH 는 어떤 데이터가 최상위 데이터가 될 것인지 기준을 잡아주는 것임 (부모컬럼 - 관리자가 NULL 최상위 데이터를 잡아주는 것이다.)

- CONNECT BY 연결을 시켜줌, 자식과 부모의 관계를 연결시켜주는 방식임

SELECT LEVEL, 자식 컬럼, 부모 컬럼, 원하는 컬럼
FROM 테이블명
START WITH 부모 컬럼 IS NULL (부모 컬럼이 NULL인 행이 Root(가장 상위)가 됨)
CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼; (상위 데이터와 하위 데이터의 연결 방식)

 

계층형 질의 예시(Oracle)

 - 사원번호 1000이 Root가 된다.

 - 상위데이터를 통해서 하위 데이터를 관리자에서 갖고 있는 것을 CONNECT BY 절에서 잡아주는 것임

SELECT LEVEL, 사원번호, 관리자 FROM 직원
START WITH 관리자 IS NULL CONNECT BY PRIOR 사원번호(상위데이터) = 관리자(하위데이터);

 

계층형 질의 예시(Oracle) - LPAD 응용

 - LPAD(a, b) 일 경우, a라는 문자열을 b 갯수 만큼 기존 데이터의 왼쪽에 추가시켜 주는 것이다.

 - LPAD(‘ ’, n)은 왼쪽에 n자리의 공백 추가를 의미 Root는 LEVEL값이 1이기 때문에 4*(LEVEL-1) = 0 이 된다

SELECT LEVEL, LPAD(‘ ’, 4*(LEVEL-1))||사원번호, 관리자
FROM 직원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원번호 = 관리자

 

 

 

CONNECT BY 키워드(Oracle)

LEVEL

 - 검색 항목의 깊이를 의미하며, 계층구조에서 루트(최상위)의 레벨이 1

CONNECT_BY_ROOT
 - 현재 전개할 데이터의 루트(최상위) 데이터 값 표시

CONNECT_BY_ISLEAF

 - 현재 전개할 데이터가 리프(최하위) 데이터 인지에 대한 값 표시(0 or 1)

SYS_CONNECT_BY_PATH(A, B)

 - 루트 데이터부터 현재까지 전개한 경로 표시(A: 컬럼명, B: 구분자)

 

 

CONNECT_BY_ROOT로 전개할 경우, CONNECT_BY_ROOT컬럼에는 사원번호가 전개된다.

 

CONNECT_BY_ISLEAF를 전개할 경우, 리프 데이터가 맨 아래 있는 사원번호 1002 일경우에 1이 나타나게 된다.

SYS_CONNECT_BY로 전개할 경우, 그 루트가 A:B 형태로 나타나게 되는데, 사원번호가 1001일 경우 1000:1001 로 나타나고 1002 일경우 1000:1001:1002 로 나타나게 된다.

 

 

 

계층형 질의 예시(SQL SEVER)

 - SQL Server version.2000 이전 → 저장 프로시저를 재귀 호출 / While 루프 문에서 임시테이블 사용

 - SQL Server version.2005 이후 → CTE(Common Table Expression)을 이용하여 재귀 호출

 - 특정 버전 이후 (MariaDB의 경우 10.2 이후) CTE(Common Table Expression)을 이용하여 재귀 호출

WITH EMPLOYEES_ANCHOR AS ( SELECT EMPLOYEEID), LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL
FROM EMPLOYEES
WHERE REPORTSTO IS NULL
UNION ALL
SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1
FROM EMPLOYEES_ANCHOR A, EMPLOYEES R
WHERE A.EMPLOYEEID = R.REPORTSTO )
SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO
FROM EMPLOYEES_ANCHOR GO;

 

WITH RECURSIVE CTE(member_id, manager_id, lvl)
AS (
SELECT member_id, manager_id, 0 AS lvl
FROM MEMBER
WHERE manager_id IS NULL
UNION ALL
SELECT a.member_id, a.manager_id, b.lvl + 1
FROM MEMBER a
JOIN CTE AS b
ON a.manager_id = b.member_id
)
SELECT member_id, manager_id, lvl
FROM CTE
ORDER BY member_id, lvl;

계층형 질의를 쿼리에 대해서 도출할 수 있는 방법은 

WITH RECURSIVE는 재귀호출 구문

CTE 테이블을 사용하여 추출하고자 하는 컬럼명이 오게됨

컬럼 값 안에서도 원하고자 하는 특정 쿼리를 정의하여 CTE 테이블을 만들겠다 보는 것이다.

 

마지막으로 SELECT 문으로 조회를 하게 된다. 안에 있는 재귀호출 구문이 어떻게 동작되는지 알면 좋다.

 

1번째 순환

 - manager_id IS NULL 은 최상위 데이터를 찾는 것이다. (오라클 START WITH)

ELECT member_id, manager_id, 0 AS lvl
FROM MEMBER
WHERE manager_id IS NULL;

 

CTE Table 구성

 - CTE 테이블은 임시테이블 인데, 쿼리가 재귀적으로 지속적으로 호출이 되면서, CTE 테이블이 변화가 없게 되면 쿼리가 멈추면서 결과값을 나타내게 한다. 

- JOIN한 CTE 값이 비어있게 된다면, UNIONALL로 테이블이 만들어진다.

SELECT member_id, manager_id, 0 AS lvl
FROM MEMBER
WHERE manager_id IS NULL
UNION ALL
SELECT a.member_id, a.manager_id, b.lvl + 1
FROM MEMBER a
JOIN CTE AS b
ON a.manager_id = b.member_id;

 

 

2번째 순환

 - CTE 테이블이 MEMBER 테이블에서 1000인 데이터가 JOIN을 통해서 가져오게 되고, CTE 테이블의 레벨 값을 1을 더해주게 된다. lvl값이 1이 늘어나게 되므로, 재귀 호출이 되어 두번째 순환을 통해서 CTE table이 만들어지게 된다.

SELECT member_id, manager_id, 0 AS lvl
FROM MEMBER
WHERE manager_id IS NULL
UNION ALL
SELECT a.member_id, a.manager_id, b.lvl + 1
FROM MEMBER a
JOIN CTE AS b
ON a.manager_id = b.member_id;

 

3번째 순환

 - 위의 과정과 동일하게 JOIN을 진행하지만, MEBER ID 로 만들어지는게 순환을 통해서 똑같이 만들어지지만 MEMBER_ID 가 1000이고, MANAGER_ID가 1001이면 lvl 값이 형성이 되었고, 1005인 데이터룰 통해서 LEVEL 값이 2가 되고, 이 데이터를 통해서 1인 데이터를 통해서 2인 데이터가 생기게 됨, UION

SELECT member_id, manager_id, 0 AS lvl
FROM MEMBER
WHERE manager_id IS NULL
UNION ALL
SELECT a.member_id, a.manager_id, b.lvl + 1
FROM MEMBER a
JOIN CTE AS b
ON a.manager_id = b.member_id;