Oracle.SQL고급

📊 Oracle SQL 고급 쿼리 완벽 가이드

계층형 쿼리, WITH절, 분석 함수는 대용량 데이터를 다룰 때 강력한 성능과 가독성을 제공합니다. 이 글에서는 실무에 꼭 필요한 기능들과 그 쓰임새를 예시와 함께 소개합니다.

1. 계층형 쿼리 (Hierarchical Query)

부서, 조직도, 카테고리 등 트리 구조 데이터를 조회할 때 사용하는 쿼리입니다.

✅ 예시 테이블

부서번호 | 부서명         | 상위부서번호
--------|----------------|-------------
10      | 총무기획부      | NULL
20      | 마케팅          | 10
30      | 구매/생산부     | 10
170     | 생산팀          | 30
180     | 건설팀          | 30

✅ 계층형 쿼리 예제

SELECT LPAD(' ', LEVEL * 3) || department_name AS dept_name,
       department_id,
       parent_id,
       LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id = parent_id
ORDER SIBLINGS BY department_name;

💡 ORDER SIBLINGS BY: 계층형 순서를 유지하면서 정렬할 수 있는 키워드입니다.

2. WITH 절 (Common Table Expression, CTE)

서브쿼리를 재사용하거나 복잡한 쿼리를 나누어 가독성을 높일 수 있는 강력한 도구입니다.

✅ WITH절 기본 구조

WITH dept_sales AS (
  SELECT department_id, SUM(sales) AS total_sales
  FROM sales
  GROUP BY department_id
),
top_depts AS (
  SELECT *
  FROM dept_sales
  WHERE total_sales > 1000000
)
SELECT *
FROM top_depts;

💡 여러 CTE를 순차적으로 선언하며 이전 CTE의 결과를 후속 CTE에서 사용할 수 있습니다.

3. 분석 함수 (Analytic Functions)

그룹별 집계는 물론, 각 행을 기준으로 다양한 통계를 구할 수 있습니다. ROW_NUMBER, RANK, DENSE_RANK 등을 통해 순위도 계산할 수 있습니다.

✅ 부서별 순번 ROW_NUMBER

SELECT department_id, emp_name,
       ROW_NUMBER() OVER (
         PARTITION BY department_id
         ORDER BY emp_name
       ) AS row_num
FROM employees;

✅ RANK / DENSE_RANK 차이

SELECT emp_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

💡 RANK는 동일 순위 후 건너뛰며, DENSE_RANK는 연속 순위를 유지합니다.

4. WINDOW 절 (범위 지정)

분석 함수와 함께 사용되어 집계 범위를 지정할 수 있습니다.

✅ 예시: 누적합

SELECT emp_name, department_id, salary,
       SUM(salary) OVER (
         PARTITION BY department_id
         ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees;

💡 UNBOUNDED PRECEDING부터 CURRENT ROW까지 누적 계산합니다.

📌 마무리

이제 Oracle SQL의 고급 기능들을 실무에서 활용할 준비가 되셨습니다. 복잡한 데이터도 이해하기 쉽게, 효율적으로 쿼리해보세요!