[MSSQL] Recursive CTE & 조직도

이번에 CTE, Recursive CTE(재귀 CTE)를 보며

대표적인 재귀 CTE의 예시로 조직도를 표현하는 방식이 많은데, 대댓글 표현할 때도 아예 데이터베이스에서 처리해서 넘겨주는 것도 좋겠다는 생각이 들어 가져와보았다..*

먼저, CTE(Common Table Expression)란?

기존의 뷰나 파생 테이블, 임시 테이블 등으로 사용 되는 것을 대신해 더 간결하게 표현하려고 사용하는 것이다.
결국 뷰, 서브쿼리 역할을 하지만 WITH 라는 문구를 앞에 제시하면서 가독성 측면, 재귀 쿼리 같은 표현이 가능해지기에 사용하고 있다.
우선 임시테이블처럼 데이터를 저장하진 않고, 세션기준으로 유지되는 것이지만 그래서 더욱 탐색 속도는 빠르므로,
구조를 빠르게 판단하기에 좋은 구조인 것 같다. 

기본적인 CTE문은 아래와 같이 뷰처럼 사용 가능하다.

-- CTE 문
--[결과값]
--Empno	Ename	Deptno	Sal
--8023	MIA	    20	 3250
--8027	QUINN	  20	 3350
--8033	JANE	  20	 3200
--8043	CHARLIE	20	 3100

WITH HighEmployees AS (
    SELECT Empno, Ename, Deptno, Sal
    FROM EMP
    WHERE Sal > 3000
)
SELECT * FROM HighEmployees;

재귀 CTE란?

자기 자신을 참조해 계층적 데이터를 처리하거나 반복적인 쿼리를 수행할 때 사용하는 SQL 기능이다.

적어도 두개 이상의 CTE 쿼리 정의를 포함해야하며, 보통 UNION ALL, UNION, INTERSECT, EXCEPT 집합 연산자 중 하나를 사용해서 결합한다.

WTIH RECURSIVE cte_name(col1, col2, ...) AS (
	-- 1) 기본(Anchor) 쿼리: 재귀 호출 시작점
	-- 최초의 데이터를 가져오는 부분
	
	UNION ALL -- 기본 쿼리와 재귀 쿼리를 합친다
	
	-- 2) 재귀(Recursive) 쿼리: CTE, 자신을 참조
	-- CTE 자신을 참조해서 계속 데이터를 확장하는 부분
)
SELECT * FROM cte_name;

예시 코드

가장 흔하게 사용되는 조직도로 생각해보자.
manager_id 는 사수에 대한 id를 갖고 있는 column이다. 직원 테이블의 self-FK로 설정해둔 열이다.

id	name	manager_id
1	CEO	NULL
2	Manager1	1
3	Manager2	1
4	Staff1	2
5	Staff2	2
6	Staff3	3

아래의 예시 코드로 본다면,

1번 기본 쿼리 - manager_id IS NULL인 조건인 경우의 if문이라고 생각하면 좋고,
2번 재귀 쿼리 - manager_id IS NULL이 아닌 것에 대한 재귀 함수라고 생각하면 편하다.

• inner join 했을 때, 맞는 id 값이 없으면 “하위 직원이 없다고 생각하고, 재귀를 종료”한다.

WITH RECURSIVE EmployeeHierarchy AS (
    -- 1️⃣ 기본 쿼리 (CEO부터 시작)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL  -- 최상위 관리자(CEO)
    
    UNION ALL
    
    -- 2️⃣ 재귀 쿼리 (하위 직원 찾기)
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

위의 EmployeeHierarchy 의 결과를 보면, 아래처럼 level(depth) 열이 추가된 상태이다.

id	name	manager_id	level
1	CEO	NULL	0
2	Manager1	1	1
3	Manager2	1	1
4	Staff1	2	2
5	Staff2	2	2
6	Staff3	3	2

가독성 좋게 들여쓰기를 한다면?

이러면, 테이블에서 재귀함수는 아래와 같은 결과를 얻을 수 있다.

SELECT CONCAT(replicate(' ', level), IF(level > 0, '∟', ''), name) AS 조직도
FROM EmployeeHierarchy;

-- [결과값]
조직도
--------------
CEO
    ∟Manager1
        ∟Staff1
        ∟Staff2
    ∟Manager2
        ∟Staff3

위의 형태가 마치 우리가 보던 대댓글의 형태와 비슷하다.
이런 식으로 parent_comment 와 같은 부모 댓글 id를 기준으로 대댓글을 관리하는게 일반적인 것 같은데…

보통은 재귀함수처럼 대댓글을 보는 것은 잘 안하고, 백단에서 데이터의 DB 참조를 통해 처리하는 편인것 같다.