LAG, LEAD (다른 행의 값을 참조)
- – LAG (컬럼명[,offset][,default]) : 현재 ROW 기준으로 이전 행을 참조
- – LEAD (컬럼명[,offset][,default]) : 현재 ROW 기준으로 다음 행을 참조
아래 예제는 이전 row(S_1), 다음 row(S_2), 다음 두 번째 row(S_3)의 급여를 조회하는 예제이다. 다음 두 번째 row(S_3) 값이 NULL인 경우 0을 출력한다.
SELECT deptno, empno, sal
, LAG (sal) OVER(ORDER BY deptno, empno) s_1
, LEAD(sal) OVER(ORDER BY deptno, empno) s_2
, LEAD(sal, 2, 0) OVER(ORDER BY deptno, empno) s_3
FROM emp
;
-- S_1 : 이전 ROW 의 급여 값을 출력한다.
-- S_2 : 다음 ROW 의 급여 값을 출력한다.
-- S_3 : 다음 두 번째 ROW의 급여 값을 출력한다. 만약 값이 없으면 0을 출력한다.
DEPTNO EMPNO SAL S_1 S_2 S_3
-------- ---------- ---------- ---------- ---------- ----------
10 7782 2450 5000 1300
10 7839 5000 2450 1300 800
10 7934 1300 5000 800 2975
20 7369 800 1300 2975 3000
20 7566 2975 800 3000 1100
20 7788 3000 2975 1100 3000
20 7876 1100 3000 3000 1600
20 7902 3000 1100 1600 1250
30 7499 1600 3000 1250 1250
30 7521 1250 1600 1250 2850
30 7654 1250 1250 2850 1500
30 7698 2850 1250 1500 950
30 7844 1500 2850 950 0
30 7900 950 1500 0
LAG, LEAD 함수 사용시 ORDER BY 절은 필수 입력사항 이다.
RATIO_TO_REPORT (점유율)
RATIO_TO_REPORT 함수는 합에 대한 값의 비율을 계산한다. 아래 예제를 통해서 RATIO_TO_REPORT 함수에 대해 이해해보자
SELECT deptno
, SUM(sal) sal
, SUM(SUM(sal)) OVER() tot
, ROUND(SUM(sal) / SUM(SUM(sal)) OVER(), 2) rat1
, ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(), 2) rat2
FROM emp
GROUP BY deptno
;
DEPTNO SAL TOT RAT1 RAT2
------- ---------- ---------- ---------- ----------
30 9400 29025 .32 .32
20 10875 29025 .37 .37
10 8750 29025 .3 .3
위 예제에 대해서 좀 더 설명하면 아래와 같다.
- – SAL : 부서별 급여 합계를 집계함수를 사용하여 계산한 결과이다.
- – TOT : SUM 분석함수를 이용하여 부서별 급여 합계의 전체 합계를 계산한 결과이다.
- – RAT1 : 집계함수를 이용해서 계산한 부서별 합계에서 분석함수를 이용해 계산한 전체 합계를 나누어 비율을 계산한 결과이다.
- – RAT2 : RATIO_TO_REPORT 분석함수를 사용하여 부서별 급여 합계의 비율을 계산한 결과이다.
집계함수와 분석함수의 중첩 사용
- – 분석함수는 GROUP BY 보다 나중에 실행된다.
- – 따라서 GROUP BY의 결과Set을 대상으로 분석함수 사용이 가능하다.
- – 반대로 분석함수를 집계함수 안에서 사용할 수는 없다. 예) SUM( SUM(sal) OVER() ) 에러발생
FIRST_VALUE, LAST_VALUE
- – FIRST_VALUE : 정렬된 값 중에서 첫 번째 값을 반환한다.
- – LAST_VALUE : 정렬된 값 중에서 맨 마지막 값을 반환한다.
아래 예제는 사원순 정렬의 첫번째 사원 급여(F_EMP)와 마지막 사원 급여(L_EMP)를 조회하는 예이다.
SELECT empno
, sal
, FIRST_VALUE(sal) OVER(ORDER BY empno) f_emp
, LAST_VALUE (sal) OVER(ORDER BY empno) l_emp
FROM emp;
EMPNO SAL F_EMP L_EMP
------ ---------- ---------- ----------
7369 800 800 800
7499 1600 800 1600
7521 1250 800 1250
7566 2975 800 2975
7654 1250 800 1250
7698 2850 800 2850
7782 2450 800 2450
7788 3000 800 3000
7839 5000 800 5000
7844 1500 800 1500
7876 1100 800 1100
7900 950 800 950
7902 3000 800 3000
7934 1300 800 1300
ORDER BY 사용시 분석함수의 기본 계산 범위는 처음부터 현재까지 이다.
LAST_VALUE 직전값 대체
LAST_VALUE 사용시 값이 없을 경우 이전 값을 참조하는 예제이다.
WITH t AS
(
SELECT 1 no, 10 v FROM dual
UNION ALL SELECT 2, Null FROM dual
UNION ALL SELECT 3, Null FROM dual
UNION ALL SELECT 4, 20 FROM dual
UNION ALL SELECT 5, 10 FROM dual
UNION ALL SELECT 6, Null FROM dual
UNION ALL SELECT 7, Null FROM dual
UNION ALL SELECT 8, Null FROM dual
UNION ALL SELECT 9, 40 FROM dual
)
SELECT no, v
, NVL(v, LAG(v) OVER(ORDER BY no)) v1
, LAST_VALUE(v) OVER(ORDER BY no) v2
, LAST_VALUE(v IGNORE NULLS) OVER(ORDER BY no) v3
FROM t
;
-- V3 컬럼의 경우 모든 값이 조회되는 것을 확인 할 수 있다.
NO V V1 V2 V3
----- ---------- ---------- ---------- ----------
1 10 10 10 10
2 10 10
3 10
4 20 20 20 20
5 10 10 10 10
6 10 10
7 10
8 10
9 40 40 40 40
IGNORE NULLS : NULL 을 제외한 마지막 값