SQL - 재귀쿼리 (MYSQL)

테스트 데이터

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  parent_id INT
);

INSERT INTO categories (id, name, parent_id)
VALUES
  (1, 'Electronics', NULL),
  (2, 'Mobile Phones', 1),
  (3, 'Laptops', 1),
  (4, 'Smartphones', 2),
  (5, 'Tablets', 2),
  (6, 'Apple', 4),
  (7, 'Samsung', 4);

 

 

 

재귀쿼리

 재귀쿼리는 한 쿼리 내에서 자기 자신을 참조하는 쿼리를 말하낟. 계층 구조를 가진 데이터를 처리하거나 계층적인 쿼리를 작성하는데 유용하다. 재귀 쿼리는 일반적으로 WITH RECURSIVE문을 사용하여 작성한다.

 

재귀 쿼리는 3가지로 이루어지는데 

 

1. 기본쿼리(Anchor Query): 재귀적으로 처리할 시작점을 정의하고 이 쿼리는 재귀 쿼리의 첫번쨰 결과를 생성한다.

2. 재귀쿼리(Recursive Query): 자기 자신을 참조하면서 반복적을 실행되는 쿼리이다. 이 쿼리는 재귀적으로 처리할 추가 결과를 생성한다.

3. 종료조건(Termination Condition): 재귀적인 실행을 멈출 조건을 정의한다. 이 조건이 충족되면 재귀 쿼리는 중지된다.

 

WITH RECURSIVE CTE AS(
	SELECT id, name, parent_id, 0 AS level
	FROM categories
	WHERE parent_id IS NULL
	UNION ALL
	SELECT c.id, c.name, c.parent_id, level+1
	FROM categories c
	INNER JOIN CTE ON c.parent_id = CTE.id
    where c.id < 4
)

SELECT id, name, parent_id, level
FROM CTE
ORDER BY level, id;

흐름

- 먼저 CTE 가상 테이블은 재귀적은 CTE로서 게층 구조를 처리하기 위한 임시테이블이다. Anchor Query가 실행되기 이전에는 해당 테이블은 비어있는 상태이다.

- categories 테이블에서 parent_id가 null인 최상위 부모 카테고리인 Electronics을 선택하여 CTE에 추가된다. 이때 id는 1이다. 

- 재귀 쿼리(Recurisve Query)에 들어가고 categories 테이블에서 parent_id가 이전 단계에서 선택된 CTE.id와 일치하는 레코드를 찾는다. 이 경우 이전 단계에서 CTE.id는 1이므로 Mobile Phones와 Laptops가 해당 조건을 충족한다. 따라서 이 두 카테고리가 CTE에 추가된다. 따라서 두 카테고리는 CTE 테이블에 추가되는데 id 값은 각각 2와 3이된다.

- 이전 단계에서 id가 2와 3인 데이터를 CTE 테이블에 추가했다. 재귀는 DFS 방식을 따르기 때문에 id가 3인 경우를 먼저 쿼리가 반복된다. 그렇기에 

SELECT c.id, c.name, c.parent_id, level+1 
FROM categories c
INNER JOIN CTE ON c.parent_id = 2
WHERE c.id < 4;

// CTE.id는 2이다. CTE테이블에 쌓인 순서대로 재귀한다.(DFS방식)

이 쿼리가 두번째로 실행되고 쌓인 데이터만큼 실행된다.

 

여기서 다음 쿼리를 살펴보면

SELECT c.id, c.name, c.parent_id, level+1 
FROM categories c
INNER JOIN CTE ON c.parent_id = 3
WHERE c.id < 4;

이런식으로 계속 반복되면서 종료조건인 c.id < 4 일때까지 반복될 것이다.

 

활용

 

테이블 없이 Factorial 재귀쿼리로 구현해보기 

WITH RECURSIVE factorial AS(
	SELECT 5 as number, 1 as fac
    UNION ALL
    SELECT number - 1, number * fac 
    FROM factorial 
    WHERE number > 1
)
SELECT factorial.fac 
FROM factorial 
ORDER BY factorial.number 
LIMIT 1;