재귀 함수 (MySQL)

 

MySQL 기준 재귀 함수 작성법을 소개한다.

---

 

재귀 함수는 일반 프로그래밍 언어에서의 재귀와 같은 개념으로,

특정 조건의 달성까지 테이블을 지속적으로 갱신한다.

 

프로그래밍 언어에서의 재귀가 method 내의 같은 method라면,

SQL 에서의 재귀는 하나의 가상 table 내에서 재귀 조건이 달성될 때까지 레코드를 늘려나간다.

 

 

 

MySQL 기준, 재귀 함수는 CTE와 유사하게 작성되며, RECURSIVE 키워드를 사용한다.

WITH RECURSIVE recursive_name AS (
	
    -- 초기화 과정
    SELECT 
    	*
        , 1 AS depth		-- 그 외의 변수
    FROM 
    	root_table		-- 초기화에 참조할 테이블
    WHERE 
    	[...]			-- root 테이블에서 필요한 record만 필터링
    
    
    UNION ALL			-- 재귀 키워드
    
    
    -- 재귀 과정
    SELECT 
    	*
        , depth+1 AS depth
    FROM 
    	recursive_name		-- 재귀 함수명
    WHERE 
    	depth < 10		-- 재귀가 멈출 조건
    
)

SELECT *
FROM recursive_name;		-- 재귀 함수 호출

 

 

하나씩 살펴 보자.

 

---

 

초기화 부분

먼저 초기화 과정에서는 재귀를 시작하기 전,

재귀를 적용할 최초의 레코드를 삽입하는 부분이다.

 

그러니까 초기화 과정에서 적힌 코드 내용은 재귀 내용으로 적용되는 것이 아니라,

처음 재귀의 발판으로 사용되는 데이터들이다.

 

 

    -- 초기화 부분
    SELECT 
    	*
        , 1 AS depth		-- 그 외의 변수
    FROM 
    	root_table		-- 초기화에 참조할 테이블
    WHERE 
    	[...]			-- root 테이블에서 필요한 record만 필터링

 

프로그래밍 언어에서 사용되는 재귀 알고리즘처럼,

재귀의 끝을 알리기 위해 통상 증가되는 변수 하나를 사용한다.

 

재귀를 적용할 최초 데이터 추출을 위해,

해당 데이터가 있는 테이블과 조건을 FROM 과 WHERE로 적용하는데,

 

factorial, fibonacci 구현 등의 단순 수식 계산에 사용하고 싶다면

FROM 및 WHERE 절 없이, 상수만 선언된 SELECT를 사용할 수도 있다.

 

 

WITH RECURSIVE factorial(n, factorial_n) AS (

    SELECT 0, 1
    
    UNION ALL
    
    SELECT n+1, factorial_n * (n+1)
    FROM factorial
    WHERE n < 10
    
)

SELECT * FROM factorial;

 

위의 factorial 예시와 같이, 상수만으로도 recursive를 구성할 수 있으며,

표현하고자 하는 attribute가 많을 경우 파라미터 형식으로 선언할 수 있다.

 

 

---

 

재귀 키워드

재귀 과정에서 지속적으로 늘어나는 레코드에 대해,

어떻게 처리할 것인지를 명시해주는 키워드이다.

 

 

	UNION ALL / UNION

 

보통 초기화 과정과 재귀 과정을 이어주는 키워드로 UNION ALL / UNION 을 사용하며,

 

UNION ALL은 발생하는 중복 데이터를 허용하여 결과 레코드가 늘어날 수 있고,

UNION은 발생하는 중복 데이터를 제거하여 재귀 중간중간마다 검사해야 하기에 성능이 떨어질 수 있다.

 

일반적으로 UNION ALL 을 사용한다.

 

 

---

 

재귀 부분

초기 데이터들에 대하여,

실제로 어떤 재귀 알고리즘을 적용할 것인지 명시하는 부분이다.

 

 

    -- 재귀 부분
    SELECT 
    	*
        , depth+1 AS depth
    FROM 
    	recursive_name		-- 재귀 함수명
    WHERE 
    	depth < 10		-- 재귀가 멈출 조건

 

재귀 끝 조건을 설정하기 위해

매 recursive 마다 SELECT에서 depth를 1씩 늘려주고,

WHERE에서 depth 몇까지 허용할 것인가에 대한 조건을 명시한다.

 

* 이때 재귀가 멈출 조건을 재귀 부분 내부의 WHERE 에 명시할 수도 있고,

재귀 외부, 재귀 함수를 호출한 SQL 쿼리의 WHERE 에서 명시하는 등 다양하게 할 수도 있다.

 

 

초기화 부분에서 생성된 초기 데이터들은 가상의 테이블에 담기는데,

이때 해당 가상 테이블 이름이 우리가 명시한 재귀 함수명이 되고,

따라서 재귀 알고리즘을 적용하고 레코드를 쌓을 테이블로 FROM 에 재귀 함수명을 선언하면 된다.

 

재귀의 알고리즘은

위의 factorial 예시처럼 SELECT에 상수의 수식을 직접 명시하거나,

FROM 에서 최초 테이블 혹은 다른 테이블을 JOIN 하여 self join 과 유사한 조건을 명시하거나,

WHERE 에서 특정 조건으로 필터링할 수 있다.

 

 

---

 

재귀 함수 호출

작성된 재귀 함수는 외부 쿼리에서 호출할 수 있다.

재귀 함수는 CTE 로 서브쿼리처럼 선언하고 동작하기 때문에,

재귀 함수를 사용할 쿼리문 또한 필요하다.

 

 

SELECT * FROM recursive_name;

 

외부 쿼리에서 WHERE 등으로

재귀 과정 중 쌓인 전체 데이터를 필터링하거나

특정 조건에 맞는 필요한 데이터만 추출할 수 있다.

 

 

---

 

작성법은 MySQL 기준이므로,

다른 DBMS를 사용한다면 문법에 주의하기 바란다.