본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
3. 계층형 질의와 셀프 조인
1. 계층형 질의
테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다. 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다.
가. Oracle 계층형 질의
SELECT COL
FROM TB1
WHERE condition AND condition
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition
[ORDER BY SIBLINGS BY COL1, COL2]
- START WITH 절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한 다.(액세스)
- CONNECT BY 절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다.(조인)
- PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
- PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다.
- PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개를 한다.
- NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE 를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
- ORDER BY SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)
("PRIOR 자식 = 부모" 형태일 때, 부모 >> 자식 방향으로 전개되는 게 맞습니다. 다르게 나와 있다면, 해당 교재에 오타가 있는 겁니다!)
Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 컬럼을 제공한다.
- LEVEL: 루트 데이터이면 1, 그 하위 데이터이면 2이다. 리프(Leaf) 데이터까지 1씩 증가한다.
- CONNECT_BY_ISLEAF: 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0이다.
- CONNECT_BY_ISCYCLE: 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다.
아래는 EMP 테이블에 계층형 질의 구문을 이용하여 조회한 것이다.
SELECT LEVEL, LPAD(' ', 4 * (LEVEL - 1)) || EMPNO EMPNO,
MGR, CONNECT_BY_ISLEAF ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
아래는 EMPNO 7876으로부터 상위 MGR을 찾는 역방향 전개의 예이다.
SELECT LEVEL, LPAD(' ', 4 * (LEVEL - 1)) || EMPNO EMPNO, MGR, CONNECT_BY_ISLEAF ISLEAF
FROM EMP
START WITH EMPNO = 7876
CONNECT BY PRIOR MGR = EMPNO;
Oracle은 계층형 질의를 사용할 때 사용자 편의성을 위해 아래 함수를 제공한다.
- SYS_CONNECT_BY_PATH: 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
- CONNECT_BY_ROOT: 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.
SELECT CONNECT_BY_ROOT EMPNO ROOT_EMPNO, SYS_CONNECT_BY_PATH(EMPNO, '/') 경로, EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
나. SQL Server 계층형 질의
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
WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합했다. 둘 중 위에 있는 쿼리를 ‘앵커 멤버’(Anchor Member)라고 하고, 아래에 있는 쿼리를 ‘재귀 멤버’(Recursive Member)라고 한다.
재귀적 쿼리의 처리 과정
- CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.
- 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.
- Ti 는 입력으로 사용하고 Ti+1 은 출력으로 사용하여 재귀 멤버를 실행한다.
- 빈 집합이 반환될 때까지 3 단계를 반복한다.
- 결과 집합을 반환한다. 이것은 T0 에서 Tn 까지의 UNION ALL 이다.
CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력된다. 따라서 조직도와 같은 모습으로 출력하려면 order by 절을 추가해 원하는 순서대로 결과를 정렬해야 한다. 실제 조직도와 같은 모습의 결과를 출력하도록, CTE 에 Sort 라는 정렬용 칼럼을 추가하고 쿼리 마지막에 order by 조건을 추가해보자.
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
가상의 Sort 칼럼을 추가해 정렬하는 게 아쉽기는 하지만, SQL Server 에서 계층 구조를 실제 모습대로 출력하려면 현재(2005, 2008 버전 기준)로서는 감수해야 할 수밖에 없다.
2. 셀프 조인
셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다. 그리고 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해줘야 한다. 이외 사항은 조인과 동일하다.
SELECT E1.EMPNO 사원번호, E1.ENAME 사원명, MGR.ENAME 관리자명
FROM EMP E1
LEFT JOIN EMP MGR ON E1.MGR = MGR.EMPNO
ORDER BY 사원번호;
위의 SQL 문은 아우터 조인을 사용해서 관리자가 존재하지 않는 데이터까지 모두 결과에 표시되었다.
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.5. 그룹 함수 (0) | 2020.11.26 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.4. 서브쿼리 (3) | 2020.11.26 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.2. 집합 연산자 (0) | 2020.11.25 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.1. 표준 조인 (0) | 2020.11.24 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.9. 조인 (JOIN) (0) | 2020.11.24 |
