[Oracle] 오라클 LEAD, LAG 함수(이전 행, 다음 행 값)

• 이전 행 데이터 리턴
LAG(컬럼명 [,offset] [,default]) OVER([PARTITION BY 그룹컬럼명] ORDER BY 정렬컬럼명)

• 다음 행 데이터 리턴
LEAD(컬럼명 [,offset] [,default]) OVER([PARTITION BY 그룹컬럼명] ORDER BY 정렬컬럼명)

-offset: 값을 가져올 행의 위치(기본 값: 1)
-default: 값이 없을 경우 출력할 기본 값(생략 시, null)

✅ 예시

SELECT yyyy
      ,pin
      ,start_date
      ,end_date   
      ,LAG(end_date) OVER(ORDER BY yyyy, pin, start_date) AS r_end_date --(실제)이전 근무 종료일
      ,LEAD(start_date) OVER(ORDER BY yyyy, pin, start_date) AS r_next_start_date --(실제)다음 근무 시작일
FROM employees 
AND yyyy = ‘2022’

 

yyyy	pin	start_date	end_date	r_end_date	r_start_date
2022	9501262250030	20220207	20220331	null	20220401
2022	9501262250030	20220401	20221231	20220331	20220829
2022	9208191210010	20220829	20220906	20221231	20220913
2022	9208191210010	20220913	20220914	20220906	20221207
2022	9208191210010	20221207	20221231	20220914	null

✅ 응용

--연속적으로 근무한 직원들의 pin 번호 조회
SELECT z.pin
FROM (
       SELECT y.*
             ,DENSE_RANK() OVER(ORDER BY y.pin) AS grp
       FROM (
              SELECT x.yyyy
                    ,x.pin
                    ,x.start_date
                    ,x.end_date
                    ,(CASE WHEN x.c_past_end_date = LAG(x.end_date) OVER(ORDER BY x.yyyy, x.pin, x.start_date) 
                           THEN 1 ELSE 0 END) AS chk1 --(예측)이전 근무 종료일과 (실제)이전 근무 종료일의 값 비교
                    ,(CASE WHEN x.c_next_start_date = LEAD(x.start_date) OVER(ORDER BY x.yyyy, x.pin, x.start_date)
                           THEN 1 ELSE 0 END) AS chk2 --(예측)다음 근무일과 (실제)다음 근무 시작일 값 비교
              FROM (
                     SELECT yyyy
                           ,pin
                           ,start_date --근무 시작일
                           ,end_date --근무 종료일
                           ,to_char(to_date(start_date, ‘YYYYMMDD’) - 1) As c_past_end_date --(예측)이전 근무 종료일
                           ,to_char(to_date(end_date, ‘YYYYMMDD’) + 1) AS c_next_start_date --(예측)다음 근무 시작일
                     FROM employees
                     AND yyyy = ‘2022’
                   ) x
            ) y
       WHERE ((y.chk1 = 1 AND y.chk = 0) 
               OR (y.chk1 = 1 AND y.chk = 1)
               OR (y.chk1 = 0 AND y.chk = 1))
     ) z
GROUP BY z.yyyy, z.pin, z.grp