고급 SQL: 윈도우 함수, CTE, 그리고 재귀 쿼리

윈도우 함수: 데이터 분석의 강력한 도구

윈도우 함수는 데이터 집합 내 각 행에 대해 계산을 수행하면서도, 집계 함수와 달리 원본 데이터를 유지할 수 있는 기능입니다. 이를 통해 더 정교한 데이터 분석이 가능하며, 다양한 비즈니스 요구 사항에 대응할 수 있습니다.

기본 개념

• 윈도우(Window): 데이터를 분석할 특정 범위(창)를 정의합니다. 윈도우는 전체 데이터 집합이 될 수도 있고, 특정 조건에 따라 제한된 범위가 될 수도 있습니다.
• 파티션(Partition): 데이터를 그룹화하여 각 그룹에 대해 독립적으로 연산을 수행합니다. 파티션을 설정하면 각 그룹은 별개의 데이터 집합으로 취급됩니다.
• 정렬(Ordering): 각 파티션 내의 데이터 정렬 기준을 설정합니다. 정렬은 분석 과정에서 연산 순서를 결정하는 중요한 요소입니다.

활용 예제

1. 누적 합계 (Cumulative Sum)

   SELECT
       employee_id,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
   FROM employees;

   이 쿼리는 직원들의 급여를 누적하여 계산합니다. 누적 합계는 금융 데이터 분석이나 시간에 따른 데이터 누적 추적에 유용합니다.

2. 순위(Ranking)

   SELECT
       employee_id,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
   FROM employees;

   이 쿼리는 직원 급여를 기준으로 순위를 매깁니다. 동일한 값이 있을 경우 같은 순위가 부여되고, 다음 순위는 건너뛰게 됩니다.

3. 평균과 차이(Average and Difference)

   SELECT
       department_id,
       employee_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS difference_from_avg
   FROM employees;

   부서별 평균 급여를 계산하고, 각 직원의 급여가 평균에서 얼마나 벗어나는지를 보여줍니다. 이는 급여 분포를 이해하고 이상치를 식별하는 데 매우 유용합니다.

추가 활용 사례

• 이동 평균(Moving Average): 데이터의 연속적인 하위 집합에 대한 평균을 계산하여 트렌드를 분석할 때 사용됩니다.
• 최소 및 최대 값: 특정 파티션 내에서 최소 또는 최대 값을 찾을 수 있습니다. 예를 들어, 특정 부서에서 가장 높은 성과를 낸 직원 식별이 가능합니다.
• 백분위수 계산: 데이터를 특정 백분위수 기준으로 나누어 분포를 분석합니다.
• 누적 비율(Cumulative Percentage): 데이터를 정렬한 후 누적 비율을 계산하여 상대적 기여도를 파악합니다.

윈도우 함수는 금융, 마케팅, 인사 관리 등 다양한 분야에서 사용될 수 있으며, 데이터를 세밀하게 분석하고 비즈니스 의사 결정을 지원하는 데 필수적입니다.

---

CTE: 가독성과 효율성을 동시에

CTE(Common Table Expression) 는 SQL 쿼리 내에서 임시 결과 집합을 정의할 수 있는 기능으로, 복잡한 쿼리를 더욱 간단하고 명확하게 만듭니다. CTE를 활용하면 여러 단계로 이루어진 복잡한 연산을 구조화하여 실행할 수 있습니다. 또한 쿼리를 더욱 읽기 쉽게 만들어 협업 및 유지보수에 도움이 됩니다.

기본 개념

• 가독성 향상: 긴 쿼리를 분리하여 이해하기 쉽도록 작성할 수 있습니다.
• 재사용 가능성: 동일한 결과 집합을 여러 번 참조할 수 있습니다.
• 계층적 데이터 처리: 자기 참조를 통해 복잡한 데이터 계층 구조를 탐색할 수 있습니다. 계층적 데이터는 일반적으로 조직도나 제품 카테고리 구조에서 흔히 볼 수 있습니다.

활용 예제

1. 기본 CTE 활용

   WITH EmployeeSalaries AS (
       SELECT employee_id, department_id, salary
       FROM employees
   )
   SELECT *
   FROM EmployeeSalaries
   WHERE salary > 50000;

   EmployeeSalaries라는 이름의 CTE를 정의하여, 특정 급여 이상의 직원만 간단히 조회할 수 있습니다. 이 방법은 코드 가독성을 높이고, 서브쿼리를 간결하게 대체할 수 있습니다.

2. 계층적 데이터 탐색

   WITH EmployeeHierarchy AS (
       SELECT e.employee_id, e.name, m.name AS manager_name
       FROM employees e
       LEFT JOIN employees m ON e.manager_id = m.employee_id
   )
   SELECT *
   FROM EmployeeHierarchy;

   직원과 관리자의 관계를 한눈에 확인할 수 있는 조직도를 생성합니다. 이 방법은 인사 데이터 관리 시스템에서 중요한 정보를 시각화하는 데 사용됩니다.

