오라클에서 현재 로우를 기준으로 '이전 로우 값' 또는 '이후 로우 값'을 가져오고 싶으신가요? 해당 방법에 대해 안내해드리려고 합니다. 그렇게 어렵지 않으니 천천히 읽어보세요~ 😘
예시 자료는 <EMP> 테이블에서 'SAL'의 값을 'SAL'로 정렬하여 '이전 값', '이후 값'을 확인해 보려고 합니다. 위의 사진을 보면 다음과 같이 설명할 수 있습니다.
- 첫 번째 로우 'SAL' 컬럼은 800이며, 이전 로우 값은 없고, 이후 로우 값은 950 입니다. 이전 로우 값이 없는 이유는 첫 번째 행이기 때문입니다.
- 두 번째 로우 'SAL' 컬럼은 950이며, 이전 로우 값은 800, 이후 로우 값은 1100 입니다.
- 세 번째 로우 'SAL' 컬럼은 1100이며, 이전 로우 값은 950, 이후 로우 값은 1300 입니다.
- 마지막 네 번째 로우 컬럼은 'SAL'은 1300이며, 이전 로우 값은 1100 이고, 이후 값은 없습니다. 마지막 행이기 때문이지요.
LAG OVER , LEAD OVER FUNCTION => 이전값, 이후값 가져오기
이번 예제는 매개변수 expr, offset, default 를 보여드리겠습니다. 설명은 아래에 있습니다. 우선 신택스를 확인한 다음 간단한 예제를 보며 마무리 하도록 하겠습니다.
syntax
LAG | LEAD
{ ( expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ]
| ( expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ partition_clause ] order_by_clause)LAG, LEAD 예제 자료
SELECT SAL,
LAG (SAL) OVER (ORDER BY SAL),
LEAD (SAL) OVER (ORDER BY SAL),
LAG (SAL, 2) OVER (ORDER BY SAL) LAG_OFFSET_2,
LAG (SAL, 3, 0) OVER (ORDER BY SAL) LAG_OFFSET_3_DEFAULT_0,
TA.*
FROM EMP TA
WHERE JOB = 'CLERK'
ORDER BY TA.SAL- LAG (SAL) OVER (ORDER BY SAL) : 이전값
- LEAD (SAL) OVER (ORDER BY SAL) : 이후값
- LAG (SAL, 2) OVER (ORDER BY SAL) : 2번 째 이전값(2번 째 매개변수의 기본값은 1인데 2로 설정해 보았습니다.)
- LAG (SAL, 3, 0) OVER (ORDER BY SAL) : 3번 째 이전값(2번 째 매개변수 3), 해당 window function 의 scope를 벗어난 경우 0(3번 째 매개변수)을 설정합니다. 3번 째 매개변수의 기본값은 NULL 입니다.
오라클 데이터베이스에서 현재 로우를 기준으로 이전 로우의 값, 또는 이후 로우의 값을 가져오고 싶을 때가 있을 것입니다. 이때 사용할 수 있는 함수가 LAG, LEAD인데요. 해당 함수를 사용하여 어떻게 이전, 이후 로우 값을 가져올 수 있는지 방법에 대해 알아보도록 하겠습니다.
이전 로우 값, 이후 로우 값
예제 테이블은 다음과 같으며 총 14개의 로우를 가지고 있는 사원정보 테이블입니다. 입사일자(HIREDATE) 컬럼을 기준으로 해당 사원보다 이전에 입사한 직원, 그리고 이후에 입사한 직원 정보를 알아보도록 하겠습니다.
SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 91/01/05 2850 30
7782 CLARK MANAGER 7839 99/09/06 2450 10
7566 JONES MANAGER 7839 01/02/04 2975 20
7788 SCOTT ANALYST 7566 03/06/17 3000 20
7902 FORD ANALYST 7566 81/03/12 3000 20
7369 SMITH CLERK 7902 07/12/01 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/03/12 950 30
7934 MILLER CLERK 7782 03/01/23 1300 10
7839 KING PRESIDENT 96/11/17 5000 10
14 rows selected.LAG, LEAD 함수 사용 방법
LAG 함수는 이전 로우 값, LEAD 함수는 이후 로우 값을 가져오는 오라클 분석 함수입니다. 특히 셀프조인을 굳이 할 필요 없이, 컬럼에 함수를 사용하는 것만으로 바로 조회가 가능하기 때문에 알아두면 유용한 함수랍니다.
함수의 가장 기본적인 사용 방법은 첫 번째 매개변수에 이전, 이후 값을 가져오고 싶은 컬럼을 지정하고 OVER 절에 어떠한 순서에서 가지고 올지 정하는 것입니다. 이 부분을 먼저 살펴보도록 하겠습니다.
LAG, LEAD 기본 문법
다음은 입사일자를 기준으로 이전 로우값, 이후 로우값을 가져오는 예제입니다. 조회 결과의 특징은 HIREDATE가 '1981-03-12' 동일한 JAMES와 FORD의 경우, 이전값과 이후값이 예상과 다르게 가져올 수도 있다는 것입니다. 그리하여 LAG와 LEAD 함수를 사용할 때에는 키값과 같이 고유하게 지정할 수 있는 컬럼을 순서로 하여 순서가 겹치지 않도록 하는 것이 중요합니다.
SELECT LAG (HIREDATE) OVER (ORDER BY HIREDATE),
LEAD (HIREDATE) OVER (ORDER BY HIREDATE),
TA.*
FROM EMP TA
ORDER BY HIREDATE;
LAG(HIRE LEAD(HIR EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- -------- ---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
81/02/22 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
81/02/20 81/03/12 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
81/02/22 81/03/12 7900 JAMES CLERK 7698 81/03/12 950 30
81/03/12 81/08/09 7902 FORD ANALYST 7566 81/03/12 3000 20
81/03/12 81/09/28 7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
81/08/09 91/01/05 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
81/09/28 96/11/17 7698 BLAKE MANAGER 7839 91/01/05 2850 30
91/01/05 99/09/06 7839 KING PRESIDENT 96/11/17 5000 10
96/11/17 01/02/04 7782 CLARK MANAGER 7839 99/09/06 2450 10
99/09/06 03/01/23 7566 JONES MANAGER 7839 01/02/04 2975 20
01/02/04 03/06/17 7934 MILLER CLERK 7782 03/01/23 1300 10
03/01/23 07/12/01 7788 SCOTT ANALYST 7566 03/06/17 3000 20
03/06/17 87/05/23 7369 SMITH CLERK 7902 07/12/01 800 20
07/12/01 7876 ADAMS CLERK 7788 87/05/23 1100 20
14 rows selected.LAG, LEAD 매개변수 확장
LAG와 LEAD 함수의 매개변수는 크게 3가지이며 다음과 같습니다.
- value_expr : 첫 번째 매개변수는 조회하려는 컬럼 또는 표현이며, 필수 매개변수입니다.
- offset : 두 번째 매개변수는 몇 단계 이전, 이후 값을 가져올지 설정합니다. 생략하면 기본값은 1이기에 바로 이전, 이후 값을 조회합니다.
- default : 세 번째 매개변수는 윈도우를 벗어난 최초값 또는 최종값일 때 반환할 값을 지정합니다. 예를 들면 처음 값과 마지막 값은 이전이나 이후 값이 없기에 기본적으로 null을 반환하는데, null 대신 조회하고 싶은 값을 입력합니다. NVL 함수와 비슷한 기능이랍니다.
offset 매개변수를 활용하여 해당 로우보다 3번 째 이전, 이후 로우 값을 가져와보도록 하는 예제는 다음과 같습니다.
SELECT LAG (HIREDATE, 3) OVER (ORDER BY HIREDATE), TA.*
FROM EMP TA
ORDER BY HIREDATE;
LAG(HIRE EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7900 JAMES CLERK 7698 81/03/12 950 30
81/02/20 7902 FORD ANALYST 7566 81/03/12 3000 20
81/02/22 7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
81/03/12 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
81/03/12 7698 BLAKE MANAGER 7839 91/01/05 2850 30
81/08/09 7839 KING PRESIDENT 96/11/17 5000 10
81/09/28 7782 CLARK MANAGER 7839 99/09/06 2450 10
91/01/05 7566 JONES MANAGER 7839 01/02/04 2975 20
96/11/17 7934 MILLER CLERK 7782 03/01/23 1300 10
99/09/06 7788 SCOTT ANALYST 7566 03/06/17 3000 20
01/02/04 7369 SMITH CLERK 7902 07/12/01 800 20
03/01/23 7876 ADAMS CLERK 7788 87/05/23 1100 20
14 rows selected.default 매개변수를 활용하여, 최초 입사자, 최종 입사자의 이전 이후 값이 없는 경우 값을 표시해 보도록 하는 예제는 다음과 같습니다.
SELECT LAG (HIREDATE, 1, '00010101') OVER (ORDER BY HIREDATE),
LEAD (HIREDATE, 1, '99991231') OVER (ORDER BY HIREDATE),
TA.*
FROM EMP TA
ORDER BY HIREDATE;
LAG(HIRE LEAD(HIR EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- -------- ---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
01/01/01 81/02/22 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
81/02/20 81/03/12 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
81/02/22 81/03/12 7900 JAMES CLERK 7698 81/03/12 950 30
81/03/12 81/08/09 7902 FORD ANALYST 7566 81/03/12 3000 20
81/03/12 81/09/28 7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
81/08/09 91/01/05 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
81/09/28 96/11/17 7698 BLAKE MANAGER 7839 91/01/05 2850 30
91/01/05 99/09/06 7839 KING PRESIDENT 96/11/17 5000 10
96/11/17 01/02/04 7782 CLARK MANAGER 7839 99/09/06 2450 10
99/09/06 03/01/23 7566 JONES MANAGER 7839 01/02/04 2975 20
01/02/04 03/06/17 7934 MILLER CLERK 7782 03/01/23 1300 10
03/01/23 07/12/01 7788 SCOTT ANALYST 7566 03/06/17 3000 20
03/06/17 87/05/23 7369 SMITH CLERK 7902 07/12/01 800 20
07/12/01 99/12/31 7876 ADAMS CLERK 7788 87/05/23 1100 20
14 rows selected.LAG, LEAD PARTITION 절 사용하기
LAG 및 LEAD 함수에서 OVER 윈도우 다음에는 다음의 절을 지정할 수 있습니다. ORDER BY 절은 이전, 이후 행을 식별할 수 있으야 하므로 필수 값이며, PARTITION BY 절은 GROUP으로 나누어 이전, 이후 값을 조회할 때 사용하는 선택값입니다.
다음은 JOB별로 이전, 이후 입사일자를 조회해 보는 예제 자료입니다.
SELECT LAG (HIREDATE) OVER (PARTITION BY JOB ORDER BY HIREDATE),
LEAD (HIREDATE) OVER (PARTITION BY JOB ORDER BY HIREDATE),
TA.*
FROM EMP TA
ORDER BY JOB;
LAG(HIRE LEAD(HIR EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- -------- ---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
03/06/17 7902 FORD ANALYST 7566 81/03/12 3000 20
81/03/12 7788 SCOTT ANALYST 7566 03/06/17 3000 20
03/01/23 7900 JAMES CLERK 7698 81/03/12 950 30
81/03/12 07/12/01 7934 MILLER CLERK 7782 03/01/23 1300 10
03/01/23 87/05/23 7369 SMITH CLERK 7902 07/12/01 800 20
07/12/01 7876 ADAMS CLERK 7788 87/05/23 1100 20
99/09/06 7698 BLAKE MANAGER 7839 91/01/05 2850 30
91/01/05 01/02/04 7782 CLARK MANAGER 7839 99/09/06 2450 10
99/09/06 7566 JONES MANAGER 7839 01/02/04 2975 20
7839 KING PRESIDENT 96/11/17 5000 10
81/02/22 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
81/02/20 81/08/09 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
81/02/22 81/09/28 7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
81/08/09 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
14 rows selected.이전 로우, 이후 로우, LAG LEAD 함수 실전 예제
'코드', '분류번호', '명칭', '급여구분', '단가', '적용개시일자', '적용종료일자'로 이루어진 로우가 있다고 가정해 봅시다. 특히 적용개시일자와 적용종료일자를 중점적으로 살펴보겠습니다. 특징은 적용종료일자가 무기한이면 '9999-12-31'의 값을 설정하며, 이전 종료일자 바로 다음 날이 그 다음 코드의 적용개시일자라는 것입니다. 이것을 어떻게 실제로 사용할 수 있는지 예제를 통해 간단히 보여드리도록 하겠습니다.
- 적용개시일자 2023-01-01, 적용종료일자 9999-12-31
- 적용개시일자 2022-01-01, 적용종료일자 2022-12-31
다음 예제는 개시일자, 종료일자 컬럼이 모두 있는데, 실수로 이전 로우의 종료일자와 해당 로우의 시작일자가 중복되는 문제가 발생하였습니다.
WITH TA
AS (SELECT 'AA222' AS CODE,
4520 AS PRICE,
'20221201' AS BEGIN,
'99991231' AS END
FROM DUAL
UNION ALL
SELECT 'AA222',
4430,
'20220101',
'20221231'
FROM DUAL)
SELECT *
FROM TA;
CODE PRICE BEGIN END
---------- ---------- ---------------- ----------------
AA222 4520 20221201 99991231
AA222 4430 20220101 20221231다음과 같이 종료일자를 별도의 컬럼으로 설정하지 않고, 이전 로우 값을 통해 계산하도록 하는 예제자료입니다. 이처럼 LAG, LEAD를 활용할 수 있답니다.
WITH TA
AS (SELECT 'AA222' AS CODE, 4520 AS PRICE, '20230101' AS BEGIN FROM DUAL
UNION ALL
SELECT 'AA222', 4430, '20220101' FROM DUAL)
SELECT CODE,
BEGIN,
NVL (
TO_CHAR (
TO_DATE (LEAD (BEGIN) OVER (PARTITION BY CODE ORDER BY BEGIN))
- 1,
'YYYYMMDD'),
'99991231')
FROM TA;
CODE BEGIN NVL(TO_CHAR(TO_D
---------- ---------------- ----------------
AA222 20220101 20221231
AA222 20230101 99991231