[Oracle] 오라클 연속된 값 사이의 차이 계산 방법

오라클 SQL에서 연속된 값 사이의 차를 계산할 때는 LAG 분석함수를 사용하면 된다. 이러한 쿼리를 작성할 때는 LAG 또는 LEAD 함수와 같은 분석 함수를 사용하여 이전 또는 다음 행의 값을 참조할 수 있다. 분석함수는 OVER 절과 함께 사용하며, OVER 절 내부의 ORDER BY 조건으로 데이터를 정렬 후 연속된 값의 차이를 계산한다. 칼럼 값에 NULL이 포함되어 있을 경우 차이를 계산하지 못한다. 이를 방지하기 위해 NVL 함수 등을 사용하여 NULL 값을 0으로 치환하여 계산하는 것이 좋다.

 

목차 연속된 값 사이의 차이 계산 방법 연속된 값 사이의 차이 계산 시 주의할 점

 

연속된 값 사이의 차이 계산 방법

SELECT empno
     , ename
     , job
     , hiredate
     , sal
     , LAG(sal) OVER (ORDER BY hiredate) AS prev_sal
     , sal - LAG(sal) OVER (ORDER BY hiredate) AS sal_diff
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 ORDER BY hiredate

 

 

LAG(sal) OVER (ORDER BY hiredate)

OVER절 내부의 ORDER BY 절의 칼럼으로 데이터를 정렬 후 LAG 함수를 사용하여 이전행의 값을 가져온다.

 

※ 다음행의 값과 비교하고 싶을 때는 LAG 함수 대신 LEAD 함수를 사용하면 된다.

 

이전 행을 판단할 때는 OVER 절 내부의 ORDER BY로 정렬된 데이터 기준인 것을 유의해야 한다. WHERE 하단에 ORDER BY 절의 정렬 조건이 OVER 절의 정렬 조건과 틀리다면 연속된 행의 차이 계산이 뒤섞여 보일 수 있다.

 

sal - LAG(sal) OVER (ORDER BY hiredate) 

현재 행의 급여(sal)에서 이전 행의 급여(sal)  값을 빼서 차이를 계산한다.

 

연속된 값 사이의 차이 계산 시 주의할 점

SELECT empno
     , ename
     , job
     , hiredate
     , comm
     , LAG(NVL(comm, 0)) OVER (ORDER BY hiredate) AS prev_comm
     , NVL(comm, 0) - LAG(NVL(comm, 0)) OVER (ORDER BY hiredate) AS comm_diff
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 ORDER BY hiredate

 

 

현재 행 또는 이전 행의 값이 NULL인 경우 빼기 연산이 안되기 때문에 NULL이 반환된다.

오라클에서는 NULL 값과 수치 값을 사용한 사칙연산이 불가능하다.

 

 

 

NULL 값이 존재하더라도 차이를 계산하기 위해서는 NVL 함수를 사용하여 NULL을 0으로 치환 후 계산하면 된다.

사칙연산을 할 때는 항상 칼럼의 값에 NULL이 존재할 수 있는지 주의해서 쿼리문을 작성해야 한다.

예제쿼리(테이블포함).txt

0.00MB

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

[Oracle] 오라클 PARTITION BY 사용법 정리 (분석함수)

[Oracle] 오라클 NVL, NVL2 함수 사용법