MS-SQL 계층형 쿼리: 직관적인 데이터 검색 방법

1. 계층형 쿼리란?

 

 

 

[1. 계층형 쿼리란?]

 

계층형 쿼리는 트리 구조 형태의 데이터에서 원하는 정보를 검색할 때 사용되는 방법 중 하나입니다. 트리 구조 형태의 데이터는 부모, 자식 관계로 구성되어 있으며, 계층형 쿼리는 이러한 관계를 기반으로 데이터를 검색합니다.

 

예를 들어, 조직도처럼 부서와 직원 정보가 계층적으로 구성된 데이터에서 특정 부서에 소속된 직원들의 정보를 검색하거나, 게시판의 댓글과 대댓글 구조에서 특정 게시글에 대한 모든 댓글을 검색하는 등의 용도로 많이 사용됩니다.

 

계층형 쿼리를 사용하면 여러 단계에 걸쳐 데이터를 직접 검색하는 것보다 더 간결하고 직관적인 쿼리를 작성할 수 있습니다. 또한, 계층형 쿼리를 사용하면 데이터의 변화에 따라 계층 구조가 변경되어도 쿼리를 수정하지 않아도 검색 결과가 올바르게 나오는 장점도 있습니다.

 

 

 

2. MS-SQL에서 계층형 쿼리 구현 방법

 

 

 

MS-SQL에서 계층형 쿼리를 구현하는 방법은 다음과 같다.

 

1. WITH 절 적용

 

계층형 데이터를 쿼리하기 위해서는 WITH(common table expressions) 절을 사용해야 한다. WITH 절을 사용하면서 각 레코드가 부모 레코드에 대한 참조를 포함하도록 조인하는 방법으로 계층 구조를 표시할 수 있다.

 

2. 재귀적 쿼리 적용

 

WITH 절을 사용하여 재귀적인 쿼리를 실행할 수 있도록 메인 쿼리와 재귀 쿼리를 구성해야 한다.

 

3. CTE 정의

 

정의된 common table expressions은 각 레코드에 대한 참조를 사용하여 계층 구조를 표시한다. CTE의 구성은 다음과 같다.

 

- Anchor member: 계층 구조의 가장 상위 노드

 

- Recursive member: 상위 노드에 대한 참조가 있는 하위 노드들

 

- UNION ALL 조건: Anchor와 Recursive 쿼리를 결합하는 방식

 

예시:

 

WITH employeeCTE (emp_id, emp_name, manager_id, level)

 

AS

 

(

 

-- Anchor member

 

SELECT emp_id, emp_name, manager_id, 0 AS Level

 

FROM employee

 

WHERE manager_id IS NULL

 

-- Recursive member

 

UNION ALL

 

SELECT e.emp_id, e.emp_name, e.manager_id, ecte.Level + 1

 

FROM employee e

 

INNER JOIN employeeCTE ecte ON e.manager_id = ecte.emp_id

 

)

 

SELECT emp_id, emp_name, manager_id, level

 

FROM employeeCTE

 

ORDER BY level, emp_name;

 

위 코드와 같이 계층적으로 구성된 employee 테이블에서 각 노드의 level, emp_id, emp_name, manager_id 값을 가져와서 계층 구조로 표시할 수 있다.

 

 

 

3. 계층형 쿼리를 이용한 데이터 검색 방법

 

 

 

계층형 쿼리를 이용하면, 특정 데이터와 관련된 하위 데이터들을 검색하는 것이 가능해진다. 이렇게 검색된 하위 데이터들은 다시 다른 관련 데이터들과 연결되어 있어, 전체적인 관계를 파악하기에도 용이하다.

 

가령, 부모-자식 관계로 이루어진 조직도에서, 특정 직원을 검색하면 해당 직원의 직속 상하위 부서 인원들을 함께 검색하는 것이 가능하다. 이를 통해 해당 직원이 속한 부서의 전반적인 구성원들을 파악하고, 부서 내 사람들 간의 연결 관계를 쉽게 파악하게 된다.

 

계층형 쿼리는 일반적으로 WITH 구문과 함께 사용되며, 이를 통해 긴 SQL 쿼리를 좀 더 직관적으로 작성할 수 있다. WITH 구문으로 먼저 계층 구조를 정의한 후, 그 구조에 맞게 데이터를 검색하는 식으로 코드를 작성할 수 있다.

 

예시:

 

WITH EmployeeHierarchy (EmpID, EmpName, DeptID, ManagerID, DeptName)

 

AS

 

(

 

SELECT emp.EmpID, emp.EmpName, emp.DeptID, emp.ManagerID, dept.DeptName

 

FROM Employee emp

 

JOIN Department dept ON emp.DeptID = dept.DeptID

 

UNION ALL

 

SELECT e.EmpID, e.EmpName, e.DeptID, e.ManagerID, d.DeptName

 

FROM Employee e

 

JOIN Department d ON e.DeptID = d.DeptID

 

JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmpID)

 

