[SQL] 재귀를 활용한 반복문 - WITH RECURSIVE

 Java 의 '반복문(for)' 과 비슷한 수행을 SQL 에서는 'Recursive CTE(Common Table Expressions)' 을 활용해 비슷하게 구현할 수 있다. 아무래도 한 번에 이해하기에는 어려워 Java 와 비교하며 이해하게 되었다.

 

1. 기본 문법

WITH RECURSIVE 임시테이블명 AS (
    SELECT 컬럼 첫 데이터 AS 컬럼별명  // 비재귀 SELECT 부분
    UNION ALL
    SELECT 이전 컬럼값에 대한 연산     // 재귀 SELECT 부분
    FROM 임시테이블명
    WHERE 컬럼에 대한 조건식
)

SELECT 컬럼명
FROM 임시테이블명

 

 'WITH RECURSIVE' 는 'Recursive Common Table Expressions(재귀 공통 테이블 표현식, 재귀 CTE)'이라 하는데 'UNION ALL' 또는 'UNION [DISTINCT]' 을 기준으로 '비재귀 SELECT 부분' 과 '재귀 SELECT 부분' 으로 구성된다.

 

구성 중 '재귀 SELECT 부분' 이 재귀호출 되며 연산을 수행해 행(row, tuple, record)을 추가하고, 이후 재귀를 통해 생성된 모든 행이 'UNION' 을 통해 합쳐져 임시테이블이 완성되는 것으로 보인다. 재귀 호출시 참조 임시테이블 행의 값이 WHERE 조건식을 통과하지 못할 경우 연산할 행이 없으므로 재귀는 종료된다.

 

 

2. 사용 예시

2-1. 순차적인 값을 가진 컬럼 생성

 숫자 '1' 부터 '10' 까지의 값을 순차적으로 갖는 컬럼 'num' 을 출력하는 쿼리를 작성하자.

num AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1
    FROM num
    WHERE n < 10
)

SELECT * FROM num

결과

 

2-2. 시간을 24시간 , 12시간(오전/오후)로 표현한 테이블 생성

 하루의 모든 시간을 24시간으로 표현한 'full_time' 컬럼과 12시간으로 표현한 'half_time', 오전 오후를 나타내는 'am_pm' 컬럼을 가진 결과 테이블을 출력하는 쿼리를 작성해 보자. 

WITH
RECURSIVE hour AS (
	SELECT 0 AS n, 0 AS m , 'am' AS am_pm
    UNION ALL    
    SELECT n+1, IF(m+1 > 11, m+1-12, m+1) , IF(n+1 > 11, 'pm', 'am')
    FROM hour
    WHERE n < 23
)

SELECT n AS full_time, IF(m = 0, 12, m) half_time, am_pm 
FROM hour

하루의 시간을 값으로하는 테이블

 

예시 문제를 설명이 부족했지만 하루의 모든 시간을 '24시간 표기법' 으로 사용하면 0시~24시 이고, '12시간 표기법' 을 사용하면 1시~12시에 오전 오후에 따라 'am', 'pm' 값을 갖는다. 이를 표현한 결과 테이블을 생성하는 것이 목표였다.

---

참고 문서

• Recursive Common Table Expressions - MySQL doc