PostgreSQL을 처음 배우는 사람들이 가장 난해하게 느끼는 것 중 하나가 바로 계층적 쿼리(Hierarchical Query)입니다. 데이터베이스에서 부모-자식 관계를 표현할 때 계층 구조가 자주 사용되는데, 이를 효율적으로 조회하려면 "재귀적 CTE" 또는 "WITH RECURSIVE" 쿼리를 알아야 합니다. 이번 글에서는 계층 쿼리의 기초 개념부터 실전 활용까지 쉽고 창의적인 방식으로 풀어보겠습니다.
1. 계층적 쿼리란?
계층적 쿼리는 트리 구조의 데이터를 조회하는 방식입니다. 회사 조직도나 댓글 구조처럼 부모-자식 관계가 있는 데이터에서, 상위 레벨부터 하위 레벨까지의 데이터 흐름을 파악하고 싶을 때 사용됩니다.
예시:
- 회사 조직도: CEO -> 팀장 -> 사원
- 게시판 댓글: 원 댓글 -> 대댓글 -> 추가 대댓글
2. WITH RECURSIVE란?
PostgreSQL에서 계층적 쿼리를 작성할 때 가장 중요한 도구는 바로 WITH RECURSIVE입니다. WITH RECURSIVE는 자기 자신을 호출하여 데이터를 재귀적으로 조회할 수 있게 해줍니다. 이 재귀적 쿼리를 사용하면 부모 노드부터 시작해 자식 노드까지 데이터를 나열할 수 있습니다.
3. 기본 구조
계층적 쿼리를 이해하기 위한 기초 쿼리 구조는 다음과 같습니다:
WITH RECURSIVE 조직도 AS (
-- Anchor 쿼리: 시작 지점 (CEO)
SELECT id, name, 직급, 상사_id
FROM 직원
WHERE 상사_id IS NULL -- 상사_id가 NULL인 사람은 CEO입니다.
UNION ALL
-- Recursive 쿼리: 다음 레벨 탐색
SELECT e.id, e.name, e.직급, e.상사_id
FROM 직원 e
INNER JOIN 조직도 o ON e.상사_id = o.id
)
SELECT * FROM 조직도;- Anchor 쿼리: 트리의 루트(예: CEO)를 찾는 부분입니다.
- Recursive 쿼리: 루트 노드로부터 하위 노드(자식들)를 재귀적으로 찾는 부분입니다.
UNION ALL: 계층적으로 데이터를 결합합니다.
4. 계층적 쿼리 단계별 설명
1단계: 기본 테이블 준비
먼저 회사 조직도를 테이블로 표현해 봅시다. 테이블은 아래와 같은 구조를 가질 것입니다.
CREATE TABLE 직원 (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
직급 VARCHAR(50),
상사_id INTEGER REFERENCES 직원(id)
);2단계: 데이터 삽입
INSERT INTO 직원 (name, 직급, 상사_id) VALUES
('Alice', 'CEO', NULL),
('Bob', '팀장', 1),
('Charlie', '팀원', 2),
('David', '팀원', 2),
('Eve', '팀장', 1),
('Frank', '팀원', 5);여기서 Alice는 CEO이고, Bob과 Eve는 Alice의 팀장입니다. 그 아래에는 팀원들이 배치되어 있습니다.
3단계: WITH RECURSIVE로 계층 구조 조회
이제 이 데이터를 바탕으로 조직도를 조회해보겠습니다. 계층적 쿼리를 이용해 상사로부터 팀원들까지 모두 조회할 수 있습니다.
WITH RECURSIVE 조직도 AS (
-- Anchor 쿼리: CEO부터 시작
SELECT id, name, 직급, 상사_id
FROM 직원
WHERE 상사_id IS NULL -- 상사_id가 NULL인 사람은 CEO입니다.
UNION ALL
-- Recursive 쿼리: 팀장 아래의 팀원들을 찾아냅니다.
SELECT e.id, e.name, e.직급, e.상사_id
FROM 직원 e
INNER JOIN 조직도 o ON e.상사_id = o.id
)
SELECT * FROM 조직도;결과는 다음과 같이 출력될 것입니다:
| id | name | 직급 | 상사_id |
|---|---|---|---|
| 1 | Alice | CEO | NULL |
| 2 | Bob | 팀장 | 1 |
| 3 | Charlie | 팀원 | 2 |
| 4 | David | 팀원 | 2 |
| 5 | Eve | 팀장 | 1 |
| 6 | Frank | 팀원 | 5 |
Alice에서 시작해서 Bob, Charlie, David, Eve, Frank까지 순서대로 나오게 됩니다. 각 직급과 상사 관계를 통해 조직 구조를 한눈에 파악할 수 있습니다.
5. 실전 예제: 댓글 계층 구조
이번에는 댓글 구조를 조회하는 쿼리도 작성해보겠습니다. 게시판에서 댓글과 대댓글을 계층적으로 표현하고 싶다면, 이 구조를 사용합니다.
1단계: 테이블 생성
CREATE TABLE 댓글 (
id SERIAL PRIMARY KEY,
내용 TEXT,
부모_댓글_id INTEGER REFERENCES 댓글(id)
);2단계: 데이터 삽입
INSERT INTO 댓글 (내용, 부모_댓글_id) VALUES
('이 글 정말 유익하네요!', NULL),
('동의합니다!', 1),
('저도 동감이에요.', 1),
('그건 좀 아닌 것 같아요.', 2),
('재미있게 읽었습니다.', NULL);3단계: 댓글 계층 구조 조회
WITH RECURSIVE 댓글_트리 AS (
-- 루트 댓글 찾기
SELECT id, 내용, 부모_댓글_id
FROM 댓글
WHERE 부모_댓글_id IS NULL
UNION ALL
-- 대댓글까지 모두 찾기
SELECT c.id, c.내용, c.부모_댓글_id
FROM 댓글 c
INNER JOIN 댓글_트리 ct ON c.부모_댓글_id = ct.id
)
SELECT * FROM 댓글_트리;이 쿼리를 실행하면 댓글이 계층적으로 나열됩니다:
| id | 내용 | 부모_댓글_id |
|---|---|---|
| 1 | 이 글 정말 유익하네요! | NULL |
| 2 | 동의합니다! | 1 |
| 3 | 저도 동감이에요. | 1 |
| 4 | 그건 좀 아닌 것 같아요. | 2 |
| 5 | 재미있게 읽었습니다. | NULL |
6. 계층적 쿼리 활용 팁
계층적 쿼리를 잘 활용하면 조직도, 댓글 시스템, 카테고리 구조 등 다양한 트리 기반 데이터를 쉽게 처리할 수 있습니다. 여기서 몇 가지 팁을 추가하자면:
- 레벨 계산하기: 레벨을 함께 계산하고 싶다면,
WITH RECURSIVE쿼리에서 레벨을 카운팅하는 필드를 추가할 수 있습니다. WITH RECURSIVE 조직도 AS ( SELECT id, name, 직급, 상사_id, 1 AS 레벨 FROM 직원 WHERE 상사_id IS NULL UNION ALL SELECT e.id, e.name, e.직급, e.상사_id, o.레벨 + 1 FROM 직원 e INNER JOIN 조직도 o ON e.상사_id = o.id ) SELECT * FROM 조직도;- 정렬: 조회 결과를 계층적으로 정렬하고 싶다면, 추가로 정렬 필드를 사용하여 쿼리를 더 깔끔하게 정리할 수 있습니다.
마무리
PostgreSQL에서 계층적 쿼리는 처음에는 어렵게 느껴질 수 있지만, 개념을 이해하고 몇 가지 실전 예제를 풀어가다 보면 그리 복잡하지 않다는 것을 알 수 있습니다. 이 글에서 소개한 단계별 가이드를 따라가며 직접 데이터를 다뤄보면서, 계층 구조 데이터를 쉽게 조회해보세요!