SELECT *

 

FROM EmployeeHierarchy

 

WHERE EmpID = 5;

 

위 예시에서는 WITH 구문에서 EmployeeHierarchy라는 임시 테이블을 정의한다. 해당 테이블은 사원의 ID, 이름, 소속 부서 ID, 상사 ID, 부서 이름 필드들로 구성된다.

 

그 후, UNION ALL 구문을 이용하여, Employee와 Department 테이블을 조인하여 각 직원들의 부서 정보를 가져온 후, 다시 해당 직원의 상사 정보를 조회하여 EmployeeHierarchy에 추가하게 된다.

 

이와 같은 구조를 통해, 상위 직원의 정보를 담은 레코드와 하위 직원의 정보를 담은 레코드 모두 EmployeeHierarchy 테이블 내에 모두 포함시킬 수 있다.

 

마지막으로, WHERE 절에서 검색하고자 하는 직원의 ID를 지정하여 해당 직원과 상하위 부서 인원들의 정보만 가져오게 된다.

 

 

 

4. 계층형 쿼리의 활용 예시

 

 

[4. 계층형 쿼리의 활용 예시]

 

계층형 쿼리는 조직도, 카테고리, 히어라키와 같은 구조화된 데이터를 다룰 때 유용하게 사용됩니다. 이 섹션에서는 계층형 쿼리의 실제 활용 예시를 살펴보겠습니다.

 

1) 부서별 직원 조회하기

 

먼저, 조직도를 예시로 살펴보겠습니다. 부서별로 속한 직원 목록을 조회하고 싶을 때, 계층형 쿼리를 사용할 수 있습니다. 다음은 이를 위해 작성된 쿼리입니다.

 

```

 

WITH EmployeeCTE AS

 

(

 

SELECT EmployeeID, Name, Title, ManagerID, DepartmentID, 0 AS EmployeeLevel

 

FROM Employees

 

WHERE ManagerID IS NULL -- CEO

 

UNION ALL

 

SELECT e.EmployeeID, e.Name, e.Title, e.ManagerID, e.DepartmentID, ec.EmployeeLevel + 1

 

FROM Employees e

 

JOIN EmployeeCTE ec ON e.ManagerID = ec.EmployeeID

 

)

 

SELECT Name, Title, EmployeeLevel, DepartmentName

 

FROM EmployeeCTE ec

 

JOIN Departments d ON ec.DepartmentID = d.DepartmentID

 

ORDER BY DepartmentName, EmployeeLevel, Name;

 

```

 

이 쿼리는 EmployeeCTE라는 공통 테이블 식을 사용하여 부서별로 조직도를 생성합니다. 첫 번째 부분에서는 CEO(최고경영자)를 찾기 위해 ManagerID가 NULL인 레코드를 선택하고, 두 번째 부분에서는 EmployeeCTE를 사용하여 최고경영자의 직원들을 찾습니다. 쿼리는 이러한 과정을 반복하여 모든 부서의 직원을 찾습니다.

 

2) 카테고리별 상품 조회하기

 

계층형 쿼리는 카테고리와 연관된 상품 목록을 검색하는 데에도 사용할 수 있습니다. 다음은 이를 위해 작성된 쿼리입니다.

 

```

 

WITH CategoryCTE AS

 

(

 

SELECT CategoryID, CategoryName, ParentCategoryID, 0 AS CategoryLevel

 

FROM Categories

 

WHERE ParentCategoryID IS NULL

 

UNION ALL

 

SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, cc.CategoryLevel + 1

 

FROM Categories c

 

JOIN CategoryCTE cc ON c.ParentCategoryID = cc.CategoryID

 

)

 

SELECT ProductName, CategoryLevel, CategoryName

 

FROM Products p

 

JOIN CategoryCTE cc ON p.CategoryID = cc.CategoryID

 

ORDER BY CategoryName, ProductName;

 

```

 

이 쿼리는 CategoryCTE 라는 공통 테이블 식을 사용하여 카테고리별로 상품을 분류합니다. 첫 번째 부분에서는 상위 카테고리를 찾기 위해 ParentCategoryID가 NULL인 레코드를 선택하고, 두 번째 부분에서는 CategoryCTE를 사용하여 하위 카테고리의 상품들을 찾습니다. 쿼리는 이러한 과정을 반복하여 모든 카테고리의 상품을 찾습니다.

 

최근에는 계층형 쿼리를 이용해 코로나 바이러스 관련 실시간 정보를 도출하는 등의 다양한 활용 예시가 늘어나고 있습니다.