Database/MySQL

SQL 재귀 쿼리

jmkim9 2023. 7. 6. 10:15
반응형

SQL(Structured Query Language)은 관계형 데이터베이스를 관리하고 조작하기 위한 강력한 도구입니다. SQL은 테이블 형식으로 데이터를 검색하고 조작하는 데 탁월하지만 계층적 또는 재귀적 데이터 구조를 쿼리해야 하는 상황이 있습니다. 재귀 쿼리가 작동하는 곳입니다. 이 블로그에서는 SQL의 재귀 쿼리 개념을 살펴보고 그 이점을 이해하며 계층적 데이터를 효율적으로 처리하는 데 활용할 수 있는 방법을 알아봅니다.

 

재귀 쿼리 이해:

재귀 공통 테이블 식(CTE)이라고도 하는 재귀 쿼리를 사용하면 동일한 테이블 내에서 부모-자식 관계가 있는 계층적 데이터 구조를 쿼리할 수 있습니다. 단일 쿼리에서 여러 수준의 데이터를 검색하여 계층 구조를 탐색할 수 있습니다. 재귀 쿼리는 원하는 결과를 얻을 때까지 반복적으로 작성되는 임시 결과 집합인 재귀 CTE를 사용합니다.
재귀 CTE 구문:
재귀 CTE의 구문은 앵커 멤버와 재귀 멤버의 두 부분으로 구성됩니다. 앵커 멤버는 재귀의 기본 사례 또는 시작점을 나타내고 재귀 멤버는 반복 또는 재귀 단계를 정의합니다. 두 멤버는 UNION ALL 연산자를 사용하여 결합됩니다.

 

CREATE TABLE tbcate (
	no     BIGINT UNSIGNED NOT NULL, -- 번호
	name   varchar(100)    NULL,     -- 이름
	parent BIGINT UNSIGNED NULL      -- 부모
);
INSERT INTO tbcate (no, name, parent) VALUES (1, 'Electronics', NULL);
INSERT INTO tbcate (no, name, parent) VALUES (5, 'Camera & Photo', 1);
INSERT INTO tbcate (no, name, parent) VALUES (4, 'Computers & Accessories', 1);
INSERT INTO tbcate (no, name, parent) VALUES (6, 'Cell Phones & Accessories', 1);
INSERT INTO tbcate (no, name, parent) VALUES (14, 'Networking Products', 4);
INSERT INTO tbcate (no, name, parent) VALUES (13, 'Computers & Tablets', 4);
INSERT INTO tbcate (no, name, parent) VALUES (15, 'Monitors', 4);
WITH RECURSIVE cte (no, name, parent) AS (
    SELECT c.no, c.name, c.parent FROM tbcate c where no = 1
    UNION ALL
    SELECT c.no, c.name, c.parent FROM tbcate c JOIN cte ON c.parent = cte.no
)
SELECT * FROM cte order by parent;

 

 

재귀 쿼리 사용 사례:

재귀 쿼리는 조직도, 파일 시스템 또는 범주 계층과 같은 계층적 데이터 구조를 처리할 때 특히 유용합니다. 재귀 쿼리를 사용하면 지정된 노드의 모든 하위 항목을 검색하고, 각 노드의 깊이를 결정하고, 계층 구조를 따라 합계 또는 평균과 같은 집계 값을 계산하고, 경로 기반 검색도 수행할 수 있습니다.

 

재귀 쿼리의 이점:

단순화된 데이터 검색: 재귀 쿼리를 사용하면 단일 쿼리로 여러 수준의 계층 구조에서 데이터를 가져올 수 있으므로 여러 쿼리 또는 복잡한 절차 논리가 필요하지 않습니다.
유연성 및 확장성: 재귀 쿼리는 다양한 수준의 계층을 처리하기 위한 유연하고 확장 가능한 솔루션을 제공합니다. 쿼리는 깊이에 관계없이 계층 구조를 통과하므로 다양한 데이터 구조에 적응할 수 있습니다.
향상된 성능: SQL의 재귀 쿼리는 효율성을 위해 최적화되어 기본 데이터베이스 엔진의 기능을 활용하여 반복 조인을 수행하고 불필요한 계산을 최소화합니다.
코드 유지 관리: 재귀 쿼리를 사용하면 단일 쿼리 내에서 복잡한 계층적 작업을 표현할 수 있으므로 코드를 보다 유지 관리하기 쉽고 이해하기 쉽습니다.

 

재귀 쿼리 제한 사항:

재귀 쿼리는 강력한 기능을 제공하지만 제한 사항을 고려하는 것이 중요합니다. 재귀 쿼리는 특히 계층 구조가 깊거나 복잡한 경우 상당한 리소스와 처리 시간을 소비할 수 있습니다. 또한 데이터베이스 시스템마다 재귀 쿼리 지원이 다를 수 있으므로 특정 SQL 언어 및 버전 호환성을 검토하는 것이 중요합니다.


SQL의 재귀 쿼리는 계층적 데이터 구조를 효율적으로 쿼리하는 데 유용한 도구를 제공합니다. 재귀 공통 테이블 식을 활용하면 복잡한 계층 구조에서 데이터를 쉽게 탐색하고 검색할 수 있습니다. 조직 구조, 파일 시스템 또는 기타 계층적 데이터로 작업하는 경우 재귀 쿼리를 사용하면 단일 SQL 쿼리 내에서 강력하고 표현적인 작업을 수행할 수 있습니다. 
재귀 쿼리를 마스터하면 데이터베이스 기반 애플리케이션에서 계층적 데이터를 분석하고 관리하기 위한 새로운 가능성을 열 수 있습니다.

반응형