[MySQL] 재귀 (RECURSIVE) 테이블 생성

MySQL에서는 CTE (Common Table Expression)를 활용하여 재귀 테이블을 생성할 수 있다.

CTE를 활용하게 된다면, 재귀 쿼리를 이용해 메모리 상에 가상의 테이블을 저장하게 된다.

이를 통해, 시리즈 및 계층을 생성해서 다양하게 활용할 수 있다.

 

재귀 쿼리 사용법은 아래와 같다.

WITH RECURSIVE CTE (컬럼명1, 컬럼명2, ...) AS
(
SELECT 컬럼명1, 컬럼명2, ... -- 초기 값
FROM TABLE
WHERE 제어문
UNION ALL
SELECT 컬럼명1, 컬럼명2, ... -- 초기 값 이후 재귀하면서 계속 UNION 할 값
FROM CTE
INNER JOIN 
ON CTE.컬럼명 = TABLE.컬럼명 -- 요구하는 조건 잘 확인하기
WHERE 제어문
)
SELECT *
FROM CTE

 

아래 프로그래머스 예시를 통해, 조금 더 상세히 기술해보자.

 

https://school.programmers.co.kr/learn/courses/30/lessons/301650

프로그래머스

 

위 문제에서 요구하는 바는 본인이 몇 세대 인지를 확인하고 3세대인 대장균만 추출하라는 것이 었다.

위 해결 방법을 재귀 쿼리로 구현하면 아래 코드와 같다.

WITH RECURSIVE CTE(ID, PARENT_ID, LEVEL) AS
(
    SELECT ID, PARENT_ID, 1 -- initial row
    FROM ECOLI_DATA ED
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT ED.ID, ED.PARENT_ID, CTE.LEVEL + 1
    FROM CTE
    INNER JOIN ECOLI_DATA ED
    ON CTE.ID = ED.PARENT_ID -- 부모 ID를 구함
)
SELECT
     ID
FROM CTE
WHERE LEVEL = 3
ORDER BY
     ID
;

 

나는 것은 재귀 쿼리에서 조건을 줄 때, ON 절에 어떤 조건을 줘야 하는지 헷갈렸었는데 이 문제를 통해, 명확히 알게 됐다.

ON 절에서 CTE Table의 'ID'와 ECOLI_DATA Table의 'PARENT_ID'를 조건으로 준 이유는 아래와 같다.

• 초기 값에서 결과물이 (1, NULL, 1), (2, NULL, 1)로 나온다. 이 결과 값은 1세대 대장균을 나타낸 것이다.
• 재귀 조건에서 위 초기 값 결과물이 1세대 이므로 2세대를 찾기 위해서는 CTE TABLE의 ID(초기 결과값)와 ECOLI_DATA의 PARENT_ID(재귀되는 조건)를 일치시키는 조건을 주어야 한다.
  
  • 1세대: (1, NULL, 1), (2, NULL, 1)
  • 2세대: (3, 1, 2), (4, 2, 2), (5, 2, 2)

참고

https://jjon.tistory.com/entry/Recursive-CTECommon-Table-Expression-%ED%99%9C%EC%9A%A9

Recursive CTE(Common Table Expression) 활용