SQL/분석함수

[SQL]분석함수(analytic function)_순차함수(FIRST_VALUE, LAST_VALUE, NTH_VALUE)

한기리 2020. 9. 19. 20:38
728x90
320x100

순차 함수

 

* 순차함수는 순차에 해당하는 값을 가져오는 함수다.

 

| 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과 동일한 값을 불러온다.

728x90
320x100