본문 바로가기
ORACLE/SQL

[ORACLE] LEAD LAG 앞뒤 데이터 출력 :: 마이자몽

by 마이자몽 🌻♚ 2020. 4. 4.

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, 20) OVER(ORDER BY EMPNO) AS PREV_SAL
    ,SAL
    ,LEAD(SAL, 20) OVER(ORDER BY EMPNO) AS NEXT_SAL
FROM EMP
ORDER BY EMPNO;
 
 

이전에는 찾는 데이터가 없는 경우에 NULL값을 반환했습니다. 3번째 인자로 데이터가 없을 시 출력할 결과를 설정할 수 있습니다.

 

 

관련글 참조

분석함수란? OVER 분석절

 

[Oracle] OVER ... PARTITION BY 분석절 ANALYTIC FUNCTION :: 마이자몽

사원 정보를 입력하는데, 전체 급여 평균값과 함께 출력하시오. ORACLE SCOTT 계정 EMP 테이블로 출력된 결과입니다. 위 문제를 풀기 위해 어떻게 쿼리를 작성하실건가요? 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO..

myjamong.tistory.com

 

 

 

 

댓글0