[ORACLE] LEAD LAG 앞뒤 데이터 출력

ORACLE 앞뒤 데이터 출력

ORACLE SCOTT 계정 EMP테이블을 이용해서 사원 번호로 정렬된 데이터에서 앞 뒤 급여와 함께 출력하시오.

JOIN 풀이

앞뒤 데이터를 출력하기 위해서 어떻게 쿼리를 작성해야할까요? 분석함수를 사용하지 않고 결과를 출력하기 위해서는 3개의 테이블을 조인하는 방법이 있습니다. 같은 테이블에 ROWNUM 값을 변경하여 3개의 테이블을 조인시켜 값을 얻을 수 있습니다.

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
32

SELECT
    EMPNO
    ,ENAME
    ,PREV.SAL AS PREV
    ,CURR.SAL AS CURR
    ,NEXT.SAL AS NEXT
FROM
    (
        SELECT
            ROWNUM + 1 AS SEQ
            ,SAL
        FROM EMP
    ) PREV
    RIGHT OUTER JOIN
    (
        SELECT
            ROWNUM AS SEQ
            ,EMPNO
            ,ENAME
            ,SAL
        FROM EMP
    ) CURR
    ON PREV.SEQ = CURR.SEQ
    LEFT OUTER JOIN
    (
        SELECT
            ROWNUM - 1 AS SEQ
            ,SAL
        FROM EMP
    ) NEXT
ON CURR.SEQ = NEXT.SEQ
ORDER BY EMPNO;

위의 이미지처럼 이전 데이터를 보여주기 위한 테이블은 ROWNUM에 1을 더한 값을, 이후 데이터를 보여주기 위해 ROWNUM에 1을 뺀값으로 한행씩 밀려서 결과를 얻을 수 있었습니다. 간단한 작업 같지만 비교적 긴 쿼리문을 작성해야했습니다. ORACLE에서는 LEAD와 LAG 분석함수를 사용해서 간단하게 위의 작업을 할 수 있습니다.

LEAD LAG 풀이

1
2
3
4
5
6
7
8

SELECT
    EMPNO
    ,ENAME
    ,LAG(SAL) OVER(ORDER BY EMPNO) AS PREV_SAL
    ,SAL
    ,LEAD(SAL) OVER(ORDER BY EMPNO) AS NEXT_SAL
FROM EMP
ORDER BY EMPNO;
 

위의 긴 풀이를 LEAD와 LAG 분석함수를 이용해서 쉽게 해결할 수 있습니다.

LEAD LAG 분석함수

LEAD : 칼럼내의 이후 데이터를 출력
LAG : 칼럼내의 이전 데이터 출력

기본적으로 LEAD와 LAG 함수의 사용 용도입니다. 두 함수에는 몇가지 기능이 더 있습니다.

1
2
3
4
5
6
7
8

SELECT
    EMPNO
    ,ENAME
    ,LAG(SAL, 2) OVER(ORDER BY EMPNO) AS PREV_SAL
    ,SAL
    ,LEAD(SAL, 2) OVER(ORDER BY EMPNO) AS NEXT_SAL
FROM EMP
ORDER BY EMPNO;
 

인자로 칼럼만 넣었을 때는 바로 이전 이후 데이터만 출력했습니다. 두번째 인자로 숫자를 넣어 줄 수 있는데, 이전 이후로 몇번째 데이터를 보여줄것인지 지정할 수 있습니다.

1
2
3
4
5
6
7
8
9

SELECT
    EMPNO
    ,ENAME
    ,LAG(SAL, 2, 0) OVER(ORDER BY EMPNO) AS PREV_SAL
    ,SAL
    ,LEAD(SAL, 2, 0) OVER(ORDER BY EMPNO) AS NEXT_SAL
FROM EMP
ORDER BY EMPNO;
 
 