[PostgreSQL] 재귀/계층 쿼리 Recursive

PostgreSQL에서 WITH RECURSIVE 구문을 사용하여 java의 for문의 반복문처럼 사용이 가능하다.

재귀적 쿼리인데 이는 보통 테이블 데이터가 계층형일때 많이 사용된다.

with recursive 뷰명 as(
    초기 SQL
 
    union all(or union)
 
    반복할 SQL(+반복을 멈출 where절 포함)
 
)select * from 뷰명;

원리

1. 초기 SQL을 실행하면 실행한 결과셋은 recursive문을 선언할때 기재한 뷰에 담긴다.

2. 반복할 SQL의 from 절에 뷰명을 이용해서 처리하거나 한다. 이는 상황에 따라 다르지만 주로 뷰명을 from절에 두고 반복문을 돌리는 것이 일반적이다.

3. union 혹은 union all 연산을 한다.(경우에따라 다름 마이너스도 가능)

4. 반복할 SQL에서 단하나의 레코드가 나오지 않을때 recursive문을 탈출한다.

5. recursive문을 탈출하였으면 뷰명에 연산된 결과셋이 다시 담기게되고 뷰처럼 조회할 수 있다.

활용

WITH RECURSIVE random_values(random_value, row_num) AS (
    SELECT random() AS random_value, 1 AS row_num
    UNION ALL
    SELECT random(), row_num + 1
    FROM random_values
    WHERE row_num < 10
)
SELECT * FROM random_values;

랜덤값을 총 10회 출력하는 쿼리문을 작성해보았다.
이에대한 결과값은 아래와 같다.

그럼 단순히 숫자를 1씩 늘려가면 반복문을 짜기 보다는 이를 조금 더 활용해서 계층형 구조의 데이터에서 어떻게 활용을 하는지 보자.

먼저 테스트 하기위해 테이블에 아래의 데이터를 insert 했다고 가정하자.

create table recursive_test(group_id numeric, parent_id numeric);

insert into recursive_test values(0, null);
insert into recursive_test values(1, 0);
insert into recursive_test values(2, 1);
insert into recursive_test values(3, 1);
insert into recursive_test values(4, 1);
insert into recursive_test values(5, 2);
insert into recursive_test values(6, 2);
insert into recursive_test values(7, 2);
insert into recursive_test values(8, 5);
insert into recursive_test values(9, 5);
insert into recursive_test values(10, 3);
insert into recursive_test values(11, 3);
insert into recursive_test values(12, 3);
insert into recursive_test values(13, 9);
insert into recursive_test values(14, 9);

또한 계층형 쿼리를 짜기 위해서 기본적인 문법은 아래와 같다.

with recursive Alias_VIEW([그룹컬럼],[부모컬럼]) as (
	select 그룹컬럼, 부모컬럼
	from 그룹테이블
	where 그룹컬럼시작조건
	
	union all
	
	select 그룹컬럼, 부모컬럼
	from 그룹테이블, Alias_VIEW
	where 그룹번호 = 부모 번호 < 역순 >
) select 그룹컬럼, 부모컬럼 from Alias_VIEW;

여기서 union all 기준으로 앞의 쿼리는 계층의 시작의 기준점으로 보면된다
최종적으로는 데이터의 맨 앞에 위치하게 된다

그렇다면 group_id 가 13인 데이터의 부모를 조회하고 또 그 데이터의 부모를 반복해서 조회하는 쿼리를 짜는 쿼리는 아래와 같다.

with recursive childlist(group_id,parent_id, level) as (
		select group_id, parent_id, 0	
		from recursive_test rt
		where group_id = '13'
		union all 
		select c.group_id,c.parent_id, p.level+1 
		from recursive_test c, childlist p
		where c.group_id = p.parent_id
	) select *from childlist;

• 13의 부모인 9,
• 9 의 부모인 5,
• 5 의 부모인 2,
• 2 의 부모인 1,
• 1 의 부모인 0

결국 parent_id가 9 5 2 1 0 가 조회된다.

그렇다면 자식을 반복으로 조회하는 쿼리는 아래와 같다.

with recursive childlist(group_id,parent_id, level) as (
	select group_id, parent_id, 0	
	from recursive_test rt
	where group_id = '5'
	union all 
	select c.group_id,c.parent_id, p.level+1 
	from recursive_test c, childlist p
	where c.parent_id = p.group_id
) select *from childlist;

• 5 의 자신의 5,
• 5 의 자식인 8,
• 5 의 자식인 9,
• 9 의 자식인 13,
• 9 의 자식인 14

즉, 자식인 group_id가 5 8 9 13 14 조회된다.