[Oracle] LAG, LEAD 알아보기

LAG함수와 LEAD함수의 정의 및 구조

LAG 함수 : 자체 조인을 사용하지 않고 이전 행을 리턴하는 함수
LEAD 함수: 자체 조인을 사용하지 않고 다음 행을 리턴하는 함수

 

 

LAG(expression [, offset] [, default]) OVER ( [ query_partition_clause ] order_by_clause )

LEAD(expression [, offset] [, default]) OVER ( [ query_partition_clause ] order_by_clause )

 

expression: 값을 가져올 컬럼
offset: 값을 가져올 행의 위치. 생략 가능, 기본값은 1
default: 값이 없을 경우 리턴할 기본 값. 생략 가능
query_partition_clause: 그룹 컬럼명. 생략 가능
order_by_clause: 정렬 컬럼명

 

 

기본 사용법

예제에 사용 할 테이블은 게시판 테이블이며 데이터 및 구조는 아래 사진과 같습니다.

 

 

가장 먼저 필수 입력 값만 넣어서 조회해 보겠습니다. 각 게시글들의 앞, 뒷 게시글의 제목을 조회합니다.

SELECT board_id,
       category,
       title,
       LAG(title) OVER(ORDER BY board_id)  AS prev_title,
       LEAD(title) OVER(ORDER BY board_id) AS next_title
FROM board

결과

 

default값 넣기

1번 게시글과 6번 게시글의 NULL값이 거슬려서 default값을 넣어줍니다.

이때 offset요소를 넣지 않는다면 ORA-01722오류가 발생합니다.

SELECT board_id,
       category,
       title,
       LAG(title, 1, '이전 게시글이 존재하지 않습니다.') OVER(ORDER BY board_id)  AS prev_title,
       LEAD(title, 1, '다음 게시글이 존재하지 않습니다.') OVER(ORDER BY board_id) AS next_title
FROM board

결과

 

query_partition_clause 적용하기

위의 조회결과에서는 단순히 앞, 뒤의 게시글만 가져오지만 게시글의 카테고리 별로 앞, 뒤 게시글을 가져오고 싶을 때는 query_partition_clause 부분을 작성해줍니다.

SELECT board_id,
       category,
       title,
       LAG(title) OVER(PARTITION BY category ORDER BY board_id)  AS prev_title,
       LEAD(title) OVER(PARTITION BY category ORDER BY board_id) AS next_title
FROM board

그룹된 결과를 확실하게 볼 수 있도록 default값을 적용하지 않았습니다.

결과

3, 4번째 행을 보면 공지3의 next_title은 null이고 일반1의 prev_title도 null로 바뀐 것을 알 수 있습니다.

 

특정 행의 앞, 뒤 결과만 가지고 오기

SELECT *
FROM (SELECT board_id,
             category,
             title,
             LAG(title) OVER (PARTITION BY category ORDER BY board_id)  AS prev_title,
             LEAD(title) OVER (PARTITION BY category ORDER BY board_id) AS next_title
      FROM board)
WHERE board_id = 2

결과

 

아래와 같이 바로 WHERE절에 조건을 줄 경우 NULL로 조회되니 주의해야합니다.

SELECT board_id,
       category,
       title,
       LAG(title) OVER (PARTITION BY category ORDER BY board_id)  AS prev_title,
       LEAD(title) OVER (PARTITION BY category ORDER BY board_id) AS next_title
FROM board

결과

 

offset 적용해보기

공지1의 다다음번 게시글을 조회하는 쿼리입니다.

SELECT *
FROM (SELECT board_id,
             category,
             title,
             LAG(title) OVER (PARTITION BY category ORDER BY board_id)  AS prev_title,
             LEAD(title, 2) OVER (PARTITION BY category ORDER BY board_id) AS next_title
      FROM board)
WHERE board_id = 1

결과