재귀쿼리

회사에는 여러부서가 있다. 그리고 그안에 또 팀에 있고...

이들을 계층형으로 보고싶을때, 어느팀이 어느 부서에 속해있는지를 알고싶은 경우가 있다. 또한 각 최상위 부서별로 무언가에 대한 계산을 해야할때, 최상위부서에 속한 부서들을 모두 파악할 필요가 있다. 

이때 재귀 쿼리를 사용하면 계층형태로 부서를 나타낼수있고, 따라서 최상위부서도 파악할수 있다.

재귀쿼리란?

CTE(Common Table Expression : 공통 테이블 식)를 사용하면 자기 자신을 참조하는 재귀쿼리를 만들수 있다. 재귀적 CTE는 최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위집합을 반환하는 CTE다.

우선, CTE란, SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷하다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있다는점에서 파생 테이블과는 다르다

※ 파생테이블 

- 쿼리에서 테이블 원본으로 사용되는 결과 집합입니다. 다이어그램 창에서 파생 테이블을 쿼리에 추가할 수 있다.

- FROM, WHERE절과 같이 쿼리를 통해서 리턴된 테이블이다.

재귀적 CTE의 구조

1. 루틴의 호출

재귀적 CTE의 첫 번째 호출은 하나 이상의 쿼리 정의로 구성된다. CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 한다. CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주된다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 한다.

2. 루틴의 재귀 호출

재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 쿼리문이 포함된다. 이러한 쿼리 정의를 재귀 멤버라고 한다.

3. 종료 확인

종료 확인은 암시적으로 수행된다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지된다.

실행체계

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.

2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.

3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.

4. 빈 집합이 반환될 때까지 3단계를 반복한다.

5. 결과 집합을 반환한다. 이것은 T0에서 Tn까지의 UNION ALL한 결과 값이다.

예제

예제에서 사용할 TREE_TABLE은 

이다.

이 TREE_TABLE을 재귀쿼리를 활용하여 계층형구조로 바꾸어 볼것이다.

그에 해당하는 쿼리는 다음과 같다.

WITH tree_query AS (
--Anchor member definition
 SELECT id , parent_id , name , 
        convert(varchar(255), id) sort ,
   FROM tree_table WHERE parent_id = 1 
 
UNION ALL 
 
--Recursive member definition
SELECT B.id , B.parent_id , B.name , 
        convert(varchar(255), convert(nvarchar,C.sort) + ' > ' + convert(varchar(255), B.id)) sort,
  FROM tree_table B, tree_query C
 WHERE B.parent_id = C.id 
 
)
 
 SELECT id, parent_id, name, SORT
   FROM tree_query 
  ORDER BY SORT

핵심은 재귀멤버를 SELECT하는 쿼리에서 현재 TREE_TABLE에 가리키는 행의 부모 컬럼이 TREE_QUERY에 존재하는 ID와 같은지를 확인하여 ROW를 COLLECTION하는 것이다.

자식필드에서 부모의 코드값과 CTE에서 자신의 코드값이 같은 행을 찾아야 무한루프에 빠지지 않는다. 

그리고 WITH절 앞에 ; 을 붙이지 않으면 프로시저를 실행할때 오류가 나기때문에 반드시 ; 을 붙혀주도록하자.

재귀공통테이블식의 재귀부분에는 외부조인을 사용할 수 없다. 이너 조인은 되네? NULL때문에 안되는건가?

SELECT시에 CONVERT(VARCHAR(255),A)를 해주는 이유는 앵커멤버와 재귀맴버의 유형을 일치시켜주기 위해서이다. 만에 하나라도 다르면 에러가 발생하니까...

앵커맴버가 최상위 그룹의 행이므로, 재귀맴버들을 각각의 앵커맴버의 자식 행이된다. 따라서 최상위부서를 구하는 컬럼을 명시해두면 각부서의 최상위 부서를 알 수 있다.

결과

참조 : https://technet.microsoft.com/ko-kr/library/ms186243(v=sql.105).aspx