SQL 공통 테이블 식 및 재귀 쿼리를 사용한 계층형 데이터 탐색

소개

데이터 관리 영역에서 우리는 종종 데이터 포인트가 격리된 것이 아니라 부모-자식 관계로 본질적으로 연결된 시나리오를 접하게 됩니다. 게시글에 댓글이 달리고, 그 댓글에 다시 답글이 달리는 포럼을 생각해보세요. 깊은 대화 스레드를 형성합니다. 또는 회사의 조직 구조를 고려해보세요. 직원들은 관리자에게 보고하고, 관리자들은 다시 상위 경영진에게 보고합니다. 이것들은 계층형 데이터의 고전적인 예입니다. 전통적으로 이러한 구조를 관리하고 쿼링하는 것은 번거로울 수 있으며, 종종 여러 애플리케이션 수준 쿼리나 복잡한 절차적 로직이 필요했습니다. 이러한 복잡성은 비효율적인 데이터 검색, 애플리케이션 로드 증가, 유지 관리하기 어려운 코드로 이어질 수 있습니다. 다행히도 최신 SQL은 강력하고 우아한 솔루션을 제공합니다. 바로 **공통 테이블 식(CTE)**이며, 특히 재귀 쿼리와 함께 사용할 때 더욱 그렇습니다. 이 글에서는 CTE와 재귀가 복잡한 관계를 탐색, 분석 및 조작하는 강력하고 효율적인 수단을 제공하면서 데이터베이스 내에서 직접 계층형 데이터를 처리하는 방식을 어떻게 혁신하는지 살펴봅니다.

핵심 개념

메커니즘을 자세히 살펴보기 전에 논의의 중심이 되는 주요 용어를 간략하게 정의해 보겠습니다.

• 계층형 데이터: 각 항목(루트 제외)이 부모를 갖고, 부모는 여러 자식을 가질 수 있는 트리와 같은 구조로 구성된 데이터입니다. 예로는 파일 시스템, 조직도, 스레드 댓글, 제품 카테고리 등이 있습니다.
• 공통 테이블 식(CTE): 단일 SQL 문(SELECT, INSERT, UPDATE 또는 DELETE) 내에서 참조할 수 있는 임시 명명된 결과 집합입니다. CTE는 복잡한 쿼리를 논리적이고 관리 가능한 단계로 분해하여 가독성과 유지 관리성을 향상시킵니다. WITH 절을 사용하여 정의됩니다.
• 재귀 CTE: 정의 내에서 자체를 참조하는 특수한 유형의 CTE입니다. 이러한 자체 참조 기능을 통해 기본 조건이 충족될 때까지 계층형 데이터를 반복하여 한 번에 한 "레벨"씩 처리할 수 있습니다. 재귀 CTE는 두 가지 주요 부분으로 구성됩니다.
  • 앵커 멤버: 재귀의 기본 행 집합을 설정하는 초기 쿼리입니다. 이것이 계층의 시작점입니다.
  • 재귀 멤버: CTE 자체를 참조하고 UNION ALL 연산자를 사용하여 앵커 멤버 또는 이전 재귀 멤버의 결과를 기반으로 구축하는 쿼리입니다. 현재 레벨을 기준으로 다음 레벨을 찾는 방법을 정의합니다.
• 종료 조건: 재귀 CTE에서 무한 루프를 방지하는 데 중요합니다. 재귀 멤버는 더 이상 새 행을 반환하지 않을 때까지 계속 실행되어 계층의 "바닥" 또는 "끝"에 효과적으로 도달합니다.

구현 및 적용

실용적인 예를 통해 재귀 CTE의 강력함을 설명해 보겠습니다.

예제 1: 댓글 트리

각 댓글이 parent_id를 가질 수 있는 간단한 comments 테이블을 상상해 보세요.

CREATE TABLE comments (
    id INT PRIMARY KEY,
    parent_id INT,
    author VARCHAR(50),
    content TEXT,
    comment_date DATETIME
);

INSERT INTO comments (id, parent_id, author, content, comment_date) VALUES
(1, NULL, 'Alice', 'Great post!', '2023-01-01 10:00:00'),
(2, 1, 'Bob', 'I agree with Alice!', '2023-01-01 10:15:00'),
(3, 1, 'Charlie', 'Interesting perspective.', '2023-01-01 10:30:00'),
(4, 2, 'David', 'Me too!', '2023-01-01 10:45:00'),
(5, NULL, 'Eve', 'Another topic entirely.', '2023-01-02 11:00:00'),
(6, 3, 'Frank', 'Can you elaborate?', '2023-01-02 11:15:00'),
(7, 4, 'Grace', 'Haha!', '2023-01-02 11:30:00');

특정 부모부터 전체 댓글 스레드를 검색하려면 재귀 CTE를 사용할 수 있습니다.

