1.5. 기타 함수

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 을 제외한 마지막 값

출처 : http://www.gurubee.net/lecture/2675