[DB] WITH AS절 사용법 (feat.CTE)

WITH절 이란?

WITH절은 이름이 부여된 서브쿼리 이며, CTE(Common Table Expression)를 표현하기 위한 구문이다.
임시테이블을 만든다는 관점에서본다면 VIEW와 쓰임새가 비슷한데 차이점이 있다면 VIEW는 한번 만들어놓으면 DROP할때까지 없어지지 않지만 WITH절의 같은경우 한번 실행할 쿼리문내에 정의되어 있을경우, 그 쿼리문안에서만 실행된다는 차이점이 있다.

 CTE(Common Table Expression) 란?

1. 기존의 View,파생 Table, 임시Table 등으로 사용되던 것을 대신할 수 있다.
2. 더 간결한 식으로 보여지는 장점이 있다.
3. CTE는 크게 비재귀적CTE, 재귀적CTE 두가지가 있다.
 

 비재귀적 CTE

: 재귀적이지 않은 CTE, 단순한 형태이며 복잡한 쿼리문장을 단순화 시키는 데에 적합하게 사용된다.
 
우선 비재귀적 CTE 형식을 살펴보자.

WITH CTE_테이블이름(열이름)
AS(
<쿼리문>
)
SELECT 열이름 FROM CTE_테이름이름 ;

※ 비재귀적 CTE에는 SELECT 필드들 FROM CTE_테이블이름 구문 외에 UPDATE 등도 가능하지만, 주로 사용되는 것은 SELECT문이다.
 
위의 형식이 좀 생소해보일 수도 있지만, 위쪽을 떼버리고 SELECT 열이름 FROM CTE_테이블이름 구문만 생각해도된다.
그런데 이 테이블을 기존에는 실제 DB에 있는 테이블을 사용했지만, CTE는 바로 위의 WITH절에서 정의한 'CTE_테이블이름' 을 사용하는 것만 다르다.
즉, WITH CTE_테이블이름(열이름) AS ··· 형식의 테이블이 하나 더 있다고 생각합면 된다.
 
쉽게 이해하기 위해서 buyTBL 이라는 테이블에서 총 구매액을 구하는 것을 해보자.

USE sqlDB;

SELECT user_id AS '사용자',
	   SUM(price * amount) AS '총 구매액'
FROM buyTBL
GROUP BY user_id;

<쿼리 결과>

사용자	총구매액
BBK	1,920
EJW	95
JYP	200
KBS	1,210
SSK	75

 
위의 결과를 총 구매액이 많은 사용자 순서로 정렬하고 싶다면 어떻게 해야할까?
물론, 위 작성된 쿼리에 이어서 ORDER BY 문을 첨가해도 된다.
그러나 그럴 경우 SQL문이 더욱 복잡해보여 가독성이 떨어질 수 있으므로, 위의 쿼리의 결과가 abc 라는 이름의 테이블이라고 생각해보자.
그렇다면 정렬하는 쿼리는 다음과 같이 간단해진다.

SELECT *
FROM abc
ORDER BY 총구매액 DESC;

이것이 CTE의 장점 중 하나인 구문 단순화이다.
지금까지 얘기한 실질적인 쿼리문은 다음과 같이 작성하면 된다.

WITH abc(user_id,total)
AS
(SELECT user_id,SUM(price*amount)
		FROM buy_TBL
        GROUP BY user_id )
 SELECT *
 FROM abc
 ORDER BY total DESC;

<CTE 쿼리의 결과>

user_id	total
BBK	1,920
KBS	1,210
JYP	200
EJW	95
SSK	75

< CTE의 작동 >

 

제일 아래의 'FROM abc ' 구문에서  abc는 실존하는 테이블이 아니라, 바로 위에 네모로 표시 된 WITH구문으로 만든 SELECT의 결과다. 단, 여기서 'AS(SELECT ···' 에서 조회하는 열과 'WITH abc(···'과는 개수가 일치해야 한다.

 

예시

다른 예로 하나 더 연습을 해보자!!

 

 

회원테이블(userTBL) 에서 각 지역별로 가장 큰 키를 1명씩 뽑은 후에, 그 사람들 키의 평균을 내보자.

 

만약, 전체의 평균이라면 AVG(height) 만 사용하면되지만, 각 지역별로 가장 큰 키의 1명을 우선 뽑아야하므로 얘기가 좀 복잡해진다.

이럴땐 한번에 생각하지 말고, 단계별로 생각을 해보자.

 

[1단계]  : "각 지역별로 가장 큰 키" 를 뽑는 쿼리 작성

SELECT addr,
	   MAX(height)
FROM userTBL
GROUP BY addr

[2단계] : [1단계] 쿼리를 WITH 구문으로 묶기

WITH cte_userTBL(addr,maxHeight)
AS
( SELECT addr,
		MAX(height)
        FROM userTBL
        GROUP BY addr )

[3단계] : " 키의 평균을 구하는 쿼리를 작성"

SELECT AVG(키)
FROM CTE_테이블이름

[4단계] : [2단계] +[3단계]

이 예시에서는 키의 평균을 실수로 만들기 위해서 키에 1.0을 곱하여 실수로 변환했다.

WITH cte_userTBL(addr,maxHeight)
AS
(SELECT addr,
		MAX(height)
 FROM userTBL
 GROUP BY addr
)
SELECT AVG(maxHeight*1.0) AS '각 지역별 최고키 평균'
FROM cte_userTBL;

CTE는 VIEW 와 용도는 비슷하지만, 개선된 점이 많다.

또한 VIEW는 계속 존재해서 다른 구문에서도 사용할 수 있지만, CTE와 파생 테이블은 구문이 끝나면 같이 소멸한다.

즉, 위의 예시에서 사용한 cte_userTBL은 다시 사용할 수 없다.

 

추가로, CTE는 다음 형식과 같은 중복 CTE가 허용된다.

WITH 
AAA(칼럼들)
AS (AAA의 쿼리문),
	BBB(컬럼들)
    AS (BBB의 쿼리문),
    CCC(컬럼들)
    AS (CCC의 쿼리문)
SELECT *
FROM [AAA || BBB || CCC]

※ 주의할 점

CCC의 쿼리문에서는 AAA나 BBB를 참조할 수 있지만,

AAA의 쿼리문이나 BBB의 쿼리문에서는 CCC를 참조할 수 없다.

즉, 아직 정의되지 않은 CTE를 미리 참조할 수 없다.

 

출처 및 참고문헌 : 이것이 MARIA DB다 _215P~219P