Oracle Advanced SQL 강좌
기타 함수 3 3 13,688

by 마농 LEAD LAG FIRST_VALUE LAST_VALUE RATIO_TO_REPORT 분석함수 [2014.01.14]


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

- 강좌 URL : http://www.gurubee.net/lecture/2675

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 정진우 [2014.01.23 15:47:16]
감사합니다 !

by 시그너스7000 [2014.02.17 13:19:56]

IGNORE NULLS는  order by no 순으로 조회했을때 null을 제외한 값 입니다

by 박민철 [2014.08.28 16:57:03]

좋은 강좌 감사합니다.

이용하게 활용할께요^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입