Oracle NTH_VALUE 설명 : 오라클 함수

1. 함수의 목적 

    Oracle NTH_VALUE는 analytic_clause구문의 정의안에서 measure_expr 파라미터의 n번째 행을 리턴한다. 

2. 샘플을 통한 개념 퀵뷰

employees테이블의 manager_id=100인 데이터는 아래와 같다.

manager_id=100인 직원중에 급여가 두번째로 낮은 직원의 월급을 nv에 표현한다.

select employee_id, manager_id, salary,
           nth_value(salary,2) over (partition by manager_id order by salary) nv
 from employees
where manager_id=100;

파라미터 n보다 낮은 등위에 있는 있는 데이터는 null로 반환한다
(샘플에서는 employee_id=124가 null로 반환되었다)

3. 사용방법   

4. 함수 PARAMETER 설명

[measure_expr]
파리미터 n번째의 값을 취할 기준 컬럼이다.

[n]
n은 ORDER BY에 의해 소팅된 집합에서 반환할 n번째 행을 결정한다.
n은 양수로 해석되는 한 상수, 바인드 변수, 열 또는 이들과 관련된 표현식이 될 수 있다.
 window에 의해 생성된 소스가 n 행 미만인 경우이 함수는 NULL을 리턴한다. ("2. 샘플을 통한 개념 퀵뷰 참조)
n이 null이면 함수는 오류를 리턴한다.

{RESPECT | IGNORE} NULLS
measure_expr 파라미터에서 null을 무시할지 말지를 결정한다. 
 RESPECT NULLS은 null값을 포함, IGNORE NULLS는 null값을 제외한다.

FROM {FIRST | LAST}
n번째 계산이 window 서브셋의 첫번째 혹은 마지막 행에서 시작되는지를 결정한다.
기본값은 FROM FIRST이다.

5. 다양한 샘플표현

example1) "2. 샘플을 통한 개념 퀵뷰"의 쿼리에  FROM LAST를 붙여보자.

select employee_id, manager_id, salary,
          nth_value(salary,2) FROM LAST over (partition by manager_id order by salary) nv
 from employees
 where manager_id=100;

nv컬럼이 계속 변한다. 어찌된 일일까?
변경된게 FROM LAST 키워드 넣은 것 밖에 없으니, FROM LAST가 이런 변화를 일으킨 거라 미뤄 짐작할 수 있겠지만, 자세히 설명해 본다.

1) 1행을 지나면서 window 모습은 이렇다. 뒤에서 부터 2번재 행은 없으니 null을 반환한다.

2) 2번째 행을 지나면서 window 모습은 이렇다.

FROM LAST이니 뒤에서 부터 두번째는 5800이다.
결과로 이렇게 표현된다.

3) 3번째 행을 지나면서 window 모습은 이렇다.

역시 뒤에서 부터 두번째 값이니 6500이 된다.
결과로 이렇게 표현된다.

4) 4번재 행을 지나면서 window 모습은 이렇다.

계속 한 row씩 늘어나고 있다. 이번에 뒤에서 두번째 끝에 값은? 7900이다.
결과로 이렇게 표현된다.

※ FROM FIRST는 WINDOW에 의해 훓어가면서 SUBSET이 만들어져도 첫행의 고정은 풀리지 않기 때문에 고정값이 들어가지만, FROM LAST는 행이 끝으로 계속 붙으면서 값이 계속 달라지는 점에 유의해야 하기 때문에 좀 자세히 설명했다.