[MSSQL] 이전 행, 다음 행 값 가져오기 (LAG, LEAD)

 

SQL Server 2012부터 이전 행과 다음 행의 값을 가져올 수 있는 LAG, LEAD 함수가 추가되었다. LAG 함수를 사용하면 이전 행의 값과 현재 행의 값을 비교하여 값이 변경되었는지 쿼리상에서 쉽게 판별이 가능하다.

 

기본 사용법

■ 이전 행 값 (LAG)

SELECT empno  
     , ename
     , job
     , LAG(job) OVER(ORDER BY job, empno) AS job_prev
  FROM emp
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
 ORDER BY job, empno

LEAD 함수를 사용하여 이전 행의 값을 가져올 수 있다. WHERE 절 하단의 ORDER BY의 정렬 순서와 상관없이 OVER 절 내부의 ORDER BY 기준으로 정렬 후 이전 값을 가져오기 때문에, 되도록이면 ORDER BY를 동일하게 하는 것을 권장한다.

 

WHERE 절 하단의 ORDER BY를 생략할 경우 OVER 절 내부의 ORDER BY 기준으로 정렬되어서 조회된다.

 

■ 다음 행 값 (LEAD)

SELECT empno  
     , ename
     , job
     , LEAD(job) OVER(ORDER BY job, empno) AS job_next
  FROM emp
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
 ORDER BY job, empno

 

LEAD 함수를 사용하여 다음 행의 값을 가져올 수 있다.

 

고급 사용법

■ 가져올 행의 위치 지정

SELECT empno  
     , ename
     , sal
     , LAG(sal, 2) OVER(ORDER BY sal) AS sal_prev
     , LEAD(sal, 2) OVER(ORDER BY sal) AS sal_next
  FROM emp
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
 ORDER BY sal

 

LAG, LEAD 함수의 2번째 파라미터에 행의 위치를 입력하면 입력한 수치 값 행의 값을 가져온다.

 

LAG("컬럼명", "행의 위치")

LEAD ("컬럼명", "행의 위치")

 

■ NULL인 경우 대체 값 지정

SELECT empno  
     , ename
     , sal
     , LAG(sal, 2, 0) OVER(ORDER BY sal) AS sal_prev
     , LEAD(sal, 2, 0) OVER(ORDER BY sal) AS sal_next
  FROM emp
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
 ORDER BY sal

가져올 행이 없어서 NULL인 경우 대체 값을 지정할 수 있다. 실제 값이 NULL인 경우는 대체 값으로 바뀌지 않고 NULL로 표시된다.

 

LAG("컬럼명", "행의 위치", "대체 값")

LEAD ("컬럼명", "행의 위치", "대체 값")

 

■ 그룹별 이전 행, 다음 행 값 가져오기

SELECT empno  
     , ename
     , job
     , sal
     , LAG(sal) OVER(PARTITION BY job ORDER BY job, sal) AS sal_prev
     , LEAD(sal) OVER(PARTITION BY job ORDER BY job, sal) AS sal_next
  FROM emp
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
 ORDER BY job, sal

PARTITION BY 절을 사용하면 해당 그룹 내부에서 이전 행 및 다음 행의 값을 가져올 수 있다. 그룹(JOB)이 바뀌면 값을 참조하지 못하므로 NULL로 표시된다.

 

참조 : https://gent.tistory.com/432?category=874679