오라클 분석함수 lag(), lead()
1. LAG() OVER()
가. 지정한 컬럼의 앞의 값을 가져온다.
나. 문법
다. 예제
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
최신 댓글