[Oracle] NTH_VALUE 함수 (특정 행의 값 가져오기)

오라클에서 n번째 행의 값을 가져오기 위해서는 NTH_VALUE 함수를 사용하면 된다. NTH_VALUE 함수는 오라클 11g R2부터 사용할 수 있는 분석함수이다. 이전 오라클 버전에서는 FIRST_VALUE, LAST_VALUE, LAG, LEAD, WINDOWING절 등을 활용하여 특정 행의 값을 가져올 수 있다.

 

NTH_VALUE(칼럼, 위치)

 

조회된 전체 행에서 특정 행의 값 가져오기

SELECT empno
     , ename
     , job
     , sal
     , NTH_VALUE(sal, 3) OVER(ORDER BY sal DESC 
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS sal_nv
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

위의 예제는 조회된 전체 행에서 3번째 행의 급여(sal)를 표시하는 예제이다.

NTH_VALUE 함수는 분석함수 이므로 OVER 절과 함께 사용해야 한다. 

 

WINDOWING 절을 함께 사용해야 조회된 전체 행에서 특정행의 값을 모두 표시할 수 있으며, WINDOWING 절을 사용하지 않을 경우의 예제는 마지막 2번째 예제를 참고하면 된다.

 

그룹별로 특정 행의 값 가져오기

SELECT empno
     , ename
     , job
     , sal
     , NTH_VALUE(sal, 3) OVER(PARTITION BY job ORDER BY sal DESC 
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS sal_nv
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

PARTITION BY 절을 사용하면 그룹별로 행의 위치를 지정할 수 있다.

 

뒤에서부터 행 위치를 검색하는 방법 (FIRST | LAST)

SELECT empno
     , ename
     , job
     , sal
     , NTH_VALUE(sal, 3) FROM FIRST OVER(ORDER BY sal DESC 
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS nv_first     
     , NTH_VALUE(sal, 3) FROM LAST OVER(ORDER BY sal DESC 
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS nv_last
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

FROM FIRST 키워드는 기본값이기 때문에 생략이 가능하며, FROM LAST 키워드를 사용하면 조회된 전체 행의 뒤에서부터 지정한 행의 위치를 검색한다.

 

WINDOWING 절을 사용하지 않을 경우 주의 사항

SELECT empno
     , ename
     , job
     , sal
     , NTH_VALUE(sal, 1) OVER(ORDER BY sal DESC) AS sal_nv1
     , NTH_VALUE(sal, 3) OVER(ORDER BY sal DESC) AS sal_nv3
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')

 

 

WINDOWING 절을 사용하지 않을 경우 현재 행까지 기준으로 특정 행을 검색하므로, 지정한 행이 이후의 행이면 표시하지 못한다. 1번째, 2번째 행은 3번째 행의 값은 표시하지 못한다.

 

NULL을 제외하고 행의 위치를 찾는 방법

WITH temp_tab AS (
  SELECT 1 AS t_no, 100  AS t_val FROM dual UNION ALL
  SELECT 2 AS t_no, 200  AS t_val FROM dual UNION ALL
  SELECT 3 AS t_no, NULL AS t_val FROM dual UNION ALL 
  SELECT 4 AS t_no, 400  AS t_val FROM dual
)

SELECT t_no
     , t_val
     , NTH_VALUE(t_val, 3) OVER(ORDER BY t_no 
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS nv1
     , NTH_VALUE(t_val, 3) IGNORE NULLS OVER(ORDER BY t_no 
                              ROWS BETWEEN UNBOUNDED PRECEDING 
                                       AND UNBOUNDED FOLLOWING) AS nv2
  FROM temp_tab

 

 

NULL을 제외하고 특정 행의 위치를 찾고 싶을 때는 IGNORE NULLS 키워드를 사용하면 된다.

해당 값(t_val)이 NULL인 경우 ROW 카운터에 포함하지 않는다.

예제쿼리(테이블포함).txt

0.00MB

[Oracle] 오라클 WINDOWING 절 사용법

[Oracle] FIRST_VALUE, LAST_VALUE 함수 사용법

[Oracle] LAG, LEAD 함수 사용법 (이전값, 다음값)