LAG, LEAD (다른 행의 값을 참조)

LAG, LEAD (다른 행의 값을 참조)

• - LAG (컬럼명[,offset][,default]) : 현재 ROW 기준으로 이전 행을 참조
• - LEAD (컬럼명[,offset][,default]) : 현재 ROW 기준으로 다음 행을 참조

아래 예제는 이전 row(S_1), 다음 row(S_2), 다음 두 번째 row(S_3)의 급여를 조회하는 예제이다. 다음 두 번째 row(S_3) 값이 NULL인 경우 0을 출력한다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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 절은 필수 입력사항 이다.

분석: 순서대로 보기 바람

SELECT deptno, empno, sal

     , LAG (sal) OVER(ORDER BY deptno, empno) s_1 //1번 함수 고유의 기능대충이제 알겠다.

     , LEAD(sal) OVER(ORDER BY deptno, empno) s_2 //2번 다음 로우값을 출력.

     , LEAD(sal, 2, 0) OVER(ORDER BY deptno, empno) s_3 //3번 다음2번째 로우값을 출력 값이 없다면 0

  FROM emp

;

RATIO_TO_REPORT (점유율)

RATIO_TO_REPORT 함수는 합에 대한 값의 비율을 계산한다. 아래 예제를 통해서 RATIO_TO_REPORT 함수에 대해 이해해보자

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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() ) 에러발생

분석:순서대로 보기 바람.

SELECT deptno

     , SUM(sal) sal //DEPTNO부서별 합

     , SUM(SUM(sal)) OVER() tot //전체의 합...

     , ROUND(SUM(sal) /  SUM(SUM(sal)) OVER(), 2) rat1 //퍼센트 나오는데 소수2자리수까지 ROUND함수.

     , ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(), 2) rat2 //위와 결과는 동일함.. 소수2자리수 까지.

  FROM emp

 GROUP BY deptno

FIRST_VALUE, LAST_VALUE

• - FIRST_VALUE : 정렬된 값 중에서 첫 번째 값을 반환한다.
• - LAST_VALUE : 정렬된 값 중에서 맨 마지막 값을 반환한다.

아래 예제는 사원순 정렬의 첫번째 사원 급여(F_EMP)와 마지막 사원 급여(L_EMP)를 조회하는 예이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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 사용시 분석함수의 기본 계산 범위는 처음부터 현재까지 이다.

분석:순서대로 보기 바람

SELECT empno

     , sal

     , FIRST_VALUE(sal) OVER(ORDER BY empno) f_emp //1번 전체 로우중에서 처번째 로우 값

     , LAST_VALUE (sal) OVER(ORDER BY empno) l_emp //2번 전체 로우중 마지막 로우 값

  FROM emp;

LAST_VALUE 직전값 대체

LAST_VALUE 사용시 값이 없을 경우 이전 값을 참조하는 예제이다.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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

SELECT no, v

     , NVL(v, LAG(v) OVER(ORDER BY no)) v1 //1번 null일때 v의 이전 값을 읽어옴 결국엔 null도 나올수 있음

     , LAST_VALUE(v) OVER(ORDER BY no)  v2 //2번 다음 로우 값을 읽어옴 null도 나올수 있음

     , LAST_VALUE(v IGNORE NULLS) OVER(ORDER BY no) v3 //3번 이건 IGNORE NULLS 이것이 null을 제외한 마지막 값을 가르킴..

  FROM t

태그

• LEAD LAG FIRST_VALUE LAST_VALUE RATIO_TO_REPORT 분석함수

문서에 대하여

• - 강좌 URL : http://www.gurubee.net/lecture/2675
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.