데이터베이스/Postgresql
[Postgres] 계층형 쿼리에 대한 정리 ( RECURSIVE )
모찌바라기
2022. 5. 9. 12:51
728x90
반응형
오늘은 카테고리와 같은 기능을 구현할 때 쓰이는 계층형 쿼리에 대해 알아보자.
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함. 필요한 컬럼들을 가져온다.
프로젝트를 하면서 계층형 쿼리를 쓰는 곳이 있긴 했는데, 내가 직접 계층형 쿼리를 써 본적은 없어서
이렇게 정리해본다.
다음에 계층형 쿼리를 쓸 기회가 있으면 한번 써봐야겠다.
728x90
반응형