[Postgres] 계층형 쿼리에 대한 정리 ( RECURSIVE )

 

 

 

 

오늘은 카테고리와 같은 기능을 구현할 때 쓰이는 계층형 쿼리에 대해 알아보자.

 

Postgresql에서는 데이터의 트리구조(계층)를 표현하기 위해서 RECURSIVE 키워드를 통해

재귀적인 쿼리를 사용한다고 한다.

 

여기서 재귀란 어떠한 것을 정의할 때 자기자신을 참조한다는 의미이다. 중요한 건 아니니 

바로 계층형 쿼리를 통해 트리구조를 만들어보자. 

 

샘플 테이블 생성

CREATE TABLE RECURSIVE_BOOK(
    BOOK_ID INTEGER NOT NULL,
    PARENT_ID INTEGER,
    BOOK_NAME CHARACTER VARYING(20) NOT NULL,
    BOOK_QTY INTEGER,
	CONSTRAINT BOOK_KEY PRIMARY KEY (BOOK_ID));

 

샘플 데이터 적재

INSERT INTO RECURSIVE_BOOK VALUES (101, null, '도서', 1);
INSERT INTO RECURSIVE_BOOK VALUES (102, 101, '과학책', 1);
INSERT INTO RECURSIVE_BOOK VALUES (103, 101, '역사책', 1);
INSERT INTO RECURSIVE_BOOK VALUES (104, 101, '잡지', 1);
INSERT INTO RECURSIVE_BOOK VALUES (105, 102, '달나라_여행', 1);
INSERT INTO RECURSIVE_BOOK VALUES (106, 102, '내셔널지오그래픽_동물사전', 1);
INSERT INTO RECURSIVE_BOOK VALUES (107, 102, '블랙홀은_존재하는가', 1);
INSERT INTO RECURSIVE_BOOK VALUES (108, 106, '독화살개구리의_생존', 1);
INSERT INTO RECURSIVE_BOOK VALUES (109, 106, '오리너구리의_비밀', 1);
INSERT INTO RECURSIVE_BOOK VALUES (110, 104, '90년대_오렌지족_패션', 1);
INSERT INTO RECURSIVE_BOOK VALUES (111, 103, '6.25전쟁의_진실', 1);

 

테이블 생성/적재 결과

 

계층형 쿼리문

WITH RECURSIVE search_book(
	BOOK_ID, 
 	PARENT_ID, 
 	BOOK_NAME, 
 	BOOK_QTY,
   	LEVEL, 
   	PATH, 
   	CYCLE) AS (
   		SELECT  book.BOOK_ID, book.PARENT_ID, book.BOOK_NAME, book.BOOK_QTY, 0, ARRAY[book.BOOK_ID], false
			FROM RECURSIVE_BOOK book
			WHERE book.PARENT_ID IS null UNION ALL
		SELECT book.BOOK_ID, book.PARENT_ID, book.BOOK_NAME, book.BOOK_QTY, LEVEL + 1, PATH || book.BOOK_ID, book.BOOK_ID = ANY(PATH)
			FROM RECURSIVE_BOOK book, search_book sb
			WHERE book.PARENT_ID = sb.BOOK_ID AND NOT CYCLE)
SELECT BOOK_ID, PARENT_ID, lpad('', LEVEL) || BOOK_NAME, BOOK_QTY, LEVEL, PATH
	FROM search_book ORDER BY path;

 

결과

 

계층형 쿼리문을 돌리면 이렇게 결과가 나온다. 

여기서 잘 보아야 할 게, level과 path컬럼이다. 자신의 레벨과 자신을 포함한 자신의 부모, 또 그 부모의 부모를

path로 가져온다.

 

간략히 설명을 하자면

 

(1) 데이터의 ROOT를 구함 ( LEVEL, PATH등의 데이터 추가 )

 

(2) PARENT_ID가 NULL인, 최상위 부모를 가져온다.

 

(3) 위에서 가져온 최상위 부모의 SELECT문 + 모든 컬럼들을 재귀하여 조건에 맞는 SELECT문을 합쳐준다.

 

(4) 재귀가 전부 돌고 최종적으로 SELECT함. 필요한 컬럼들을 가져온다.

 

프로젝트를 하면서 계층형 쿼리를 쓰는 곳이 있긴 했는데, 내가 직접 계층형 쿼리를 써 본적은 없어서

이렇게 정리해본다. 

 

다음에 계층형 쿼리를 쓸 기회가 있으면 한번 써봐야겠다.

 

참조블로그

[POSTGRESQL] 계층형 쿼리, RECURSIVE