답변.

SQL에서는 반복/재귀 계산을 구현하는 여러 가지 방법이 있습니다:

재귀적 CTE (WITH RECURSIVE) — 계층 탐색과 같은 작업에 유용합니다(트리, 그래프, 링크 체인).
커서 (CURSOR) — 데이터를 행 단위로 처리할 수 있지만, 대량의 데이터에 대해서는 집합 기반 작업보다 성능이 떨어집니다.
일반적인 집합 기반 표현식 — 큰 경우 대부분을 위해 바람직합니다(반복 없이 행 집합을 조작합니다).

재귀적 CTE를 사용하는 경우:

계층 구조(제품 카테고리, 조직 구조)일 때.
경로/체인의 길이를 찾을 때(예: 계보, 의존성 그래프).

예제:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

재귀적 CTE가 문제를 해결하지 못하거나 데이터 양이 너무 많은 경우, 다른 메커니즘(예: 외부 처리, 임시 테이블 또는 SQL 외부의 특수 알고리즘)을 사용합니다.

함정 질문.

재귀적 CTE가 무한 실행을 초래할 수 있나요? 어떻게 방지할 수 있나요?

답변: 예, 그래프에 사이클이 있을 경우(예: parent_id가 자식을 다시 가리키는 경우) 재귀적 CTE가 무한 루프에 빠질 수 있습니다. 대부분의 DBMS는 재귀 깊이의 최대 수를 제한하는 기능을 지원합니다(MAXRECURSION을 MS SQL 또는 유사한 옵션으로 사용). 재귀 깊이를 항상 제한하는 것이 좋은 관행입니다.

-- MS SQL Server
OPTION (MAXRECURSION 100)

주제에 대한 세부 사항을 모르고 발생한 실제 오류 사례.

이야기

금융 프로젝트에서 재귀적 CTE를 통해 연결된 계정(parent-child) 체인의 최종 잔액을 계산했습니다. 한 사용자가 구조 안에 사이클을 만들어(부모가 자신의 자식이 됨) 요청이 무한히 실행되었습니다. 서버에 부하가 걸렸습니다. 사이클 체크와 MAXRECURSION을 추가하여 문제를 해결했습니다.

이야기

작업 관리 시스템에서 종속 작업의 상태를 커서 처리를 통해 계산했습니다. 재귀적 CTE로 마이그레이션 하면서 parent_id에 대한 최적 인덱스를 고려하지 않아 성능이 5배 저하되었습니다. 각 단계에서 costly join 작업이 발생했기 때문입니다.

이야기

커서를 사용한 카테고리 트리 탐색 쿼리는 제대로 작동했지만 100,000행에 대해 7분이 걸렸습니다. "느리다"고 여겨진 재귀적 CTE로 재작성하여, 집합 작업 엔진의 최적화 덕분에 5초 만에 동일한 결과를 얻었습니다.

SQL에서 복잡한 반복 계산을 구현하는 다양한 방법을 설명하십시오. 언제 재귀적 공통 테이블 표현식(CTE)을 사용해야 하며, 언제 다른 방법을 사용하는 것이 더 나은가요?

답변.

함정 질문.

주제에 대한 세부 사항을 모르고 발생한 실제 오류 사례.