오라클 SQL NTILE OVER 함수: 특정 컬럼을 기준으로 등급 부여 및 그룹 묶기

1. NTILE 함수란?

오라클 SQL에서 NTILE 함수는 특정 컬럼의 값을 기준으로 데이터를 N개의 등급으로 나누는 윈도우 함수입니다. 주어진 데이터를 오름차순 또는 내림차순으로 정렬한 후, 균등한 그룹으로 나누어 각 행에 등급을 부여하는 방식입니다.

1.1 NTILE 함수 사용 사례

다음과 같은 경우에 NTILE 함수를 사용할 수 있습니다:

• 직원들의 급여를 기준으로 10개 등급으로 나누기
• 학생들의 점수를 5개 등급으로 분류하기
• 특정한 집단별 등급 나누기
• 특정 값에 따른 레코드 그룹 묶기

이처럼 오름차순 또는 내림차순으로 데이터를 분류해야 할 때 NTILE OVER를 활용하면 유용합니다.

2. NTILE 함수의 기본 구문

2.1 기본 사용법 (오름차순 정렬)

특정 컬럼(K)을 기준으로 N개의 등급으로 나누려면 다음과 같이 사용합니다:

SELECT 컬럼X, 컬럼Y, ...,
       NTILE(숫자N) OVER (ORDER BY 컬럼K) AS 등급
FROM 테이블A;

• NTILE(숫자N): 데이터를 N개의 등급으로 나눕니다.
• ORDER BY 컬럼K: 등급을 나누는 기준이 되는 컬럼을 지정합니다.
• 등급은 오름차순으로 부여됩니다. 즉, 가장 작은 값이 1등급이 되고, 가장 큰 값이 N등급이 됩니다.

2.2 그룹별 등급 부여 (내림차순 정렬)

특정 그룹(컬럼L) 내에서 컬럼K를 기준으로 내림차순 정렬 후, N개 등급으로 나누는 방법은 다음과 같습니다:

SELECT 컬럼X, 컬럼Y, ...,
       NTILE(숫자N) OVER (PARTITION BY 컬럼L ORDER BY 컬럼K DESC) AS 등급
FROM 테이블A
WHERE 조건;

• PARTITION BY 컬럼L: 컬럼L을 기준으로 데이터를 그룹화합니다.
• ORDER BY 컬럼K DESC: 컬럼K를 기준으로 내림차순 정렬합니다.
• 내림차순 정렬 시, 가장 큰 값이 1등급이고 작은 값이 N등급이 됩니다.

2.3 특정 등급만 조회

특정 등급(M)만 조회하고 싶다면 서브쿼리를 활용해야 합니다:

SELECT * FROM (
    SELECT 컬럼X, 컬럼Y, ...,
           NTILE(숫자N) OVER (PARTITION BY 컬럼L ORDER BY 컬럼K DESC) AS 등급
    FROM 테이블A
    WHERE 조건
)
WHERE 등급 = 숫자M;

3. NTILE 함수 실습 예제

아래 예제에서는 hr 스키마의 employees 테이블을 사용하여 NTILE 함수의 실제 적용 사례를 살펴봅니다.

3.1 급여 기준 10등급으로 나누기 (오름차순)

사원들의 급여(salary)를 기준으로 10등급으로 나누고자 합니다:

SELECT employee_id, first_name, salary, job_id,
       NTILE(10) OVER (ORDER BY salary) AS ntile_10
FROM employees;

• salary가 가장 낮은 사원부터 1등급을 부여하고, 높은 사원일수록 등급이 증가합니다.
• 전체 107개의 레코드를 10개의 그룹으로 분류합니다.

3.2 특정 직급 내 급여 기준 3등급으로 나누기 (내림차순)

job_id가 IT_PROG인 사원들만 급여 기준으로 3등급으로 나누려면 다음과 같이 작성합니다:

SELECT employee_id, first_name, salary, job_id,
       NTILE(3) OVER (PARTITION BY job_id ORDER BY salary DESC) AS ntile_3
FROM employees
WHERE job_id = 'IT_PROG';

• PARTITION BY job_id: 동일한 job_id 내에서 급여 기준 등급을 부여합니다.
• ORDER BY salary DESC: 급여가 높은 순서대로 1등급을 부여합니다.

3.3 모든 직급별 급여 기준 5등급으로 나누기

전체 사원들을 직책(job_id)별로 5등급으로 나누는 SQL문입니다:

SELECT employee_id, first_name, salary, job_id,
       NTILE(5) OVER (PARTITION BY job_id ORDER BY salary DESC) AS ntile_5
FROM employees
ORDER BY job_id, salary;

• 모든 직책별(job_id)로 급여(salary)를 내림차순 정렬한 후, 5개의 등급을 부여합니다.

3.4 특정 등급만 조회하기 (1등급 사원만)

위 예제(3.3)에서 1등급 사원만 조회하려면 서브쿼리를 사용합니다:

SELECT * FROM (
    SELECT employee_id, first_name, salary, job_id,
           NTILE(5) OVER (PARTITION BY job_id ORDER BY salary DESC) AS ntile_5
    FROM employees
    ORDER BY job_id, salary
)
WHERE ntile_5 = 1;

• WHERE ntile_5 = 1: 각 직책별로 1등급으로 분류된 사원만 조회합니다.

4. 정리

NTILE OVER 함수는 데이터를 N개의 그룹으로 분류하는 데 매우 유용한 윈도우 함수입니다. 특히, 급여와 같은 연속형 데이터를 분석할 때 등급을 부여하거나 특정 그룹 내에서 등급을 나눌 때 활용됩니다.

핵심 요약

• NTILE(N) OVER (ORDER BY 컬럼): 전체 데이터를 N개 등급으로 나눔
• PARTITION BY 컬럼L: 특정 그룹 내에서 등급 부여
• ORDER BY 컬럼K DESC: 내림차순 정렬하여 등급 부여
• 서브쿼리를 활용하여 특정 등급만 필터링 가능

이제 NTILE 함수를 활용하여 다양한 데이터 그룹화 및 분석 작업을 수행해 보세요!