[MSSQL] 계층형 쿼리, 트리구조로 실적 구하기(WITH, 재귀 CTE)

 

 

출처 : https://hongik-prsn.tistory.com/78

MSSQL 계층형 쿼리, 트리구조로 실적 구하기(WITH, 재귀 CTE)

 

 

 

 

 

1. 트리구조 테이블로 만들기

예제로 사용할 데이터의 트리구조

이것은 한 회사의 부서 조직도를 나타낸 것이다 

 

이것을 테이블로 표현하면 아래와 같이 표현될 것이다. 

CREATE TABLE DEPT_TABLE (
    DEPT_CD           VARCHAR(4),
    PARENT_DEPT_CD    VARCHAR(4),
    DEPT_NM           VARCHAR(20)
)

INSERT INTO DEPT_TABLE VALUES ('0000', '', '전체')
INSERT INTO DEPT_TABLE VALUES ('1000', '0000', '임원실')
INSERT INTO DEPT_TABLE VALUES ('1001', '1000', '회장실')
INSERT INTO DEPT_TABLE VALUES ('1002', '1000', '사장실')
INSERT INTO DEPT_TABLE VALUES ('2000', '0000', '전략기획팀')
INSERT INTO DEPT_TABLE VALUES ('2001', '2000', '심사')
INSERT INTO DEPT_TABLE VALUES ('2010', '2000', '마케팅')
INSERT INTO DEPT_TABLE VALUES ('2011', '2010', '온라인마케팅')
INSERT INTO DEPT_TABLE VALUES ('2012', '2010', '오프라인마케팅')
INSERT INTO DEPT_TABLE VALUES ('3000', '0000', 'IT')
INSERT INTO DEPT_TABLE VALUES ('3010', '3000', '정보보안')
INSERT INTO DEPT_TABLE VALUES ('3020', '3000', 'IT혁신')
INSERT INTO DEPT_TABLE VALUES ('3021', '3020', '개발1팀')
INSERT INTO DEPT_TABLE VALUES ('3022', '3020', '개발2팀')

부서 테이블(DEPT_TABLE)은 부서코드(DEPT_CD)와 상위부서코드(PARENT_DEPT_CD)를 갖는다. 

 

---

2. 계층형 쿼리로 만들기(WITH 절)

오라클에서는 CONNECT BY, START WITH과 같은 내장함수로 간단하게 트리구조를 만들 수 있지만, MSSQL은 WITH 절에서 재귀적 CTE를 구현해야 계층형 쿼리를 만들 수 있다.

 

WITH 절에서 재귀적 CTE를 구현하는 형식은 아래와 같다.

WITH CTE_테이블명(열이름1, 열이름2, 열이름3 ...)
AS
(
    <쿼리문1 : SELECT * FROM TABLE_A>
    UNION ALL
    <쿼리문2 : SELECT * FROM TABLE_A JOIN CTE_테이블명>
)
SELECT * FROM CTE_테이블명;

위의 구문에서 쿼리문1을 앵커 멤버(Anchor Member, AC)라 부르고 쿼리문2를 재귀 멤버(Recursive Member, RM)라 부른다.

 

이를 적용해 보면, 동작 원리는 아래와 같다.

WITH DEPT_CTE AS (
    SELECT DEPT_CD, DEPT_NM, PARENT_DEPT_CD
         , 0 AS LEVEL
         , CONVERT(VARCHAR(500), DEPT_CD) AS CD_PATH
         , CONVERT(VARCHAR(500), DEPT_NM) AS NM_PATH
    FROM DEPT_TABLE WITH(NOLOCK)
    WHERE DEPT_CD = '0000'

    UNION ALL

    SELECT DT.DEPT_CD, DT.DEPT_NM, DT.PARENT_DEPT_CD
         , DC.LEVEL + 1
         , CONVERT(VARCHAR(500), DC.CD_PATH + '>' + DT.DEPT_CD) AS CD_PATH
         , CONVERT(VARCHAR(500), DC.NM_PATH + '>' + DT.DEPT_NM) AS NM_PATH
    FROM DEPT_TABLE DT WITH(NOLOCK)
    INNER JOIN DEPT_CTE DC
            ON DC.DEPT_CD = DT.PARENT_DEPT_CD
)
SELECT replicate('		', LEVEL) + DEPT_NM AS 부서명
     , * 
INTO #DEPT_CTE
FROM DEPT_CTE WITH(NOLOCK)
ORDER BY CD_PATH

1. 최초 호출 시 쿼리문1(앵커 멤버)을 실행, 부서이름이 '전체'인 루트노드를 DEPT_CTE라는 테이블을 최초 생성한다. 레벨은 0으로 초기화

 

2. 쿼리문2(재귀 멤버)를 실행, 레벨 +1 증가, 최초 생성된 DEPT_CTE 테이블의 부서코드와 부서 테이블의 부모코드로 조인해서 루트노드(0000)가 부모노드인 노드들을 가져온다.

 

3.  만약 SELECT 결과가 빈 값이 아니라면 쿼리문2(재귀 멤버)를 다시 재귀 호출. 2번 과정 반복, 부서테이블의 부모노드가 임원실(1000), 전략기획팀(2000), IT(3000)인 노드들을 가져온다. 레벨은 +1 증가

 

3. SELECT 결과가 빈값이면 재귀 호출 중단

 

4. 외부의 SELECT 문을 실행해서 앞 단계에서의 누적 결과를 UNION ALL을 통해 가져옴.

레벨 값과 REPLICATE함수를 이용해주면 보다 더 한눈에 표현이 가능하게할 수 있다.

 

---

3. 각 부서별 실적을 집계해보자.

실적은 리프노드 부서들만 넣어서 테이블을 만들어 준다.

CREATE TABLE DEPT_PERFORMANCE (
    DEPT_CD    VARCHAR(4),
    DEPT_AMT   BIGINT
)

INSERT INTO DEPT_PERFORMANCE VALUES ('1001', '1000')
INSERT INTO DEPT_PERFORMANCE VALUES ('1002', '2000')
INSERT INTO DEPT_PERFORMANCE VALUES ('2001', '3000')
INSERT INTO DEPT_PERFORMANCE VALUES ('2011', '1000')
INSERT INTO DEPT_PERFORMANCE VALUES ('2012', '2000')
INSERT INTO DEPT_PERFORMANCE VALUES ('3010', '3000')
INSERT INTO DEPT_PERFORMANCE VALUES ('3021', '1000')
INSERT INTO DEPT_PERFORMANCE VALUES ('3022', '2000')

SELECT DEPT_AMT, A.*
FROM #DEPT_CTE A WITH(NOLOCK)
OUTER APPLY (
    SELECT SUM(DEPT_AMT) DEPT_AMT
    FROM DEPT_PERFORMANCE B WITH(NOLOCK)
    INNER JOIN #DEPT_CTE C WITH(NOLOCK)
            ON C.DEPT_CD = B.DEPT_CD
           AND C.CD_PATH LIKE A.CD_PATH+'%'
) DP
ORDER BY CD_PATH

1. DEPT_CTE 테이블에 실적 테이블(DEPT_PERFORMANCE)을 조인해 준다.

 

2. CD_PATH와 LIKE를 조건으로 DEPT_CTE 테이블을 다시 조인해서 SUM을 해준다. 1번 이미지와 2번 이미지의 NM_PATH를 보면 이해하기 쉬울 것이다.

DEPT_CTE 테이블을 다시 조인하고 SUM을 하기 전 결과값

 

3. DEPT_AMT를 보면 각 계층별 부서별 합계가 구해진 것을 볼 수 있다.