상권's
재귀쿼리에 대해 알아보자. 본문
오늘은 재귀쿼리가 무엇인지 알아보고, 실제 데이터에서 어떻게 실행되는 지 알아보겠습니다.
재귀 쿼리란,
먼저 재귀의 의미는 다음과 같습니다.
재귀(recursion)은 어떠한 것을 정의할 때 자기 자신을 참조하는 것을 뜻한다
출처 : https://ko.wikipedia.org/wiki/%EC%9E%AC%EA%B7%80
이처럼 자기 자신을 참조하면서 특정 쿼리를 실행 시키는 것이 재귀 쿼리입니다.
저는 계층적 구조를 가진 데이터를 확인하기 위해 재귀 쿼리를 사용했으며,
재귀 쿼리를 실행하기 위해 CTE(Common Table expression)를 사용하여 재귀적 CTE를 작성했습니다.
CTE(Common Table Expression)
CTE(공통 테이블 식)는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있습니다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷합니다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있습니다.
재귀적 CTE는 다음 세 요소로 구성됩니다.
1. 루틴의 호출재귀적
CTE의 첫 번째 호출은 UNION ALL, UNION, EXCEPT 또는 INTERSECT 연산자로 조인된 하나 이상의 CTE_query_definitions로 구성됩니다. 이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 합니다.
CTE_query_definitions는 CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주됩니다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 합니다.
2. 루틴의 재귀 호출
재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 CTE_query_definitions가 포함됩니다. 이러한 쿼리 정의를 재귀 멤버라고 합니다.
3. 종료 확인
종료 확인은 암시적으로 수행됩니다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지됩니다.
https://learn.microsoft.com/ko-kr/previous-versions/sql/sql-server-2008-r2/ms190766(v=sql.105)
위의 설명처럼 재귀적 CTE는 Anchor Member와 Recursive Member로 이루어져 있습니다.
조금 더 간단하게 설명한다면, Anchor Member는 초기에 Recursive Member가 참조할 수 있는 데이터를 생성하기 위한 쿼리입니다. Anchor Member를 참조하여 실행될 쿼리가 Recursive Member입니다.
마지막으로 Anchor Member와 Recursive Member로 이루어진 CTE를 사용하여 만들어진 데이터를 바탕으로 원하는 작업을 할 수 있습니다.
실행시켜보기
테이블 정보
재귀적 쿼리를 테스트하기 위해 다음과 같이 테이블을 정의해봤습니다.
메뉴는 폴더와 파일과 같은 계층적 구조로 이루어져 있어 parentId를 가집니다.
메뉴가 폴더 타입일 경우 parentId가 -1이 아니며, 이 외의 메뉴는 파일로써 리포트와 연결되어 있습니다.
이러한 테이블들을 사용해서 메뉴를 계층적으로 정렬하고 리포트와 사용자 정보를 join할 수 있도록 쿼리를 작성해보겠습니다.
그리고 폴더인 메뉴(parentId가 -1인 메뉴)에 숫자를 매기고, 이 숫자를 사용하여 계층적 구조를 확인해보겠습니다.
먼저, WITH RECURSIVE cte as ()를 사용해 재귀쿼리에서 사용할 임시 테이블을 생성합니다.
괄호 내부에서 먼저, Anchor Member를 위한 쿼리를 작성합니다.
WITH RECURSIVE cte as (
SELECT
ROW_NUMBER() over (order by parentId) as no,
menu.name as menuName,
menu.id as menuId,
(select user.userName from recursiveTest.User as user
where menu.ownerId = user.userId) as owner,
report.name as reportName
from recursiveTest.Menu as menu
LEFT OUTER JOIN recursiveTest.Report as report ON menu.id = report.menuId
where menu.parentId = -1
)Anchor Member을 통해서 하위 메뉴를 포함하는 root 메뉴를 찾습니다.
첫 번째 컬럼은 하위 메뉴를 가지는 폴더 타입의 root 메뉴를 분리하기 위해 ROW_NUMBER 함수로 각 폴더 타입의 메뉴에 고유한 값을 부여합니다.
이후 필요한 정보에 맞춰 쿼리를 작성했습니다.
다음으로는 하위 메뉴를 찾고, root 메뉴에 할당하기 위해 Recursive Member를 위한 쿼리를 작성합니다.
Anchor Member를 기준으로 하위 menu들을 담아주기 위해, 위에서 만든 가상 테이블의 menuId와 parentId가 동일한 menu를 찾습니다.
제가 경험했을 때 Recursive Member 쿼리 작성 시 가장 중요한 점은 Anchor Member와 각 컬럼의 타입이 맞아야 한다는 점입니다. 그렇기에 각 Member 쿼리를 작성할 때 컬럼의 순서에 유의하고 필요할 경우 CONVERT와 같은 타입 변환 함수가 필요하기도 합니다.
UNION ALL
SELECT
c.no,
m.name as parentMenuName,
m.id as menuId,
(select u.userName from recursiveTest.User as u
where m.ownerId = u.userId) as Owner,
r.name
from recursiveTest.Menu as m
INNER JOIN cte as c ON c.menuId = m.parentId
LEFT OUTER JOIN recursiveTest.Report as r ON m.id = r.menuId
)
최종적인 쿼리는 다음과 같습니다.
WITH RECURSIVE cte as (
SELECT
ROW_NUMBER() over (order by parentId) as no,
menu.name as menuName,
menu.id as menuId,
(select user.userName from recursiveTest.User as user
where menu.ownerId = user.userId) as owner,
report.name as reportName
from recursiveTest.Menu as menu
LEFT OUTER JOIN recursiveTest.Report as report ON menu.id = report.menuId
where menu.parentId = -1
UNION ALL
SELECT
c.no,
m.name as parentMenuName,
m.id as menuId,
(select u.userName from recursiveTest.User as u
where m.ownerId = u.userId) as Owner,
r.name
from recursiveTest.Menu as m
INNER JOIN cte as c ON c.menuId = m.parentId
LEFT OUTER JOIN recursiveTest.Report as r ON m.id = r.menuId
)
SELECT * FROM cte;
결과
이 쿼리를 실행시키면 다음과 같습니다.
Anchor Member를 기준으로 parentId가 -1인 메뉴를 no으로 분리한 root 메뉴에 딸린 메뉴가 표시되며, Recursive Member을 통해서 root 메뉴에 포함된 하위 메뉴와 리포트, 사용자 정보를 한 번에 확인할 수 있습니다.
결론
이 글을 통해서 업무에서 사용하면 학습했던 재귀 쿼리에 대해 정리해보았습니다.
위에 언급했던 타입 변환 함수 외에도 Recursive Member 쿼리 작성 시 OUTER JOIN이 안되는 점 등 다른 에러들을 맞이했었는데, 이 부분에 대해서도 추가적으로 학습하고 정리해서 공유드리도록 하겠습니다.
재귀 쿼리가 필요하신 분들에게 이 글이 도움이 되길 바라며, 글을 마치겠습니다 :D