SQL에서 재귀 CTE를 사용하여 계층 적 데이터를 쿼리하려면 어떻게합니까?

계층 적 데이터에 재귀 CTE를 사용합니다

재귀 공통 테이블 표현식 (CTE)은 조직 차트, 파일 시스템 또는 재료 청구서와 같은 계층 적 데이터를 쿼리하기위한 SQL의 강력한 도구입니다. 그들은 당신이 정의 내에서 CTE 자체를 반복적으로 참조하여 나무와 같은 구조를 가로 질러 허용합니다. 기본 구조에는 앵커 멤버 (초기 쿼리)와 재귀 멤버 (자체 참조 부품)가 포함됩니다.

employees 이라는 테이블에 표시된 조직 차트의 간단한 예를 보여 드리겠습니다.

 <code class="sql">CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), manager_id INT ); INSERT INTO employees (employee_id, employee_name, manager_id) VALUES (1, 'CEO', NULL), (2, 'VP Sales', 1), (3, 'Sales Rep 1', 2), (4, 'Sales Rep 2', 2), (5, 'VP Marketing', 1), (6, 'Marketing Manager', 5);</code>

CEO (Employee_id 1)에 따라 전체 계층을 검색하기 위해 재귀 CTE를 사용합니다.

 <code class="sql">WITH RECURSIVE EmployeeHierarchy AS ( -- Anchor member: Selects the CEO SELECT employee_id, employee_name, manager_id, 0 as level FROM employees WHERE employee_id = 1 UNION ALL -- Recursive member: Joins with itself to find subordinates SELECT e.employee_id, e.employee_name, e.manager_id, eh.level 1 FROM employees e INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM EmployeeHierarchy;</code>

이 쿼리는 CEO로 시작하여 더 이상 직원이 이미 포함 된 직원에게보고하지 않을 때까지 부하 직원을 재귀 적으로 추가합니다. level 열은 계층 구조의 깊이를 나타냅니다. UNION ALL 앵커와 재귀 멤버의 결과를 결합합니다. 열쇠는 재귀 멤버의 employees 과 EmployeeHierarchy 자체 합의이며 각 직원을 관리자와 연결하는 것입니다.

재귀 CTE를 사용할 때 피할 수있는 일반적인 함정

재귀 CTE와 함께 작업 할 때 몇 가지 함정이 잘못된 결과 또는 성능 문제로 이어질 수 있습니다.

• Infinite Recursion : 가장 일반적인 실수는 데이터에주기를 만드는 것입니다. 또는 적절한 종료 조건이없는 재귀 쿼리입니다. 이로 인해 쿼리가 무기한 실행됩니다. 귀하의 데이터가 acyclic인지 확인하십시오 (직원 보고서는 직접 또는 간접적으로 보고서가 없음) 재귀 멤버가 결국 종료됩니다 (예 : 계층 구조에서 리프 노드에 도달함으로써).
• 잘못된 조정 조건 : 재귀 멤버에서 잘못된 조인 조건을 사용하면 누락 또는 추가 데이터가 발생합니다. 조인 조건을 신중하게 확인하여 데이터의 계층 적 관계를 정확하게 반영하는지 확인하십시오.
• 종료 조건 부족 : 재귀 CTE에는 무한 루프를 방지하기 위해 명확한 종료 조건이 있어야합니다. 이것은 일반적으로 특정 값 (예 : 부모 ID 열의 NULL )을 확인하거나 재귀 깊이를 제한하여 수행됩니다.
• 데이터 중복 무시 : Union 대신 UNION UNION ALL 사용에는 계층에 존재하는 경우 중복 행이 포함됩니다. 중복을 제거 해야하는 경우 UNION 사용하십시오. 그러나 UNION ALL 일반적으로 더 빠릅니다.

대형 데이터 세트의 재귀 CTE 쿼리 최적화

재귀 CTE는 매우 큰 계층 적 데이터 세트에서 느릴 수 있습니다. 몇 가지 최적화 전략은 성능을 향상시킬 수 있습니다.

• 인덱싱 : 조인 조건 (일반적으로 부모-자식 관계 열)에 사용 된 열에 적절한 인덱스가 존재하는지 확인하십시오. 인덱스는 재귀 CTE 내에서 조인의 속도를 크게 높입니다.
• 필터링 : 앵커 및/또는 재귀 멤버에 조항을 추가하여 WHERE 구조의 불필요한 분기를 필터링하여 재귀 범위를 제한하십시오. 이것은 처리 된 데이터의 양을 줄입니다.
• 구체화 된 뷰 : 자주 실행되는 재귀 쿼리의 경우 계층 적 데이터를 사전 컴퓨팅하는 구체화 된 뷰를 작성하십시오. 이는 저장 공간 비용과 일부 데이터 핵심으로 쿼리 성능을 크게 향상시킬 수 있습니다.
• 대체 접근법 : 매우 큰 데이터 세트의 경우 인접성 목록 또는 중첩 세트를 사용하는 것과 같은 대체 접근 방식을 고려하여 특정 계층 적 쿼리에 더 나은 성능을 제공 할 수 있습니다. 재귀 CTE가 항상 모든 시나리오에 대한 최적의 솔루션은 아닙니다.
• 배치 처리 : 전체 계층 구조를 단일 쿼리로 처리하는 대신 더 작은 배치로 분류하는 것을 고려하십시오.

다른 데이터베이스 시스템의 재귀 CTE

재귀 CTE는 대부분의 주요 데이터베이스 시스템에서 지원되지만 구문은 약간 다를 수 있습니다.

• SQL Server : WITH RECURSIVE 사용합니다 ( RECURSIVE 키워드는 선택 사항이지만).
• Postgresql : WITH RECURSIVE 사용합니다.
• MySQL : 버전 8.0에서 시작하는 재귀 CTE를 지원합니다. 구문은 PostgreSQL과 유사합니다.
• Oracle : 조항은 약간 다른 구문을 가지지 만 동일한 기능을 달성하는 클로즈와 START WITH CONNECT BY 재귀 CTE를 지원합니다.

핵심 개념은 다른 시스템에서 동일하게 유지되지만 올바른 구문 및 시스템 별 한계 또는 최적화에 대한 특정 데이터베이스 시스템의 문서를 항상 참조하십시오. 쿼리를 철저히 테스트하고 성능을 프로파일 링하여 병목 현상을 식별하고 해결해야합니다.

위 내용은 SQL에서 재귀 CTE를 사용하여 계층 적 데이터를 쿼리하려면 어떻게합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!