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

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 분석함수를 사용하여 부서별 급여 합계의 비율을 계산한 결과이다.

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

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