【오라클(Oracle) SQL 함수】 NTH_VALUE 함수

o NTH_VALUE 함수

 

---

문 법

 

 

---

목 적

NTH_VALUE 함수는 analytic_clause 절에 정의된 창(window) 내에서 n번째 행의 measure_expr 값을 반환한다. 반환되는 값의 데이터 타입은 measure_expr의 타입을 따른다.

 

• {RESPECT | IGNORE} NULLS는 measure_expr의 null 값을 계산에 포함할지 또는 제외할지 여부를 결정한다. 기본값은 RESPECT NULLS 이다.
• n은 측정값 중에서 반환할 행의 순서(n번째 행)을 결정한다. n에 지정할 수 있는 것은 양의 정수로 계산되는 상수, 바인드 변수, 열 또는 이들을 포함하는 표현식 중에서 사용 할 수 있다. 이 함수는 데이터 소스 창에 포함된 행의 개수가 n보다 적으면 NULL을 반환한다. n이 null이면 함수는 오류를 반환한다.
• FROM {FIRST | LAST}는 개별 창(windows) 내에서 계산을 첫 번째 행에서 시작할지, 아니면 마지막 행에서 시작할지를 결정한다. 기본값은 FROM FIRST 인수이다.

analytic_clause 절의 windowing_clause 절을 생략하는 경우, 기본값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 가 된다. 이 기본값은 "NTH_VALUE ... FROM LAST ..." 에서 때때로 예상치 못한 값을 반환하는 경우가 있는데, 이는 창의 마지막 값이 창의 맨 아래에 고정되어 있지 않고 현재 행이 변경됨에 따라 계속 변하기 때문이다. 따라서, NTH_VALUE ... FROM LAST ... 함수를 사용할 때에는 원하는 결과를 얻기 위해 windowing_clause를 명시적으로 지정하는 것이 좋다. 올바른 결과를 얻으려면 windowing_clause 절을 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING으로 지정하시오. 또는 windowing_clause 절을 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING으로 지정할 수도 있다.

[예기치 않은 결과 반환 예제]

Oracle Program

SELECT DEPTNO, 
       SAL,
       NTH_VALUE(SAL, 2) FROM LAST OVER (ORDER BY SAL DESC) AS nth_value_2
FROM EMP;

 

Results

DEPTNO	SAL	NTH_VALUE_2
-----------------------------
10	5000	 - 
20	3000	3000
20	3000	3000
20	2975	3000
30	2850	2975
10	2450	2850
30	1600	2450
30	1500	1600
10	1300	1500
30	1250	1250
30	1250	1250
20	1100	1250
30	950	1100
20	800	950

 

---

 

Oracle Program

SELECT DEPTNO, 
       SAL,
       NTH_VALUE(SAL, 2) FROM LAST OVER (ORDER BY SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS nth_value_2
FROM EMP;

 

Results

DEPTNO	SAL	NTH_VALUE_2
-------------------------------
10	5000	950
20	3000	950
20	3000	950
20	2975	950
30	2850	950
10	2450	950
30	1600	950
30	1500	950
10	1300	950
30	1250	950
30	1250	950
20	1100	950
30	950	950
20	800	950

 

---

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

• NTH_VALUE Oracle Function 프로그래밍 비교

 

---

예 제

다음 예제에서는 상품 ID(prod_id) 13~16 사이의 각 제품에 대해, 채널(channel_id) 그룹 기준으로 최소 판매액 (amount_sold) 을 계산한 후, 상품 ID 그룹 내에서 집계된 2번째 최소 판매액을 출력한다.

Oracle Program

SELECT prod_id, channel_id, MIN(amount_sold),
       NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id
                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) nv
  FROM  sales
  WHERE prod_id BETWEEN 13 and 16
  GROUP BY prod_id, channel_id;

 

Results

   PROD_ID CHANNEL_ID MIN(AMOUNT_SOLD)         NV
---------- ---------- ---------------- ----------
        13          2           907.34      906.2
        13          3            906.2      906.2
        13          4           842.21      906.2
        14          2          1015.94    1036.72
        14          3          1036.72    1036.72
        14          4           935.79    1036.72
        15          2           871.19     871.19
        15          3           871.19     871.19
        15          4           871.19     871.19
        16          2           266.84     266.84
        16          3           266.84     266.84
        16          4           266.84     266.84
        16          9            11.99     266.84

13 rows selected.

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

 

Image Creator 이미지