sqld: sql 활용 - 계층형 질의와 셀프 조인

계층형 질의와 셀프 조인

계층적 데이터가 존재하는 경우 데이터 조회를 위해서 계층형 질의(Hierarchical Query)를 사용

계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

A는 관리자가 없고, B, C의 관리자는 A, D,E의 관리자는 E ...

-- 오라클이 제공하는 계층형 질의 구문
SELECT ...
FROM 테이블명
WHERE condition
START WITH 
CONNECT BY [PRIOR][NOCYCLE] condition
[ORDER SIBLINGS BY column, ...]

START WITH절은 계층 구조 전개의 시작 위치 지정, 즉 루트 데이터를 지정

CONNECT BY 절은 자식 데이터를 지정.

PRIOR : CONNECTD BY 절에 사용되며 현재 읽은 칼럼을 지정? (부모/자식 전개방향 지정)

• PRIOR 자식 = 부모: 부모 → 자식 방향으로 전개 (반대라서 조금 헷갈린다)
• PRIOR 부모 = 자식 : 자식 → 부모 방향으로 전개

NOCYCLE: 데이터 전개중 이미 나타난 동일한 데이터가 다시 나오는 것을 CYCLE이 형성되었다고 하는디 runtime error가 발생한다고함. NOCYCLE 쓰면 이후 데이터는 전개하지않음

ORDER SIBLINGS: 형제 노드 (동일level) 사이에서 정렬 수행

다음과같은 가상칼럼 제공

EMP 테이블 내용. 보면 이해가 더 잘됨.

-- 순방향 전개 예시
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || EMPNO, MGR, 
CONNECT_BY_ISLEAF as ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

• LPAD: Left padding (블랭크나 의미가 없는 기호를 부가하여 고정길이로 하는것?). 계층 표시하기 위해서 한듯
• 매니저가 없는 사람부터 시작해야 함으로 MGR의 NULL 부터 시작 (start with), 다음 전개될 값은 prior empno = mgr
• MGR (부모) → 자식 (EMPNO) 순으로 탐색하기 때문에 PRIOR EMPNO = MGR

테이블의 내용으로 살펴보면...

1. MGR(NULL) → EMPNO(7839) (KING), LEVEL 1임 (root data이므로)
2. 이 KING이 매니저인 애들을 찾으면? (MGR=7839 인 애들은 7566, 7698, 7782)
3. 이런식으로 반복해서 찾아나가는 과정

-- 역방향 전개 예시
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || EMPNO, MGR, CONNECT_BY_ISLEAF AS ISLEAF
FROM EMR
START WITH EMPNO = '7876'
CONNECT BY PRIOR MGR = EMPNO;

• EMPNO가 7876인 11번 ADAMS로부터 시작. 여기가 root 이고 부모 개념과는 다르다
• 이미 역방향이기 때문에 PRIOR 부모 = 자식 형태로 써줌

예시에서 살펴보면

EMP 테이블 내용. 보면 이해가 더 잘됨.

1. EMPNO 7876인 ADAMS로부터 (루트) 시작
2. ADAMS의 매니저는 7788, (8번 SCOTT) → 7566 (4번 JONES)
3. 이런식으로 반복

또 계층형 질의에서 사용되는 함수도 두가지 있다고함..

-- 계층형 질의 사용한 함수 예시
SELECT CONNECT_BY_ROOT(EMPNO) AS 루트사원, SYS_CONNECT_BY_PATH(EMPNO, '/') AS 경로, EMPNO AS 사원, MGR AS 관리자
FROM EMP
START WITH MRG IS NULL
CONNECT BY PRIOR EMPNO = MGR;

• CONNECT_BY_ROOT 가 EMPNO 칼럼을 표시
• SYS_CONNECT_BY_PATH로 /로 분리하여 경로를 만들어서 표시해줌

위의 예시의 결과값이 이렇게 나옴

CONNECT BY의 실행순서는 다음과 같다.

• 첫째 START WITH 절
• 둘째 CONNECT BY 절
• 세째 WHERE 절 순서로 풀리게 되어있다.

SQL SERVER 계층형 질의

SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았다. 조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개해야만 했다. 그러나 SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 되었다. 먼저, Northwind 데이터베이스에 접속하여 Employees 테이블의 데이터를 조회해 보자. (이해못함)

USE NORTHWIND GO SELECT EMPLOYEEID, LASTNAME, FIRSTNAME,REPORTSTO
FROM EMPLOYEES GO

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 )

다음 순서로 재귀적 쿼리가 처리됨

1. CTE 식을 앵커/재귀 멤버로 분할 (UNION ALL로 쿼리 두개를 합쳤는데 위에 있는 쿼리를 앵커, 아래의 쿼리를 재귀 멤버)
2. 앵커 멤버실행하여 첫 집합 생성 (T0)
3. Ti는 입력 → Ti +1을 출력하여 재귀 멤버 실행
4. 빈 집합이 출력될 때까지 3단계 반복
5. 결과 집합 반환

⇒ 결국 T0 부터 Tn까지의 UNION ALL과 같은셈

정리하자면 다음과 같다. 먼저, 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인을 시작한다. 이어서, 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면 즉, 더 조인할 수 없으면 지금까지 만들어진 결과 집합을 모두 합하여 리턴한다. [그림 Ⅱ-2-10]에 있는 조직도를 쿼리로 출력했을 때, 대부분 사용자는 아래와 같은 결과를 기대할 것이다.(보기 편하도록 각 로우 앞쪽에 자신의 레벨만큼 빈칸을 삽입했다.)

하지만 실제 결과는 조직도와 많이 다르기 때문에 order by를 추가해서 원하는 순서대로 결과를 정렬해야 한다고함

결과 출력을 위해서 SORT라는 가상의 정렬용 칼럼을 추가해서 이를 통해 order_by 하는 예시

그래서 CTE 안에서 사번 (EmployeeID)을 재귀적으로 더해 정렬 기준으로 삼아 이를 sorting

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

셀프조인

셀프 조인(Self Join)이란 동일 테이블 사이의 조인

따라서 FROM 절에 동일 테이블이 두 번 이상 나타남 ⇒ 테이블/칼럼 명이 동일하기 때문에 반드시 Alias를 써줘야함

SELECT AliasName1.column, AliasName2.column ...
FROM Table1 AliasName1, Table2 AliasName2
WHERE AliasName1.column2 = AliasName2.column1

SELECT WORKER.ID 사원번호, WORKER.NAME 사원명, MANAGER.NAME 관리자명
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.ID;

• EMP를 각각 WORKER, MANAGER로 ALIAS

위에서 본 매니저/사원 예시는 사원/관리자가 모두 하나의 사원 개념. 만약 차상위 관리자 (관리자의 관리자)가 궁금하다면?

이를 셀프 조인으로 해결한 예시

SELECT E1.EMPNO AS 사원, E1.MGR AS 관리자, E2.MGR AS 차상위관리자
	FROM EMP E1
LEFT OUTER JOIN EMP E2
ON (E1.MGR = E2.EMPNO)
ORDER BY E2.MGR DESC, E1.MGR, E1.EMPNO

• 매니저=사원번호인 ON 조건절을 통해서 셀프조인