3. 재귀적 CTE 활용

   WITH RECURSIVE DepartmentCTE AS (
       SELECT department_id, department_name, parent_department_id
       FROM departments
       WHERE parent_department_id IS NULL
   
       UNION ALL
   
       SELECT d.department_id, d.department_name, d.parent_department_id
       FROM departments d
       INNER JOIN DepartmentCTE dc ON d.parent_department_id = dc.department_id
   )
   SELECT *
   FROM DepartmentCTE;

   최상위 부서에서 시작하여 모든 하위 부서를 탐색할 수 있습니다. 이는 조직 구조 또는 제품 분류 데이터를 처리하는 데 효과적입니다.

추가 활용 사례

• 중간 집계 결과 활용: CTE를 이용하여 중간 단계의 집계 결과를 저장한 뒤, 최종 결과를 계산할 수 있습니다.
• 복잡한 조건 필터링: CTE를 여러 번 중첩하여 복잡한 조건을 단계적으로 처리할 수 있습니다.
• 데이터 정규화: 복잡한 데이터 구조를 CTE로 분리하여 정규화 과정을 돕습니다.
• 다중 데이터 소스 통합: 여러 테이블이나 데이터 소스를 결합하여 통합된 결과를 제공하는 데 CTE를 활용할 수 있습니다.

CTE는 단순한 데이터 정리에만 국한되지 않으며, 복잡한 비즈니스 로직을 구조적으로 작성하고 디버깅하기 쉽게 만들어줍니다. 이를 통해 분석 프로세스를 더 체계적으로 구현할 수 있습니다.

---

재귀 쿼리: 계층적 데이터의 탐색

재귀 쿼리는 주로 계층적 데이터를 처리하는 데 사용됩니다. 조직도, 제품 카테고리, 가족 트리와 같은 구조적 데이터를 다룰 때 매우 유용합니다. 계층 구조를 재귀적으로 탐색할 수 있는 SQL 기술은 데이터베이스 설계 및 분석에서 중요한 역할을 합니다.

기본 구조

1. 기본 사례 (Anchor Member): 초기 결과를 반환하는 SELECT 문.
2. 재귀 사례 (Recursive Member): 이전 단계의 결과를 기반으로 추가 데이터를 생성.

활용 예제

직원의 계층 구조를 탐색하는 예제를 통해 재귀 쿼리의 활용을 살펴보겠습니다.

WITH RECURSIVE EmployeeCTE AS (
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN EmployeeCTE cte ON e.ManagerID = cte.EmployeeID
)
SELECT *
FROM EmployeeCTE;

이 쿼리를 통해 최상위 관리자부터 시작하여 모든 하위 직원들의 정보를 한 번에 조회할 수 있습니다. 결과적으로, 조직 전체의 계층 구조를 명확히 파악할 수 있습니다.

추가 활용 사례

• 조직도 작성: 직원들의 계층적 관계를 시각화하여 회사 구조를 이해.
• 재고 관리: 제품과 하위 제품 간의 관계를 탐색하여 재고 현황을 분석.
• 가족 트리 분석: 가계도 데이터를 분석하여 조상과 후손을 탐색.
• 다단계 마케팅 데이터 분석: 네트워크 관계를 기반으로 데이터 패턴을 파악.
• 복합 경로 탐색: 네트워크나 그래프 구조 데이터를 재귀적으로 처리하여 최단 경로를 찾거나 연결성을 분석.

재귀 쿼리는 복잡한 계층 구조 데이터를 단순화하고, 이를 바탕으로 의미 있는 인사이트를 도출하는 데 필수적입니다. 데이터를 시각적으로 표현하거나 비즈니스 로직을 모델링하는 데 활용도가 높습니다.

---

결론

윈도우 함수, CTE, 재귀 쿼리는 SQL의 강력한 도구로, 복잡한 데이터를 효율적이고 명확하게 분석하고 탐색할 수 있게 해줍니다. 이 세 가지 기능은 비즈니스 인사이트 도출, 데이터 시각화, 그리고 복잡한 관계 데이터를 처리하는 데 있어 필수적인 역할을 합니다. 이를 활용하여 데이터베이스 작업을 최적화하고, 실제 비즈니스 문제를 해결할 수 있는 기반을 마련하세요. 충분한 연습과 실제 프로젝트 적용을 통해 이러한 기술들을 마스터해보세요. 더 나아가, 이를 활용하여 데이터 기반의 의사 결정을 더욱 신속하고 정확하게 내릴 수 있을 것입니다. 또한 이러한 기술은 데이터 엔지니어링, 분석 및 데이터 과학 분야에서도 큰 가치를 제공합니다. 이를 적극적으로 익혀 데이터 활용 역량을 강화해 보세요.