계층 구조 작업을 위한 재귀 CTE 예제

상상해봐: 네가 수천 개의 상품을 가진 온라인 쇼핑몰을 운영하는데, 상품들이 카테고리, 서브카테고리, 서브-서브카테고리로 깔끔하게 정리되어 있어. 사이트에서는 멋진 드롭다운 메뉴로 보이지만, DB에서는 진짜 골치 아픈 구조야. "전자제품 → 스마트폰 → 액세서리" 전체 트리를 한 번에 어떻게 뽑지? 각 카테고리의 깊이는 어떻게 세지? 일반적인 JOIN만으로는 힘들어 — 재귀가 필요해!

재귀 CTE로 상품 카테고리 구조 만들기

관계형 DB에서 자주 나오는 고전적인 문제 중 하나가 바로 계층 구조 다루기야. 예를 들어, 상품 카테고리 트리가 있다고 해보자: 메인 카테고리, 서브카테고리, 서브-서브카테고리 등등. 예시:

전자제품
  └── 스마트폰
      └── 액세서리
  └── 노트북
      └── 게이머용
  └── 사진과 비디오

이 구조는 쇼핑몰 UI에서는 쉽게 표현되지만, DB에 어떻게 저장하고 꺼낼까? 여기서 재귀 CTE가 등장하지!

카테고리 테이블 만들기

먼저 categories 테이블을 만들어서 상품 카테고리 정보를 저장하자:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- 카테고리의 유니크 아이디
    category_name TEXT NOT NULL,          -- 카테고리 이름
    parent_category_id INT                -- 부모 카테고리 (메인 카테고리는 NULL)
);

테이블에 넣을 데이터 예시:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('전자제품', NULL),
    ('스마트폰', 1),
    ('액세서리', 2),
    ('노트북', 1),
    ('게이머용', 4),
    ('사진과 비디오', 1);

여기서 무슨 일이 일어나냐면:

• 전자제품 — 메인 카테고리야 (부모 없음, parent_category_id = NULL).
• 스마트폰은 전자제품 안에 있어.
• 액세서리는 스마트폰의 하위야.
• 나머지도 마찬가지로 연결돼.

지금 categories 테이블의 데이터 구조는 이렇게 생겼어:

재귀 CTE로 카테고리 트리 만들기

이제 각 카테고리의 깊이(level)까지 포함해서 전체 계층 구조를 뽑고 싶어. 재귀 CTE를 써보자.

WITH RECURSIVE category_tree AS (
    -- 기본 쿼리: 루트 카테고리만 선택 (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- 첫 번째 레벨
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- 재귀 쿼리: 각 카테고리의 서브카테고리 찾기
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- 레벨 증가
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- 최종 쿼리: CTE에서 결과 뽑기
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

결과:

여기서 무슨 일이 일어나냐면?

1. 먼저 기본 쿼리 (SELECT … FROM categories WHERE parent_category_id IS NULL)가 메인 카테고리를 뽑아. 여기서는 전자제품만 depth = 1이야.
2. 그 다음 재귀 쿼리가 INNER JOIN으로 서브카테고리를 추가하면서 depth + 1로 레벨을 올려.
3. 이 과정을 모든 레벨의 서브카테고리를 다 찾을 때까지 반복해.

꿀팁 & 실전 개선

기본 예제도 좋지만, 실제 프로젝트에서는 더 많은 게 필요하지. 예를 들어, 사이트에 breadcrumb(빵조각) 만들거나, 매니저한테 어떤 카테고리에 서브카테고리가 제일 많은지 보여주고 싶을 수도 있어. 몇 가지 실전 개선 예제를 보자.

1. 카테고리 전체 경로(full path) 추가

가끔은 전자제품 > 스마트폰 > 액세서리처럼 카테고리의 전체 경로를 보여주는 게 좋아. 문자열 합치기를 써서 만들 수 있어:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- 문자열 합치기
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

결과:

이제 각 카테고리마다 계층 구조가 한눈에 보여.

2. 서브카테고리 개수 세기

각 카테고리에 서브카테고리가 몇 개 있는지 알고 싶다면?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

결과:

테이블을 보면 전자제품에는 3개의 서브카테고리(스마트폰, 노트북, 사진과 비디오)가 있고, 스마트폰과 노트북에는 각각 1개씩 있어.

재귀 CTE 쓸 때 주의할 점 & 흔한 실수

무한 재귀: 만약 데이터에 사이클(예: 카테고리가 자기 자신을 부모로 가리킴)이 있으면 쿼리가 무한 루프에 빠질 수 있어. WHERE depth < N이나 LIMIT으로 깊이를 제한해줘.

성능 최적화: 재귀 CTE는 데이터가 많으면 느릴 수 있어. parent_category_id에 인덱스를 걸어두면 빨라져.

UNION 대신 UNION ALL 실수: 재귀 CTE에서는 항상 UNION ALL을 써야 해. UNION을 쓰면 PostgreSQL이 중복을 제거하려고 해서 쿼리가 느려져.

이 예제처럼 재귀 CTE는 계층 구조를 다루는 데 진짜 유용해. DB에서 계층 구조를 뽑는 스킬은 실전 프로젝트에서 자주 써먹을 수 있어. 예를 들어, 사이트 메뉴 만들기, 조직도 분석, 그래프 데이터 다루기 등등. 이제 어떤 난이도의 문제도 해결할 준비가 된 거야!