0Pricing
SQL Academy · 강의

재귀 CTE의 작동 원리

기본 사례와 재귀 단계로 구성됩니다.

재귀 CTE의 작동 원리은(는) CoddyKit의 무료 SQL Academy 강의입니다. 이것은 4개 중 1번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

재귀 CTE란 무엇인가

재귀 CTE는 자기 자신을 참조하는 공통 테이블 식입니다. 조건이 충족될 때까지 한 단계를 반복하는 질의를 작성할 수 있으며, 반복문과 비슷하지만 순수한 에스큐엘로 표현됩니다.

재귀 CTE는 WITH RECURSIVE 키워드로 정의하며, 조직도, 폴더 트리, 자재 명세서 구조와 같은 계층형 또는 그래프 형태의 데이터를 탐색하는 데 적합합니다.

두 부분으로 이루어진 구조

모든 재귀 CTE는 UNION ALL로 구분된 정확히 두 부분으로 구성됩니다.

1. 기본 사례 — 시작 행을 반환하는 비재귀 SELECT입니다.

2. 재귀 단계 — CTE를 다시 자기 자신과 조인하여 다음 수준의 행을 생성하는 SELECT입니다.

실행 엔진은 새로운 행이 0개가 될 때까지 재귀 단계를 계속 실행하고 결과를 누적합니다.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

1에서 5까지 세기

가장 간단한 재귀 CTE는 숫자를 셉니다. 기본 사례에서 값 1로 시작합니다. 재귀 단계에서는 반복할 때마다 1을 더합니다. 재귀 단계 안의 WHERE 절은 종료 조건으로 작동합니다. 이 조건이 없으면 질의가 영원히 실행됩니다.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

단계별 실행

실행 엔진이 카운터 CTE를 반복할 때마다 처리하는 방식은 다음과 같습니다.

반복 0(기본 사례): {1}을 반환합니다.

반복 1: {1}에 재귀 단계를 적용하여 {2}를 반환합니다.

반복 2: {2}에 재귀 단계를 적용하여 {3}을 반환합니다.

반복 3, 4: {4}, 이어서 {5}를 반환합니다.

반복 5: n=5일 때 WHERE n < 5는 거짓이므로 행을 반환하지 않습니다. 질의가 종료됩니다.

누적된 모든 행인 1, 2, 3, 4, 5가 최종 결과입니다.

계층 테이블 설정

재귀 CTE는 자기 참조 테이블에서 특히 강력합니다. 각 직원이 동일한 테이블을 가리키는 선택적 manager_id를 갖는 employees 테이블을 만들어 보겠습니다.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

계층 탐색

이제 CEO인 앨리스(식별자=1)부터 시작하여 전체 보고 체계를 따라갈 수 있습니다. 기본 사례에서는 앨리스를 선택하고, 재귀 단계에서는 CTE에 이미 있는 식별자와 manager_id가 일치하는 모든 직원을 찾습니다.

결과에는 트리가 아무리 깊어도 앨리스에서 도달할 수 있는 모든 직원이 포함됩니다.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

경로 추적

일반적으로 추가하는 기능은 루트에서 각 노드까지의 전체 연결 경로를 보여 주는 경로 문자열을 만드는 것입니다. 더 깊이 재귀할수록 이름 사이에 ' -> '를 넣어 연결합니다.

이를 사용하면 경로 표시줄 형태의 탐색을 표시하거나 깊은 계층 구조를 점검하기가 쉽습니다.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

재귀 깊이 제한

깊거나 순환하는 데이터로 인해 재귀 CTE가 매우 오랫동안 실행될 수 있습니다. 안전하게 사용하는 두 가지 방법은 다음과 같습니다.

1. 깊이를 추적하고 WHERE 절을 추가하십시오 — WHERE depth < 10을 사용하면 10단계를 절대 넘지 않습니다.

2. 순환 감지 열을 사용하십시오 — 일부 데이터베이스(PostgreSQL 14 이상)는 CYCLE 구문을 제공하여 반복되는 노드 방문을 자동으로 감지합니다.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION과 UNION ALL 비교

재귀 단계에서는 거의 항상 UNION이 아니라 UNION ALL을 사용합니다. 이유는 다음과 같습니다.

UNION은 모든 결과 집합을 비교하여 매 반복 후 행의 중복을 제거합니다. 이 작업은 매우 비용이 많이 들며, 동일한 노드에 여러 경로로 정상적으로 도달할 수 있는 그래프에서는 의미가 달라질 수 있습니다.

UNION ALL은 중복을 제거하지 않고 모든 행을 유지하므로 트리 탐색에서 더 빠르고 올바릅니다. 중복 제거가 반드시 필요하고 성능 비용을 이해하고 있을 때만 UNION을 사용하십시오.

날짜 연속열 생성

재귀 CTE는 날짜의 연속열을 생성할 때도 유용합니다. 다음 예시는 특정 주의 모든 날짜를 생성하며, 달력 보고서를 만들거나 시계열 데이터의 빈 구간을 채울 때 자주 사용하는 방식입니다.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

한 관리자의 모든 부하 직원 찾기

기본 사례는 루트뿐 아니라 특정 노드에서 시작하도록 설정할 수 있습니다. 여기서는 밥(식별자=2)부터 시작하여 밥에게 직접 또는 간접적으로 보고하는 모든 사람을 찾습니다.

이 방식은 권한 확인, 하위 트리 집계, 대시보드의 범위를 단일 부서로 제한하는 작업에 유용합니다.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

빠른 확인

재귀 CTE가 작동하는 방식에 대한 이해도를 확인해 보십시오.

학습 내용 복습

이번 학습에서는 재귀 CTE의 작동 방식을 배웠습니다.

구조: 모든 재귀 CTE는 UNION ALL을 사용하여 기본 사례(시작 행)와 재귀 단계(자기 참조 SELECT)를 연결합니다.

종료: 실행 엔진은 재귀 단계가 행을 0개 반환할 때까지 해당 단계를 반복하고 결과를 누적합니다.

일반적인 용도: 조직도와 폴더 트리 탐색, 숫자 또는 날짜 연속열 생성, 경로 계산, 하위 트리의 모든 노드 찾기 등에 사용합니다.

안전 수칙: 항상 종료 조건(깊이 제한 또는 순환 방어 조건)을 포함하고, 성능을 위해 UNION보다 UNION ALL을 우선 사용하십시오.

자주 묻는 질문

“재귀 CTE의 작동 원리” 강의는 무료인가요?

네 — “재귀 CTE의 작동 원리” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Academy 강의 전체를 잠금 해제할 수 있습니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“재귀 CTE의 작동 원리”에서 뭘 배우나요?

기본 사례와 재귀 단계로 구성됩니다. 브라우저에서 직접 실행하는 실습 코드로 SQL Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Academy을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 1번째 강의입니다.

“재귀 CTE의 작동 원리” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Academy 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 재귀 CTE의 작동 원리
  2. 범주 트리 탐색하기
  3. 계열과 시퀀스 생성
  4. 무한 반복 피하기
← SQL Academy(으)로 돌아가기