[MSSQL] WITH 절을 이용해 CTE(Common Table Expression) 표현하기

하이. 

 

오늘은 실무에서 종종 볼 수 있는 WITH 절의 활용 법을 포스팅 할 예정이다.

 

제목에서 보듯 WITH 절은 CTE, Common Table Experssion을 표현하기 위한 구문이다.

 

CTE?

CTE란 기존의 뷰나 파생 테이블, 임시 테이블 등으로 사용되는 것들을 대신할 수 있고, 보다 더 간결한 표현을 사용할 수 있따는 장점이 있다.

CTE는 Non-Recursive(비재귀) CTE 와 Recursive(재귀) CTE 로 두가지 종류가 있다.

 

Non-Recursive(비재귀) CTE

 

일단 상대적으로 간단한 Non-Recursive(비재귀) CTE 에 대해 먼저 알아보자.

 

Non-Recursive CTE의 경우는 보다 복잡한 쿼리문을 단순화 시키는데 주로 사용된다.

형식은 아래와 같다.

WITH CTE_테이블명(열이름1, 열이름2, 열이름3...)
AS
(
	<쿼리문>
)
SELECT 열이름1, 열이름2, 열이름3 ... FROM CTE_테이블명

위의 형식이 조금은 어색해 보여도 뜯어보면 단순하다.

기존에는 실제 DB에 있는 테이블을 이용했다면, CTE의 경우는 맨 아래 쿼리문에서 바로위의 WITH절에서 정의한 CTE_테이블명을 사용하는 것이다.

단순하게 CTE_테이블명 이라는 테이블이 하나더 있다고 생각하면 편하다.

 

이해가 쉽도록 예제를 하나들어보겠다.

 

아래 쿼리는 유저별 구매총합 을 구하는 쿼리이다.

SELECT userID, SUM(price*amount) FROM buyTbl GROUP BY userID

 

이 쿼리의 결과를 WITH절을 이용해 테이블처럼 만들고, SELECT 해오는 방식은 아래와 같다.

WITH CTE_Temp(userID, buySum)
AS
(
	SELECT userID, SUM(price*amount) FROM buyTbl GROUP BY userID
)
SELECT * FROM CTE_Temp ORDER BY buySum DESC;

결과를 보면 WITH 절에서 정의한 열이름이 잘 매핑되어 출력되는것을 볼 수 있다.

 

조금 더 복잡한 예시를 하나 더 들어보도록 하겠다.

만약에 userTbl 에서 각 지역별로 가장 작은 키를 한 명씩 뽑고, 그 사람들 키의 평균을 구하는 걸 쿼리로 짠다고생각해보자.

 

... 벌써 복잡하다.

WITH문을 사용한다면 아주 간결하고 심플하게 구할 수 있다.

 

아래의 쿼리는 지역별로 가장 작은키를 구하는 쿼리이다.

SELECT addr, MIN(height) FROM userTbl GROUP BY addr;

 

이쿼리를 이용해 WITH절은 만들고, 평균을 가져와보자.

WITH CTE_MINAVG(addr, minHeight)
AS
(
	SELECT addr, MIN(height) FROM userTbl GROUP BY addr
)
SELECT AVG(minHeight*1.0) AS [각 지역별 최저 키의 평균] FROM  CTE_MINAVG;

결과까지 잘 나오는 것을 확인할 수 있다.

 

그리고 WITH는 중복 CTE를 허용한다.

WITH
	TEMP1(userID, total)
	AS
	(
		SELECT userID, SUM(price*amount) FROM buyTbl GROUP BY userID
	),
	TEMP2(totalSum)
	AS
	(
		SELECT SUM(total) FROM TEMP1
	),
	TEMP3(avgSum)
	AS
	(
		SELECT totalSum / (SELECT count(*) FROM buyTbl) FROM TEMP2
	)

SELECT * FROM TEMP3;

위의 SQL을 보면 여러개의 쿼리문을 하나의 WITH 문안에 넣을 수 있다.

TEMP3는 TEMP1과 TEMP2를

TEMP2는 TEMP1을 참조할 수 있다.

 

WITH절은 한번 사용하고 나면 다시 사용할 수 없으니 주의하기 바란다.

 

Recursive(재귀) 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) 이라 부른다.

동작 원리는 아래와 같다.

1. 최초 호출 시 쿼리문1을 실행, 0 레벨로 초기화

2. 쿼리문 2 실행, 레벨 +1 증가, 만약 SELECT 결과가 빈 값이 아니라면 CTE_테이블명 다시 재귀 호출

3. 2번 과정 반복, SELECT 결과가 빈값이면 재귀 호출 중단

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

 

무슨말인지 이해가 안될 수 도 있다. 예시를 통해 보여주도록 하겠다.

 

아래 조직도를 보자. 이것은 한 회사의 직급 조직도를 나타낸 것이다

 

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

한눈에 파악이 되는가? 지금 사람 숫자도 얼마 없는데 이렇게 잘 표현이 안된다.

이때 재귀적 CTE를 이용해주면 한눈에 쉽게 표현이 된다.

WITH empCTE(empNo, empName, mgrNo, dept, level)
AS
(
	SELECT empNo, empName, manager, department, 0 FROM empTbl WHERE manager IS NULL
	UNION ALL
	SELECT empTbl.empNo, empTbl.empName, empTbl.manager, empTbl.department, empCTE.level+1
	FROM empTbl INNER JOIN empCTE ON empTbl.manager =empCTE.empNo
)
SELECT * FROM empCTE ORDER BY dept, level;

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

WITH empCTE(empNo, empName, mgrNo, dept, level)
AS
(
	SELECT empNo, empName, manager, department, 0 FROM empTbl WHERE manager IS NULL
	UNION ALL
	SELECT empTbl.empNo, empTbl.empName, empTbl.manager, empTbl.department, empCTE.level+1
	FROM empTbl INNER JOIN empCTE ON empTbl.manager =empCTE.empNo
)
SELECT replicate('		', level) + empName AS [직원이름], dept [직원부서] 
	FROM empCTE ORDER BY dept, level;

 

오늘은 여기까지 포스팅하도록 하겠다.

 

끝