오라클 분석함수 lag(), lead()

다. 예제

SELECT LEVEL LVL
     , LAG(LEVEL) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE1
     , LAG(LEVEL, 2) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE2
     , LAG(LEVEL, 3, 100) OVER(ORDER BY LEVEL ASC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;
       LVL LAG_VALUE1 LAG_VALUE2 LAG_VALUE3
---------- ---------- ---------- ----------
         1                              100
         2          1                   100
         3          2          1        100
         4          3          2          1
         5          4          3          2
         6          5          4          3
         7          6          5          4
         8          7          6          5
         9          8          7          6
        10          9          8          7

SELECT LEVEL LVL
     , LAG(LEVEL, 1, 100) OVER(ORDER BY LEVEL DESC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;
       LVL LAG_VALUE3
---------- ----------
        10        100
         9         10
         8          9
         7          8
         6          7
         5          6
         4          5
         3          4
         2          3
         1          2

2. LEAD() OVER()

가. 지정한 컬럼의 뒤의 값을 가져온다.

나. 문법

SELECT LEVEL LVL
     , LEAD(LEVEL) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE1
     , LEAD(LEVEL, 2) OVER(ORDER BY LEVEL ASC ) AS LAG_VALUE2
     , LEAD(LEVEL, 3, 100) OVER(ORDER BY LEVEL ASC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;

       LVL LAG_VALUE1 LAG_VALUE2 LAG_VALUE3
---------- ---------- ---------- ----------
         1          2          3          4
         2          3          4          5
         3          4          5          6
         4          5          6          7
         5          6          7          8
         6          7          8          9
         7          8          9         10
         8          9         10        100
         9         10                   100
        10                              100


SELECT LEVEL LVL
     , LEAD(LEVEL, 1, 100) OVER(ORDER BY LEVEL DESC) AS LAG_VALUE3
  FROM DUAL CONNECT BY LEVEL <= 10
;

       LVL LAG_VALUE3
---------- ----------
        10          9
         9          8
         8          7
         7          6
         6          5
         5          4
         4          3
         3          2
         2          1
         1        100