[Oracle] 오라클 NTH_VALUE() 함수로 특정 순위의 값 가져오는 법

Oracle NTH_VALUE()

 

 

 

Oracle SQL의 NTH_VALUE() 함수는 분석 함수 중 하나로, 윈도 내 N번째 행의 값을 반환하는 기능을 합니다. 특정 순위의 데이터를 추출해야 하는 다양한 실무 상황에서 유용하게 쓰입니다.

1. NTH_VALUE() 함수란?

NTH_VALUE(expr, N) OVER (PARTITION BY... ORDER BY... ROWS BETWEEN...) 형식으로 사용되며, 윈도 내 N번째 행의 expr 값을 반환합니다.

• expr: 반환할 열(칼럼)의 값
• N: 반환할 행의 위치 (1부터 시작)
• PARTITION BY: 그룹핑 기준
• ORDER BY: 정렬 기준 (N번째 결정 요소)
• ROWS BETWEEN: 윈도 범위

2. 기본 사용 예제

SELECT employee_id, salary,
       NTH_VALUE(salary, 2) OVER (
         ORDER BY hire_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_salary
FROM employees;

입사일 기준 전체 정렬 후, 두 번째로 입사한 직원의 급여를 모든 행에 대해 반환합니다.

3. PARTITION BY와 함께 사용

SELECT department_id, employee_id, salary,
       NTH_VALUE(salary, 3) OVER (
         PARTITION BY department_id
         ORDER BY hire_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_salary_in_dept
FROM employees;

부서별로 입사일 기준 세 번째로 입사한 직원의 급여를 부서별로 반환합니다.

4. ROWS BETWEEN의 중요성

NTH_VALUE() 함수는 기본적으로 현재 행까지의 범위를 사용하기 때문에, 원하는 결과를 얻으려면 반드시 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 지정해야 합니다.

5. 실무 활용 예제

5.1 고객별 세 번째 구매 금액

SELECT customer_id, transaction_date, amount,
       NTH_VALUE(amount, 3) OVER (
         PARTITION BY customer_id
         ORDER BY transaction_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_purchase
FROM transactions;

5.2 부서별 세 번째로 높은 급여

SELECT department_id, employee_id, salary,
       NTH_VALUE(salary, 3) OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_highest_salary
FROM employees;

5.3 제품별 세 번째 출시 가격

SELECT product_id, release_date, price,
       NTH_VALUE(price, 3) OVER (
         PARTITION BY product_id
         ORDER BY release_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_release_price
FROM product_history;

6. FIRST_VALUE() / LAST_VALUE()와 비교

함수	기능	특징
FIRST_VALUE()	첫 번째 행의 값	N = 1과 동일
LAST_VALUE()	마지막 행의 값	윈도우 프레임 설정 필요
NTH_VALUE()	N번째 행의 값	N 지정 가능

7. 성능 및 주의사항

• ORDER BY 대상 칼럼에 인덱스를 설정하면 성능 향상
• ROWS BETWEEN 절을 생략하면 예상과 다른 결과가 나올 수 있음
• N번째 값이 존재하지 않으면 NULL 반환

8. 응용 시나리오

8.1 세 번째 방문 페이지 분석

SELECT user_id, visit_time, page_url,
       NTH_VALUE(page_url, 3) OVER (
         PARTITION BY user_id
         ORDER BY visit_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_visited_page
FROM web_logs;

8.2 3분기 동안의 세 번째 판매 상품

SELECT quarter, product_id, sale_date,
       NTH_VALUE(product_id, 3) OVER (
         PARTITION BY quarter
         ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_product_sold
FROM sales_data;

9. 자주 묻는 질문(FAQ)

Q1. NTH_VALUE()는 모든 행에 동일한 값을 반환하나요?

A. 윈도 프레임 설정에 따라 모든 행에 동일한 값 또는 현재 행 기준 값이 반환됩니다.

Q2. N이 범위를 초과하면 어떻게 되나요?

A. N번째 값이 존재하지 않으면 NULL을 반환합니다.

Q3. 숫자 외의 칼럼에도 적용 가능한가요?

A. 예, NTH_VALUE()는 숫자, 문자열, 날짜 등 모든 데이터 타입에 사용할 수 있습니다.

10. 요약

NTH_VALUE()는 Oracle 분석 함수 중에서도 매우 유용한 기능으로, N번째 행의 값을 추출할 때 필수적인 도구입니다. 특히 고객 행동 분석, 순위 기반 분석, 타임라인 기준 데이터 추출에 효과적이며, 정확한 윈도 프레임 설정이 핵심입니다.

출처

• Oracle 공식 문서: https://docs.oracle.com/en/database/
• Oracle Live SQL 실습: https://livesql.oracle.com
• Stack Overflow SQL 분석 함수 관련 토론
• SQL Tutorials from Mode Analytics