[DB/SQL] ORACLE LAG,LEAD 함수 사용법 정리 (이전, 다음값)

 

 

 

 

 

오늘은 오라클 함수 중에 LAG, LEAD에 대해서 정리해보겠습니다.

 

LAG, LEAD란?

LAG, LEAD 함수는 행의 이전 값을 찾거나 다음행을 찾는 용도로 사용된다.

- LAG : 질의 결과 행의 이전 값을 찾을 때 사용
  LAG(EXPR [,OFFSET] [,DEFAULT]) OVER([PARTITION_BY_CLAUSE] ORDER_BY_CLAUSE)
- LEAD : 질의 결과 행의 이전 값을 찾을 때 사용
  LEAD(EXPR [,OFFSET] [,DEFAULT]) OVER([PARTITION_BY_CLAUSE] ORDER_BY_CLAUSE)

expr : 대상 컬럼명
offset : 값을 가져올 행의 위치 기본값은 1 (생략가능)
default : 값이 없을 경우 기본값 (생략가능)
partition_by_clause : 그룹 컬럼명 (생략가능)
order_by_clause : 정렬 컬럼명, 필수

 

 

테스트 데이터

CREATE TABLE emp 
( 
    EMP_ID      VARCHAR2(10) ,
    HIS_SEQ     NUMBER(4) ,
    NAME        VARCHAR2(10),
    ADR         VARCHAR2(200)
);

INSERT INTO EMP VALUES ('A00001','1','홍길동','서울 성동구 성수동');
INSERT INTO EMP VALUES ('A00001','2','홍길동','서울 강동구 천호동');
INSERT INTO EMP VALUES ('A00001','3','홍길동','서울 강동구 성내동');
INSERT INTO EMP VALUES ('A00002','1','김똥개','부산 동구');
INSERT INTO EMP VALUES ('A00002','2','김똥개','부산 중구');
INSERT INTO EMP VALUES ('A00002','3','김똥개','부산 해운대구');

SELECT * FROM EMP;

설명에 앞서 테스트 테이블과 데이터를 준비하였습니다. 

EMP_ID는 직원의 ID를 의미하며, HIS_SEQ는 변경이력을 뜻합니다.

홍길동과 김똥개 직원의 주소 변경이력을 보여주는 데이터입니다.

 

 

사용법

SELECT
      EMP.EMP_ID  
     ,EMP.HIS_SEQ
     ,EMP.NAME
     ,EMP.ADR
     ,LAG(EMP.ADR) OVER (PARTITION BY EMP.EMP_ID ORDER BY EMP.HIS_SEQ) AS BF_ADR
     ,LEAD(ADR) OVER (ORDER BY HIS_SEQ) AS AF_ADR
FROM  EMP
WHERE EMP_ID IN ('A00001')

현재행 기준, 변경이력(HIS_SEQ) 순으로 이전 행의 주소와 다음행 주소를 표시된 걸 확인 가능합니다.

 

SELECT
      EMP.EMP_ID  
     ,EMP.HIS_SEQ
     ,EMP.NAME
     ,EMP.ADR
     ,LAG(EMP.ADR,2,'이력없음') OVER (PARTITION BY EMP.EMP_ID ORDER BY EMP.HIS_SEQ) AS BF_ADR
FROM  EMP
WHERE EMP_ID IN ('A00001')

현재 행의 기준으로 2번째 이전 값을 표시한다.

홍길동 3번째 전 주소는 "서울 성동구 성수동" 표시함

가지고올 행의값(주소)가 없는 경우는 "이력없음" 표시함

 

SELECT
      EMP.EMP_ID  
     ,EMP.HIS_SEQ
     ,EMP.NAME
     ,EMP.ADR
     ,LAG(EMP.ADR) OVER (PARTITION BY EMP.EMP_ID ORDER BY EMP.HIS_SEQ) AS BF_ADR
     ,LEAD(EMP.ADR) OVER (PARTITION BY EMP.EMP_ID ORDER BY EMP.HIS_SEQ) AS AF_ADR
FROM  EMP
WHERE EMP_ID IN ('A00001','A00002')

 

파티션을 이용하여 그룹화한 칼럼 기준으로 이전 값, 다음 값을 표시한다.

 

*파티션 문법은 아래 글 확인

[DB/SQL] 오라클 PARTITION BY 정리 | 집계함수 사용 | 순위함수 사용

 

 

실무에서 개발하거나 자료추출하는 경우 빈번하게 사용하는 함수여서 정리하는 겸 적어보았습니다.

감사합니다.