순차 함수
* 순차함수는 순차에 해당하는 값을 가져오는 함수다.
| FIRST_VALUE 함수
FIRST_VALUE 함수는 윈도우 첫 행의 expr을 반환한다. IGNORE NULLS 키워드를 기술하면 널이 무시된다.
FIRST_VALUE(expr)[IGNORE NULLS] OVER(analytic_clause)
ex 1 ) EMP 테이블에서 부서번호가 30이고, 직업별로 입사를 가장 먼저한 사람의 급여를 구해라.
SELECT job,hiredate, sal
, FIRST_VALUE(sal) OVER(PARTITION BY job ORDER BY hiredate) AS c1
FROM emp
WHERE deptno = 30
ORDER BY 1, 2;
- 위 결과값을 보면 부서번호가 30인 CLERK, MANAGER, SALESMAN (JOB별) 각 직업별 입사순으로 정렬하여 가장 먼저 일찍 입사한 직원의 급여를 구해서 C1으로 출력하였다.
ex 2 )
SELECT sal, comm
, FIRST_VALUE(comm) OVER(ORDER BY sal) AS c1
, FIRST_VALUE(comm) IGNORE NULLS OVER(ORDER BY sal) AS c2
, FIRST_VALUE(comm) IGNORE NULLS OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS c3
FROM emp
WHERE deptno = 30
ORDER BY 1, 2;
- C1은 SAL순으로 COMM의 가장 먼저 오는 값을 출력했다.(NULL 출력) 그리고 C2는 IGNORE NULLS 옵션을 사용하여 NULL 다음 500을 출력하였고, C3도 C2와 마찬가지로 IGNORE NULLS를 사용하여 NULL 다음 500을 출력하였다.
| LAST_VALUE 함수
LAST_VALUE 함수는 윈도우 끝 행의 expr을 반환한다.
LAST_VALUE(expr)[IGNORE NULLS] OVER(analytic_clause)
ex )
SELECT job, hiredate, sal
, LAST_VALUE(sal) OVER(PARTITION BY job ORDER BY hiredate) AS c1
, LAST_VALUE(sal) OVER(PARTITION BY job ORDER BY hiredate
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c2
FROM emp
WHERE deptno = 30
ORDER BY 1, 2, 3;
- C1과 C2는 JOB별 HIREDATE 순으로 SAL 끝 값을 출력한다. 다른 점이라고 하면 C1은 윈도우 범위를 주지 않아서 HIREDATE순으로 SAL 값이 마지막값이 되어 C1에 출력되는 것을 확인할 수 있고, C2는 윈도우 범위를 현재 행부터 끝까지 범위를 주었기 때문에 SALESMAN과 같은 경우에는 윈도우 맨 끝 값 1250 이 출력되는 것을 확인할 수 있다.
| NTH_VALUE 함수
NTH_VALUE 함수는 윈도우 n번째 행의 measure_expr을 반환한다. FIRST는 윈도우의 첫 행, LAST는 윈도우의 끝 행부터 검색을 시작한다. 기본값은 FIRST다. NTH_VALUE 함수는 11.1버전부터 사용할 수 있다.
NTH_VALUE(measure_expr, n)[FROM {FIRST| LAST}][IGNORE NULLS] OVER(analytic_clause)
ex 1 )
SELECT hiredate, sal
, NTH_VALUE(sal, 1) OVER(ORDER BY hiredate) AS c1
, NTH_VALUE(sal, 3) OVER(ORDER BY hiredate) AS c2
FROM emp
WHERE deptno = 30
ORDER BY hiredate;
ex 2 )
SELECT hiredate, sal
,① NTH_VALUE(sal, 3) FROM LAST OVER(ORDER BY hiredate RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1
,② NTH_VALUE(sal, 3) OVER(ORDER BY hiredate DESC) AS c2
FROM emp
WHERE deptno = 30
ORDER BY 1;
① NTH_VALUE(sal, 3) FROM LAST OVER(ORDER BY hiredate RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS c1 : hiredate열을 기준으로 오름차순으로 정렬하고 현재 범위부터 맨 끝 범위까지 윈도우를 지정하여 sal열의 뒤에서 3번째 행의 값을 불러온다. 81/09/28 부터는 현재 범위에 뒤에서 3번째 행이 포함되어 있지 않으므로 NULL 값이 나온다.
② NTH_VALUE(sal, 3) OVER(ORDER BY hiredate DESC) AS c2 : hiredate 열 기준으로 내림차순으로 정렬하고 sal 의 3번째 행의 값을 가져오기 때문에 c1과 동일한 값을 불러온다.
'SQL > 분석함수' 카테고리의 다른 글
[SQL]분석함수(analytic function)_KEEP 키워드 (0) | 2020.09.19 |
---|---|
[SQL]분석함수(analytic function)_LAG, LEAD, LISTAGG (0) | 2020.09.19 |
[SQL]분석함수(analytic function)_분포함수(PERCENTILE_CONT, PERCENTILE_DISC, MEDIAN) (0) | 2020.09.19 |
[SQL]분석함수(analytic function)_순위함수(NTILE, CUME_DIST, PERCENT_RANK, RATIO_TO_REPORT) (0) | 2020.09.19 |
[SQL]분석함수(analytic function)_순위함수(RANK, DENSE_RANK, ROW_NUMBER) (0) | 2020.09.19 |