[postgresql] postgresql 재귀쿼리(계층 쿼리) 사용법

이번 게시글에서는 postgresql에서의 재귀쿼리 사용법에 대해 정리합니다.

 

1. 재귀쿼리의 구조

WITH RECURSIVE recur_data as(

    부모 쿼리
    
    UNION
    
    자식 쿼리
)
SELECT * FROM recur_data

(1). with recursive [view 이름] as

    - with recursive구문을 이용하여 view를 만들어주는 개념으로 시작합니다.

 

(2). 부모쿼리 작성

    - 부모가 될 쿼리를 작성합니다.

 

(3). UNION, 자식쿼리 작성

    - UNION 아래의 구문은 실행결과가 recur_data에 저장되며 데이터가 나오지 않을때까지 반복됩니다.

 

간단한 예제를 만들어보겠습니다.

 

 

2. 예제

family 테이블을 만들어봤습니다.(up_key는 부모의 key를 의미합니다.)

Jame의 부모는 Ellise, Ellise의 부모는 John, John의 부모는 Tom 입니다.

각 레코드의 최상위 부모이름과, 최상위 부모의 key, 어떤 계층으로 구성됐는지를 확인할 수 있도록

recursive쿼리를 사용하여 데이터를 뽑아보겠습니다.

 

with RECURSIVE recur_data AS
(
	select 
		*,
		"key" as top_key,
		"name" as top_name,
		'' || key as "keys"
	from family
	where up_key = ''
	
	UNION
	
	select 
		b.*,
		a.top_key,
		a.top_name,
		a.keys || ',' || b.key as keys
	from recur_data a
	inner join family b on a.key = b.up_key
)
select * from recur_data

 

 

3. 결과

name이 James인 레코드의 경우

 

가장 상위조상의 key와 이름, James 까지의 조상들을 모두 확인할 수 있게 되었습니다.