해당 글에서는 WITH ~ [RECURSIVE] CTE를 이용하는 방법에 대해서 알아봅니다
1) CTE(Common Table Expression)
💡 CTE(Common Table Expression)
- SQL에서 사용되는 기능으로 복잡한 쿼리를 간결하게 작성할 수 있게 해주는 임시 결과 집합입니다. - WITH 구문을 사용하여 쿼리 내에서 한 번 정의되며, 그 후에는 쿼리의 여러 부분에서 여러 번 ‘재사용’될 수 있습니다. - 쿼리의 가독성을 향상하고, 복잡한 조인과 집계 로직을 간결하게 표현할 수 있도록 돕습니다.
CTE 종류
사용목적
일반 CTE
단순히 복잡한 쿼리를 단순화하는 데 사용
재귀 CTE
쿼리가 자기 자신을 참조하고 반복적으로 실행되는 것을 가능하게 해서, 계층형 데이터를 쉽게 쿼리할 수 있게 해줍니다.
2) WITH ~ CTE(Common Table Expression)
💡 WITH ~ CTE(Common Table Expression)
- MySQL 8.0 이상 버전부터 지원하며 ‘임시 결과 집합’을 정의하여 ‘재 사용’하는 방식을 의미합니다. - 순서에 의한 절차적으로 작성을 하며, 간결하고 읽기 쉽게 만드는데 도움이 됩니다.
1. WITH ~ CTE 기본 형식
💡 기본형식
- 해당 형태는 subquery1, subquery2라는 SQL문의 임시 결과 집합을 정의하고, 최종적으로 SELECT 문에서 구성한 임시 결과 집합을 조회하는 기본 형식입니다.
- cteName1, cteName2는 일시적인 결과 집합을 의미합니다. - subquery1, subquery2는 결과 집합을 생성하는 SQL 쿼리를 의미합니다. - 최종적으로 SELECT 문을 사용하여 CTE의 결과를 조회하는 형태입니다.
WITH [RECURSIVE]
cteName1 AS
(
subquery1
),
cteName2 AS
(
subquery2
)
SELECT *
FROM cteName1, cteName2;
2. 사용예시 -1 : 기본 예시
💡 사용예시 -1 : 기본 예시
- 해당 SQL문에서는 WITH 문과 CTE를 사용하여 두 개의 서브쿼리(cte1, cte2)를 정의하고 이들을 이용하여 최종적인 쿼리를 수행합니다.
- cte1라는 이름의 CTE를 생성하고 a, b 컬럼을 반환하는 서브쿼리가 구성되었습니다. - cte2라는 이름의 CTE를 생성하고 c, d 컬럼을 반환하는 서브쿼리가 구성되었습니다. - 마지막으로 cte1과 cte2를 사용하여 INNER JOIN을 수행하여 최종적으로 b, d 컬럼의 값을 선택하여 반환합니다.
WITH
-- 1. CTE1 이름 지정 및 서브쿼리 구성
cte1 AS
(
SELECT a, b FROM table1
),
-- 2. CTE2 이름 지정 및 서브쿼리 구성
cte2 AS
(
SELECT c, d FROM table2
)
-- 3. CTE를 이용한 최종 결과값 반환
SELECT b,d
FROM cte1
INNER JOIN cte2
ON cte1.a = cte2.c;
3. 사용예시 -2 : 부서별 최대 급여 조회
3.1. 부서(Dept), 직원(Emp) 테이블 DDL
-- Dept Table
CREATE TABLE `Dept` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`pid` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '상위부서id',
`dname` varchar(31) NOT NULL,
PRIMARY KEY (`id`)
)
-- Empt Table
CREATE TABLE `Emp` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ename` varchar(31) NOT NULL,
`dept` tinyint unsigned NOT NULL,
`salary` int NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `dept` (`dept`),
CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `Dept` (`id`)
)
💡 WITH ~ CTE 사용 구문 - 해당 부분에서는 WITH ~ CTE를 이용하여 평균 급여가 높은 부서와 평균 급여가 낮은 부서 조회하여, 이에 대해 높은 부서와 낮은 부서와의 급여 차액을 출력하는 예시입니다.
1. AVGSAL AS
- 부서 당 평균 급여를 계산하는 서브쿼리를 임시 결과 집합인 ‘AVGSAL’에 정의하였습니다.
2. MAXAGSAL AS
- 부서 당 평균급여 값(AVGSAL)을 받아서 그 중 가장 많이 받는 부서를 조회하였습니다.
3. MINAVGSAL AS
- 부서 당 평균급여 값(AVGSAL)을 받아서 그 중 가장 적게 받는 부서를 조회하였습니다.
4. SUMUP AS
- 부서의 평균급여가 가장 높은 값(MAXAGSAL)과 부서의 평균급여가 가장 낮은 값(MINAVGSAL)을 기반으로 두 개의 부서를 합쳐서 조회를 하였습니다.
5. UNION SELECT ~
- 추가적으로 최소와 최대의 ‘평균 급여차액’을 추가하여서 평균 최대 급여를 받는 부서와 평균 최소 급여를 받는 부서와의 차액을 구하여 출력을 하였습니다.
WITH
-- 직원 당 평균 급여
AVGSAL AS (
SELECT d.dname
, AVG(e.salary) AS avgSal
FROM DEPT d
INNER JOIN Emp e
ON d.id = e.dept
GROUP BY d.id
),
-- 최대 급여
MAXAGSAL AS(
SELECT *
FROM AVGSAL
ORDER BY AVGSAL DESC
LIMIT 1
),
-- 최저 급여
MINAVGSAL AS(
SELECT *
FROM AVGSAL
ORDER BY AVGSAL
LIMIT 1
),
-- 최대 급여와 최저 급여를 합함.
SUMUP AS(
SELECT '최고' AS gb, m1.* FROM MAXAGSAL m1
UNION
SELECT '최저' AS gb, m2.* FROM MINAVGSAL m2
)
SELECT * FROM SUMUP
UNION
SELECT ''
, '평균급여차액'
, FORMAT((MAX(AVGSAL) - MIN(AVGSAL)) * 1000, 0)
FROM SUMUP;
3.4. 결과 확인
💡 결과 확인 - 순차적으로 계산된 값을 기반으로 평균 급여가 높은 부서와 평균 급여가 낮은 부서를 출력하였으며, 평균 급여 차액을 출력하는 결과를 확인하였습니다.
3) WITH ~ [RECURSIVE] CTE (Common Table Expression)
💡 WITH ~ RECURSIVE CTE(Common Table Expression)
- WITH ~ CTE와 동일하게 SQL 쿼리에서 임시 결과 집합을 정의하고 저장해 두는 방법입니다. 하지만 RECURSIVE 키워드가 추가되어서 순환(재귀) 쿼리를 사용하여 복잡한 데이터 구조를 쉽게 구성이 가능합니다.
- 순환(재귀)라는 말은 내 자신을 다시 참조하여 호출하여 반복적으로 진행되는 것을 의미합니다. 해당 절 내에서는 내 자신을 호출하여 재귀적인 쿼리를 가능하게 만드는 기능을 의미합니다.
1. 기본 형식
💡 기본형식 - 해당 쿼리 내에서는 WITH RECURSIVE를 사용하여 재귀적인 CTE를 정의하고 있습니다.
1. cte_name1(p1, p2, p3)
- 임시 테이블 명과 그 테이블의 열을 지정합니다.
2. SELECT 1, 0, 1
- 각각 p1, p2, p3 컬럼의 초기 값을 할당합니다.
3. UNION ALL SELECT ... FROM test WHERE p1 < 10
- 해당 부분은 재귀적인 쿼리를 수행하는 부분입니다. WHERE 절로 최대 p1이 10이 넘지 않는 범위 내에 까지 계속 반복 수행합니다.
4. SELECT * FROM test;
- 최종적인 결과를 반환 받습니다.
-- 1. CTE 이름 지정 및 컬럼 지정
WITH RECURSIVE cte_name1(p1, p2, p3) AS
(
-- 2. 초기 값을 지정
SELECT 1, 0, 1
UNION ALL
SELECT
-- 3. 초기 값을 이용하여 값을 재귀적으로 수행하며, 조건을 만족하면 수행을 멈춤
SELECT p1 + 1
, p2
, p2 + p3
FROM test
WHERE p1 < 10
)
-- 4. 최종 결과값 반환
SELECT * FROM test;
2. 사용예시 -1 : 피보나치수열
💡 사용예시 -1 : 피보나치수열
- 해당 예시에서는 WITH ~ 재귀 CTE를 이용하여 피보나치수열을 출력하는 예시입니다.
1. WITH RECURSIVE fibonacci(n, fib_n, next_fib_n)
- WITH RECURSIVE 구문은 임시 결과 집합인 CTE를 선언하고 있습니다. - fibonacci라는 이름을 가지며, n, fib_n, next_fib_n 3개의 컬럼으로 구성되어 있습니다.
2. SELECT 1, 0, 1
- 이 부분은 CTE의 초기값을 설정합니다. 여기서 n(수열의 인덱스)은 1, fib_n(n번째 피보나치 수)은 0, next_fib_n(n+1번째 피보나치 수)은 1로 설정됩니다.
3. UNION SELECT ~
- 이 부분은 재귀적인 쿼리를 수행하는 부분입니다. - n은 1씩 증가하며, fib_n은 이전의 next_fib_n 값으로 업데이트되고, next_fib_n은 fib_n과 next_fib_n의 합으로 업데이트됩니다. - 이 과정은 n 값이 10보다 작은 동안 반복적으로 수행됩니다.
4. SELECT * FROM fibonacci
- 최종적으로 fibonacci CTE의 모든 값을 조회합니다. 이 결과로 피보나치수열의 처음 10개 값을 얻을 수 있습니다.
-- 재귀 CTE를 이용한 피보나치 수열
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS
(
-- 초기값 지정
SELECT 1, 0, 1
UNION ALL
-- 초기값 지정 이후 수행되는 값, n이 10이 되기전까지 수행됨.
SELECT n + 1
, next_fib_n
, fib_n + next_fib_n
FROM fibonacci
WHERE n < 10
)
SELECT * FROM fibonacci;
3. 사용예시 -2 : 상위 부서와 하위 부서 출력
3.1. 부서 테이블(Dept) DDL
💡 부서 테이블 DDL
- 부서 테이블은 ID, PID, DNAME 형태로 구성이 되어 있고, PID 컬럼은 상위 부서 아이디를 가리키고 있습니다.
CREATE TABLE `Dept` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`pid` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '상위부서id',
`dname` varchar(31) NOT NULL,
PRIMARY KEY (`id`)
)
3.2. 부서 테이블(Dept) DML
💡 부서 테이블 DML
- 아래와 같이 부서 테이블에 대한 임시 Dummy 데이터를 추가하였습니다. - 아래의 데이터를 상위부서와 하위부서로 출력을 합니다.
- 해당 부분에서는 WITH ~ RECURSIVE CTE를 이용하여 상위부서와 하위부서를 출력하는 예시입니다.
1. WITH RECURSIVE CTEDEPT(id, pid, pname, dname, dept, h) AS
- WITH RECURSIVE 구문은 임시 결과 집합인 CTE를 선언하고 있습니다 - fibonacci라는 이름을 가지며 id(부서 시퀀스), pid(상위부서), pname(상위부서이름), dname(부서이름), dept(부서별 dept), h(상위부서-하위부서)라는 6개의 컬럼으로 구성되어 있습니다.
2. SELECT id, pid, CAST('' AS CHAR(31)), dname, 0, CAST(id AS CHAR(10))
- 상위 부서(pid가 0인 부서)를 선택하는 부분입니다.
3. SELECT d.id, d.pid, cte.dname, d.dname, dept + 1, CONCAT(cte.h, '-', d.id) - 상위 버서의 하위 부서를 선택하는 부분을 재귀적으로 수행이 됩니다. - 상위 부서의 id가 하위 부서의 pid와 같은 부서를 찾아냅니다. dept는 상위 부서의 dept + 1로 설정이 되며 h는 상위 부서의 h 값에 하위 부서의 id를 연결하여 설정합니다.
4. SELECT dept, dname FROM CTEDEPT ORDER BY h; - 결론적으로 구성한 CTEDEPT에서 dept, dname 값을 가져온 뒤 h 값을 기준으로 오름차순 정렬을 수행하여 값을 가져옵니다.
-- 부서를 트리별 구조로 출력합니다.
WITH RECURSIVE CTEDEPT(id, pid, pname, dname, dept, h) AS
(
-- 상위 부서를 기준으로 합니다.
SELECT id, pid, CAST('' AS CHAR(31)), dname, 0, CAST(id AS CHAR(10))
FROM DEPT
WHERE pid = 0
UNION ALL
-- 상위 부서(dept:0) > 하위 부서(dept:1) > 최하위 부서(dept:2)로 출력합니다
SELECT d.id, d.pid, cte.dname, d.dname, dept + 1, CONCAT(cte.h, '-', d.id)
FROM DEPT d
INNER JOIN CTEDEPT cte
ON d.pid = cte.id
)
-- SELECT * FROM CTEDEPT ORDER BY h;
SELECT dept, dname FROM CTEDEPT ORDER BY h;
3.4. 결과확인
💡 결과 확인
- 아래와 같이 최상위 부서(dept=0)와 그 하위 부서(dept=1, 2)에 대해 순차적으로 출력이 됨을 확인할 수 있습니다.
4) WITH ~ [RECURSIVE] ~ CTE 무한 루프 방지
💡 WITH ~ [RECURSIVE] CTE 무한 루프 방지
- 재귀 쿼리를 수행하는 도중에 무한으로 루프를 수행되는 문제가 발생할 수 있습니다.
1. 현상 확인
💡 현상 확인
- 아래의 예시는 피보나치수열을 수행하는 재귀 CTE입니다. - 해당 SQL문 내에서 WHERE n < 10인 조건이 없다면 해당 재귀 CTE는 제한 없이 무제한으로 반복이 됩니다.
-- 재귀 CTE를 이용한 피보나치 수열
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS
(
-- 초기값 지정
SELECT 1, 0, 1
UNION ALL
-- 초기값 지정 이후 수행되는 값
SELECT n + 1
, next_fib_n
, fib_n + next_fib_n
FROM fibonacci
-- WHERE n < 10
)
SELECT * FROM fibonacci;
2. 현상 제한 방법
2.1. 탈출 조건 설정
💡 탈출 조건 설정
- 재귀 쿼리는 무한히 반복될 수 있으므로, 재귀가 종료될 탈출 조건을 명확하게 설정해야 합니다. 이 탈출 조건은 WHERE 절을 통해 설정할 수 있습니다. - 예를 들어, 특정 횟수 이상 재귀를 반복하지 않도록 하거나, 특정 조건이 만족될 때까지만 재귀를 반복하도록 설정할 수 있습니다.
💡 아래의 SQL문에서 수행되는 값 내에 WHERE 절로 특정 조건에 만족할 경우 종료하도록 처리를 해야 합니다.
-- 재귀 CTE를 이용한 피보나치 수열
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS
(
-- 초기값 지정
SELECT 1, 0, 1
UNION ALL
-- 초기값 지정 이후 수행되는 값
SELECT n + 1
, next_fib_n
, fib_n + next_fib_n
FROM fibonacci
WHERE n < 10
)
SELECT * FROM fibonacci;
2.2. SET SESSION 이용 : 실행 횟수 제한
💡 SET SESSION 이용 : 실행 횟수 제한
- MySQL에서 세션별 시스템 변수를 설정하는 명령어입니다. - 이 명령어를 사용하면 현재 연결된 세션에만 적용되는 설정을 할 수 있습니다. 설정이 완료된 후에는 해당 세션에서만 그 설정이 유지되며, 다른 세션에는 영향을 주지 않습니다. - 세션이 종료되면 설정도 사라집니다. 따라서, 일시적이거나 특정 작업에만 필요한 설정을 할 때 유용합니다.
명령어
설명
SET SESSION cte_max_recursion_depth = 20;
세션의 최대 재귀 깊이를 20으로 설정합니다. 이는 재귀적 쿼리가 반복될 수 있는 최대 횟수를 제한합니다.
SET SESSION max_execution_time = 1000;
최대 실행 시간을 1000 밀리초(1초)로 설정합니다. 이는 쿼리가 실행될 수 있는 최대 시간을 제한합니다.
💡 SET SESSION cte_max_recursion_depth = 9; 적용
- CTE 재귀를 최대 9번까지에 대해 적용하였습니다.
💡 실제 재귀를 9번 이상으로 수행하도록 구성했을 때, 아래와 같은 오류가 발생하여 수행되지 않습니다.
- Error Code: 3636. Recursive query aborted after 10 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
[ 더 알아보기 ] 💡 cte_max_recursion_depth와 max_execution_time 설정의 디폴트 값은 몇일까?
show global variables like '%cte_max_recursion_depth%';
show global variables like '%max_execution_time%';
