[Oracle] Analytic Function LAG/LEAD

[출처] http://blog.naver.com/renovate/40007643531

PURPOSE

SCOPE & APPLICATION

KEY IDEA

ANALYTIC FUNCTION,PARTITION, WINDOW, CURRENT ROW, Lag, Lead, RATIO

SUPPOSITION

VERSION : ORACLE 8.1.6 이상

DESCRIPTION

기존 RDBMS에서는 다른 시간대에 있는 값들을 참조하기 위한 포인터를 가지고 있지 않다. 이것은 집합에서 모든 요소가 동등한 자격과 위치를 가지며 서로를 참조하기 위한 포인터를 가지고 있지 않기 때문이다.

Analytic Function의 Lag와 Lead는 다른 시간대에 있는 값들을 비교하기 위한 포인터를 제공하기 때문에 이러한 연산을 수행할 경우 아주 유리하다.

{LAG | LEAD}
(, [ [, ]]) OVER 
([PARTITION BY [,...]]
ORDER BY [collate clause>]
[ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])

은 옵션이며 지정하지 않으면 1 이 default로 사용된다.

다음 SQL은 Lag와 Lead Function을 사용하여 자신이 소속한 부서에서 자기보다 월급이 많은 사원의 급여와 자기보다 월급이 적은 사원의 급여를 참조하기 위한 것이다.

select deptno,empno,sal,
       lag(sal,1)   over (order by deptno,sal) as sal_lag,
       lead(sal,1) over (order by deptno,sal) as sal_lead
  from emp

<< Result Set >>

DEPTNO	EMPNO	SAL	SAL_LAG	SAL_LEAD
10	7934	100		2450
10	7782	2450	100	5000
10	7839	5000	2450	800
20	7369	800	5000	1100
20	7876	1100	800	2975
20	7566	2975	1100	3000
20	7788	3000	2975	3000
20	7902	3000	3000	950
30	7900	950	3000	1250
30	7521	1250	950	1250
30	7654	1250	1250	1500
30	7844	1500	1250	1600
30	7499	1600	1500	2850
30	7698	2850	1600

     

적용 예>

SELECT a.t_dt, SUM(NVL(b.in_usr_cnt,0)) icnt,
       NVL(lag(SUM(in_usr_cnt),1) over (ORDER BY a.t_dt, SUM(in_usr_cnt)),0) bcnt
  FROM TCOPY a,
       ST_JOIN_DD_TB b
 WHERE a.t_yy = b.st_yy(+)
   AND a.t_mm = b.st_mm(+)
   AND a.t_dd = b.st_dd(+)
   AND a.t_dt BETWEEN '20041101' AND '20041108'
 GROUP BY a.t_dt

====================================================================================================

<<< 추가 정보 >>>

[출처] http://energ.tistory.com/134

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.

If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.

You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.

[참고 ] Oracle 8.1.7 SQL reference [ LAG 함수 ]