습관처럼
SQL 활용 - 제 3절 계층형 질의와 셀프 조인 본문
1. 계층형 질의
테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query) 사용.
계층형 데이터란?
동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터
예를들면, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재. 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생.
순환관계 데이터 모델의 예) 조직, 사원, 메뉴 등
(2) 계층형 구조 : A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E 가 있음
(3) 샘플 데이터 : (2) 계층형 구조를 데이터로 표현한 것
※ 계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원.
가. Oracle 계층형 질의
Oracle은 계층형 질의를 지원하기 위해서 ［그림 Ⅱ-2-7］과 같은 계층형 질의 구문을 제공
- START WITH 절 (액세스)
계층 구조 전개의 시작 위치를 지정하는 구문. 즉, 루트 데이터 지정
- CONNECT BY 절 (조인)
다음에 전개될 자식 데이터를 지정하는 구문
자식 데이터는 CONNECT BY절에 주어진 조건 만족
- PRIOR
CONNECT BY절에 사용되며, 현재 읽은 칼럼 지정
PRIOR 자식 = 부모 형태 시, 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개
PRIOR 부모 = 자식 형태 시, 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 역방향 전개
- NOCYCLE
데이터 전개 시, 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 사이클(Cycle) 형성이라함 사이클이 발생한 데이터는 런타임 오류 발생
☞ NOCYCLE 추가 : 사이클이 발생한 이후의 데이터는 전개하지 않음
- ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬 수행
- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)
※ 확인해 보자!!
SELECT LEVEL,LPAD(' ', 4 * (LEVEL-1))||EMPNO||':'||ENAME 사원,MGR 관리자,CONNECT_BY_ISLEAF IsLF
FROM SCOTT.EMP
WHERE ENAME <>'FORD'
START WITH EMPNO = '7369'
CONNECT BY PRIOR MGR = EMPNOOracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column) 제공
예제 1 -［그림 Ⅱ-2-6］의 (3)샘플 데이터를 계층형 질의 구문을 이용해서 조회
SQL:
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM 사원
START WITH 관리자 IS NULL
CONNECT BY PRIOR 사원 = 관리자 //순방향
>>실행결과
LEVEL 사 원 관리자 ISLEAF
----- ------------- ------ ------
1 A 0
2 B A 1
2 C A 0
3 D C 1
3 E C 1 결과 데이터를 들여쓰기 하기 위해서 LPAD 함수 사용
관리자 → 사원 방향을 전개이기 때문에 순방향 전개
예제 2 - 사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개의 예
SQL:
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM 사원
START WITH 사원 = 'D'
CONNECT BY PRIOR 관리자 = 사원
실행결과
LEVEL 사 원 관리자 ISLEAF
----- ------------- ------ ------
1 D C 0
2 C A 0
3 A 1
역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개, 리프 데이터는 A이다. 루트 및 레벨은 전개되는 방향에 따라 반대가 됨
Orcle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 ［표 Ⅱ-2-3］과 같은 함수 제공
예제 3 - SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT를 사용한 예
SQL:
SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자
FROM 사원
START WITH 관리자 IS NULL
CONNECT BY PRIOR 사원 = 관리자
실행결과
루트사원 경 로 사 원 관리자
-------- ----------- ------ ------
A /A A
A /A/B B
A /A/C C A
A /A/C/D D C
A /A/C/E E C
루트사원은 모두 A ∵ START WITH를 통해 추출된 루트 데이터가 1건 이기 때문, 경로는 루트로부터 현재 데이터까지의 경로 표시
예를 들어, D의 경로는 A → C → D
※ 참고
CONNECT_BY_PATH CONNECT_BY_ROOT CONNECT_BY_ISLEAF
☞ Oracle 10g 부터 사용 가능
(회사가 8i, 9i버전이라 예제 테스트 시 수행되지 않았음... -_-;;; 8i 는 예상했지만..9i까지 안될줄이야...)
나. SQL Server 계층형 질의
SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았음
조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개
SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 됨
예제. Northwind 데이터베이스에 접속하여 Employees 테이블의 데이터 조회
USE NORTHWIND GO
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES GO
**********************************************************************************
Emp~ID LastName FirstName ReportsTo
------ ------------ --------- ---------
1 Davolio Nancy 2
2 Fulle Andrew NULL
3 Leverling Janet 2
4 Peacock Margaret2
5 Buchanan Steven 2
6 Suyama Michael 5
7 King Robert 5
8 Callahan Laura 2
9 Dodsworth Anne 5
(9개 행 적용됨)ReportsTo 칼럼 : 상위 사원에 해당하며 EmployeeID 칼럼과 재귀적 관계를 맺고 있음
EmployeeID가 2인 Fuller 사원을 살펴보면, ReportsTo 칼럼 값이 NULL이므로 계층 구조의 최상위에 있음을 알 수 있음
CTE(Common Table Expression)를 재귀 호출함으로써
Employees 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 결과는 다음과 같음
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
**********************************************************************************
Level Emp~ID LastName FirstName ReportsTo
----- ------ ------------ --------- ---------
0 2 Fuller Andrew NULL
1 1 Davolio Nancy 2
1 3 Leverling Janet 2
1 4 Peacock Margaret 2
1 5 BuchananSteven 2
1 8 Callahan Laura 2
2 6 Suyama Michael 5
2 7 King Robert 5
2 9 Dodsworth Anne 5
(9개 행 적용됨)
WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합
둘 중 위에 있는 쿼리를 ‘앵커 멤버’(Anchor Member)라고 하고,
아래에 있는 쿼리를 ‘재귀 멤버’(Recursive Member)라고 함
▼ 재귀적 쿼리의 처리 과정
1. CTE 식을 앵커 멤버와 재귀 멤버로 분할
2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0) 생성
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버 실행
4. 빈 집합이 반환될 때까지 3단계 반복
5. 결과 집합을 반환한다. 이것은 T0에서 Tn까지의 UNION ALL
※ 정리
1. 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인 시작
2. 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면
(즉, 더 조인할 수 없으면)
지금까지 만들어진 결과 집합을 모두 합하여 리턴
▼［그림 Ⅱ-2-10］에 있는 조직도를 쿼리로 출력했을 때, 대부분 사용자는 아래와 같은 결과를 예상
EmployeeID ManagerID (보기 편하도록 각 로우 앞쪽에 자신의 레벨만큼 빈칸 삽입)
----------------- ---------
1000 NULL
1100 1000
1110 1100
1120 1100
1121 1120
1122 1120
1200 1000
1210 1200
1211 1210
1212 1210
1220 1200
1221 1220
1222 1220
1300 1000
▼ 아래에 t_emp 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 그 결과이다.
WITH T_EMP_ANCHOR AS
( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL
FROM T_EMP
WHERE MANAGERID IS NULL /* 재귀 호출의 시작점 */
UNION ALL
SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1
FROM T_EMP_ANCHOR A, T_EMP R
WHERE A.EMPLOYEEID = R.MANAGERID )
SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID FROM T_EMP_ANCHOR GO
***********************************************************************************************
Level EmployeeID ManagerID
----- ----------- -----------
0 1000 NULL
1 1100 1000
1 1200 1000
11300 1000
2 1210 1200
2 1220 1200
3 1221 1220
3 1222 1220
3 1211 1210
3 12121210
2 1110 1100
2 1120 1100
3 1121 1120
3 1122 1120
(14개 행 적용됨)보다시피, 계층 구조를 단순히 하위 방향으로 전개했을 뿐 ［그림 Ⅱ-2-10］에 있는 조직도와는 많이 다른 모습
앞서 보았듯이, CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력
☞ 따라서 조직도와 같은 모습으로 출력하려면 order by 절을 추가해 원하는 순서대로 결과 정렬
▼ 실제 조직도와 같은 모습의 결과를 출력하도록, CTE에 Sort라는 정렬용 칼럼을 추가하고 쿼리 마지막에 order by 조건 추가
(단, 앵커 멤버와 재귀 멤버 양쪽에서 convert 함수 등으로 데이터 형식을 일치시켜야 한다.)
WITH T_EMP_ANCHOR AS
( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL, CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT
FROM T_EMP
WHERE MANAGERID IS NULL /* 재귀 호출의 시작점 */
UNION ALL
SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1, CONVERT(VARCHAR(1000), A.SORT + '/' + R.EMPLOYEEID) AS SORT
FROM T_EMP_ANCHOR A, T_EMP R
WHERE A.EMPLOYEEID = R.MANAGERID )
SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID, SORT
FROM T_EMP_ANCHOR ORDER BY SORT GO
CTE 안에서 Sort 칼럼에 사번(=EmployeeID)을 재귀적으로 더해 나가면 정렬 기준으로 삼을 수 있는 값 생성
▼ 아래는 Sort 칼럼으로 정렬하여 출력한 결과로, ［그림 Ⅱ-2-10］에 있는 조직도의 모습과 일치
Level Emp~ID ManagerID Sort
----- -------- ---------- ----------------------
0 1000 NULL 1000
1 1100 1000 1000/1100
2 1110 1100 1000/1100/1110
2 1120 1100 1000/1100/1120
3 1121 1120 1000/1100/1120/1121
3 1122 1120 1000/1100/1120/1122
1 1200 1000 1000/1200
2 1210 1200 1000/1200/1210
3 1211 1210 1000/1200/1210/1211
3 1212 1210 1000/1200/1210/1212
2 1220 1200 1000/1200/1220
3 1221 1220 1000/1200/1220/1221
3 1222 1220 1000/1200/1220/1222
1 1300 1000 1000/1300
(14개 행 적용됨)※ 정리
가상의 Sort 칼럼을 추가해 정렬하는 게 아쉽기는 하지만,
SQL Server에서 계층 구조를 실제 모습대로 출력하려면 현재(2005, 2008 버전 기준)로서는 감수해야 함
2. 셀프 조인
- 셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다.
∴ FROM 절에 동일 테이블이 두 번 이상 나타난다.
- 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias) 사용
- 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별
- 이외 사항은 조인과 동일
■ 셀프 조인에 대한 기본적인 사용법
SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...
FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2 => FROM 테이블1 ALIAS명1, 테이블1 ALIAS명2
WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1
SELECT WORKER.ID 사원번호, WORKER.NAME 사원명, MANAGER.NAME 관리자명
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.ID계층형 질의에서 살펴보았던 사원이라는 테이블 속에는 사원과 관리자가 모두 하나의 사원이라는 개념으로 동일시하여 같이 입력
문제 : “ 자신과 상위, 차상위 관리자를 같은 줄에 표시하라. ”
☞ 이 문제를 해결하기 위해서는 FROM 절에 사원 테이블을 두 번 사용 (셀프조인 사용)
▼ 문제 해결
셀프 조인은 동일한 테이블(사원)이지만, [그림 Ⅱ-2-11］과 같이 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일
동일 테이블을 다른 테이블인 것처럼 처리하기 위해 테이블 별칭 사용
☞ 여기서는 E1(사원), E2(관리자) 테이블 별칭사용
차상위 관리자를 구하기 위해서 E1.관리자 = E2.사원 조인 조건 사용
▼ 셀프 조인을 이용한 SQL문
SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자
FROM 사원 E1, 사원 E2
WHERE E1.관리자 = E2.사원
ORDER BY E1.사원
사원 관리자 차상위_관리자
---- ------ -------------
B A
C A
D C A
E C A자신과 자신의 직속 관리자는 동일한 행에서 데이터를 구할 수 있으나 차상위 관리자는 바로 구할 수 없음
차상위 관리자를 구하기 위해서는 자신의 직속 관리자를 기준으로 사원 테이블과 한번 더 조인(셀프 조인) 수행 필요
결과 표시를 위해 SELECT절에 2개의 ‘관리자’ 칼럼 사용
한 명은 자신의 직속 관리자(E1.관리자)이고 다른 한 명은 자신의 차상위 관리자(E2.관리자)
결과를 보면, B와 C의 관리자는 A이고 차상위 관리자는 없음
D와 E의 관리자는 C이고 차상위 관리자는 A
결과에서 A에 대한 정보는 누락
내부 조인(Inner Join)을 사용할 경우 자신의 관리자가 존재하지 않는 경우,
관리자(E2) 테이블에서 조인할 대상이 존재하지 않기 때문에 해당 데이터는 결과에서 누락
☞이를 방지하기 위해서는 아우터 조인 사용
▼ 아우터 조인을 추가한 SQL문
SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자
FROM 사원 E1 LEFT OUTER JOIN 사원 E2 ON (E1.관리자 = E2.사원)
ORDER BY E1.사원
사원 관리자 차상위_관리자
---- ------ -------------
A
B A
C A
D C A
E C A
☞ 아우터 조인을 사용해서 관리자가 존재하지 않는 데이터까지 모두 결과 표시
출처 : http://www.bysql.net
'Certification > Sqld' 카테고리의 다른 글
| SQL 활용 - 제 5절 그룹 함수 (0) | 2020.02.11 |
|---|---|
| SQL 활용 - 제 4절 서브쿼리 (0) | 2020.02.11 |
| SQL 활용 - 제 2절 집합 연산자 (0) | 2020.02.11 |
| SQL 활용 - 제 1절 표준 조인 (0) | 2020.02.11 |
| SQL 기본 및 활용 - 제 9절 JOIN (0) | 2020.02.11 |
