Oracle LAG 설명 : 오라클 함수

1. 함수의 목적 

   Oracle LAG는 현재 커서의 위치 이전의 위치에 대한 액세스를 제공한다. self 조인없이도 두 테이블 이상의 행에 동시에 액세스가 가능하다.

2. 샘플을 통한 개념 퀵뷰

employees 테이블의 각 구매 담당자에 대해 직전 고용 된 직원의 급여를 PREV_SAL컬럼으로 조회한다.

SELECT hire_date, last_name, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY hire_date;

3. 사용방법 

4. 함수 PARAMETER 설명

[value_expr]

어떤 컬럼의 LAG값을 반환할 것인지에 대한 정의를 넣는 입력 파라미터이다.

[offset]

현재 커서가 위치하고 있는 행의 이전 몇번재 행을 가르킬 건지를 세팅하는 파라미터로, 0보다 큰 숫자로 정의해야 한다. 정의하지 않으면 디폴트로 1이 들어간다.
예를 들어, 소팅된 행의 현재 위치가 10이라고 하면, 8번재 행을 액세스하고 싶다면 2를  offset으로 세팅한다.

[default]

offset의 위치가 widnow scope을 벗어날 경우 디폴트로 어떤 값을 반환할지 적어준다. 
좀 더 쉽게 설명하면,  window  scope이 벗어났다는 것은 윗행이 없다는 말과 동일하다.


[{RESPECT | IGNORE} NULLS]

value_expr에 null이 있을 경우 유지할 건지 무시할 것인지를 설정하는 파라미터다.
유지는 RESPECT NULLS, 무시는 IGNORE NULLS이다. 기본값은  REPECT  NULLS이다.

5. 다양한 샘플표현

example1) window scope이 벗어났을 경우 반환하는 값을 문자 설정할 수 있을까? 불가능하다.

SELECT A.*,
            LAG(F2,1,'왕초') OVER (ORDER BY F1) LAG_VALUE
FROM            
      (SELECT 1 F1, 1  F2 FROM DUAL
      UNION ALL
      SELECT 2 F1, NULL F2 FROM DUAL
      UNION ALL
      SELECT 3 F1, 3 F2 FROM DUAL) A
;     

ORA-01722: 수치가 부적합합니다
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

넣을 수 없다.

example2) window scope이 벗어났을 경우 반환하는 값을 null 설정할 수 있을까? 가능하다.

SELECT A.*,
            LAG(F2,1,null) OVER (ORDER BY F1) LAG_VALUE
FROM            
      (SELECT 1 F1, 1  F2 FROM DUAL
      UNION ALL
      SELECT 2 F1, NULL F2 FROM DUAL
      UNION ALL
      SELECT 3 F1, 3 F2 FROM DUAL) A
;     

        F1         F2     LAG_VALUE
---------- ---------- ----------
         1          1            (null)
         2        (null)             1
         3          3            (null)

example3) IGNORE NULLS 키워드를 넣어 NULL을 무시하게 만들면 어떻께 될까?

SELECT A.*,
            LAG(F2,1,10) IGNORE NULLS OVER (ORDER BY F1) LAG_VALUE
FROM            
      (SELECT 1 F1, 1  F2 FROM DUAL
      UNION ALL
      SELECT 2 F1, NULL F2 FROM DUAL
      UNION ALL
      SELECT 3 F1, 3 F2 FROM DUAL) A
;     

        F1         F2  LAG_VALUE
---------- ---------- ----------
         1          1         10
         2        NULL     1
         3          3          1

F1=3인행의 기준으로 보면 F1=2인 행의  F2값이  NULL이라 한칸 더 진행한 1을 반환한다.