WITH RECURSIVE CommentThread AS (
    -- 앵커 멤버: 초기 댓글 선택
    SELECT
        id,
        parent_id,
        author,
        content,
        comment_date,
        0 AS level,
        CAST(id AS VARCHAR(MAX)) AS path -- 순서 지정/계층 식별용
    FROM comments
    WHERE parent_id IS NULL -- 최상위 댓글부터 시작

    UNION ALL

    -- 재귀 멤버: 조인을 통해 자식 찾기
    SELECT
        c.id,
        c.parent_id,
        c.author,
        c.content,
        c.comment_date,
        ct.level + 1 AS level,
        CAST(ct.path + '->' + CAST(c.id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS path
    FROM comments c
    INNER JOIN CommentThread ct ON c.parent_id = ct.id
)
SELECT
    id,
    parent_id,
    author,
    content,
    level,
    path
FROM CommentThread
ORDER BY path;

이 쿼리는 먼저 모든 최상위 댓글(parent_id가 NULL인)을 선택합니다. 그런 다음 재귀 부분에서는 comments 테이블을 CommentThread CTE 자체와 반복적으로 조인하여 이전 댓글의 자식을 찾습니다. level 열은 계층 구조에서 각 댓글의 깊이를 추적하고 path는 스레드를 올바르게 시각화하고 정렬하는 데 도움이 됩니다.

예제 2: 조직도

각 직원이 manager_id를 갖는 employees 테이블을 고려해 보겠습니다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    position VARCHAR(50)
);

INSERT INTO employees (id, name, manager_id, position) VALUES
(1, 'CEO Alpha', NULL, 'CEO'),
(2, 'Manager Beta', 1, 'CTO'),
(3, 'Employee Charlie', 2, 'Senior Developer'),
(4, 'Employee David', 2, 'Junior Developer'),
(5, 'Manager Eve', 1, 'CFO'),
(6, 'Employee Frank', 5, 'Accountant'),
(7, 'Employee Grace', 5, 'Auditor');

특정 관리자(예: CEO Alpha, id=1)의 모든 부하 직원을 검색하려면 다음과 같이 합니다.

WITH RECURSIVE OrganizationChart AS (
    -- 앵커 멤버: 지정된 관리자로 시작
    SELECT
        id,
        name,
        manager_id,
        position,
        0 AS level
    FROM employees
    WHERE id = 1 -- CEO Alpha로 시작

    UNION ALL

    -- 재귀 멤버: 현재 레벨의 직속 보고서 찾기
    SELECT
        e.id,
        e.name,
        e.manager_id,
        e.position,
        oc.level + 1 AS level
    FROM employees e
    INNER JOIN OrganizationChart oc ON e.manager_id = oc.id
)
SELECT
    id,
    name,
    manager_id,
    position,
    level
FROM OrganizationChart
ORDER BY level, name;

이 CTE는 CEO로 시작한 다음 manager_id가 이전 반복에서 찾은 직원의 id와 일치하는 모든 직원을 재귀적으로 찾아 CEO 아래의 전체 경영진 계층 구조를 효과적으로 구축합니다.

주요 장점 및 사용 사례:

• 가독성 및 유지 관리성: CTE는 복잡한 계층 쿼리를 이해하기 쉬운 부분으로 나눕니다.
• 성능: 여러 번의 데이터베이스 왕복이나 애플리케이션 수준의 재귀 루프보다 더 효율적인 경우가 많습니다. 데이터베이스는 집합 기반 연산에 최적화되어 있습니다.
• 유연성: 다양한 계층 구조(조상 찾기를 위한 상향식 탐색, 하위 찾기를 위한 하향식 탐색, 전체 하위 트리)에 쉽게 적응할 수 있습니다.
• 일반적인 사용 사례:
  • 부품 폭발/자재 명세서: 어셈블리를 구성 요소로 분해합니다.
  • 카테고리 트리: 제품 카테고리 및 하위 카테고리를 탐색합니다.
  • 탐색 메뉴: 웹사이트의 동적 계층 메뉴를 구축합니다.
  • 족보 트리: 조상 또는 후손을 추적합니다.

결론

계층형 데이터를 관리하는 것은 데이터 기반 애플리케이션에서 보편적인 과제입니다. SQL 공통 테이블 식, 특히 재귀 쿼리와 결합된 것은 이 과제를 해결하는 강력하고 우아하며 매우 효율적인 솔루션을 제공합니다. 재귀 CTE를 사용하면 데이터베이스 내에서 직접 트리와 같은 구조를 탐색하고 조작할 수 있으므로 복잡한 쿼리가 단순화되고 성능이 향상되며 SQL 코드의 유지 관리성이 크게 향상됩니다. 재귀 CTE를 채택하는 것은 고급 SQL을 마스터하고 복잡한 데이터 관계를 효과적으로 처리하기 위한 기본적인 단계입니다. 구조화되고 상호 연결된 데이터를 다루는 모든 사람에게 필수적인 도구입니다.