|
이번 연재는 analytic function의 마지막 회로서 Lag/Lead family에 대한 소개 및 그 활용 사례를 살펴보기로 한다.
1.Lag/Lead Family의 특징
Lag/Lead Family는 특정 로우가 속한 파티션([엔코아 이창수 수석]제1회 Analytic Function의 소개 글의 Analytic function의 3요소 중 Result Set Partitions 참조) 내에서 상대적 상하 위치에 있는 특정 로우의 컬럼 값을 참조하거나 상호 비교하고자 할 때 사용할 수 있는 function들의 집합으로, 그 특징은 다음과 같다.
오름차순 또는 내림차순으로 정렬된 파티션 내에서 상대적으로 상위 또는 하위에 위치하고 있는 특정 로우의 컬럼 값을 offset 지정에 의해 참조할 수 있다.
파티션 내에서 참조할 로우가 없을 경우 지정한 값(default = NULL)으로 출력한다.
Order by에 기술된 컬럼의 값이 NULL인 경우 오름차순 또는 내림차순과 관계없이 순서 상 가장 처음 또는 마지막으로 강제 처리 가능하다.
2.Lag/Lead Family의 종류
1)Lag()
Lag 함수는 파티션 내에서 offset에 지정된 값(default = 1) 만큼 상대적으로 상위에 위치한 로우(오름차순의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우)를 참조하기 위해 사용된다.
Syntax :
Offset
파티션 내에서 참조하고자 하는 로우를 지정하기 위한 대한 상대 주소이다. 지정 하지 않으면 1이다.
Default
Offset에 의해 참조되는 로우가 파티션 내에 없을 경우, 즉 파티션의 범위를 벗어나는 경우에 출력되는 값을 지정한다. 지정하지 않으면 null이다.
Analytic clause
파티션의 크기를 지정하며 파티션 내 로우들에 대한 정렬 순서를 결정한다.
다음 테이블은 신용카드의 마일리지 포인트에 대한 포인트 적립 또는 포인트 사용 이력을 표현한 것이다.
위의 테이블의 각 로우에 대해 동일 카드번호의 종전 마일리지 포인트적립사용일자 및 적립사용포인트를 보고자 할 경우 다음과 같이 lag()를 사용하여 간단히 그 결과를 볼 수 있다.
SQL :
select 카드번호,
포인트적립사용일자,
순번,
적립사용포인트,
lag(포인트적립사용일자) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 종전포인트적립사용일자,
lag(적립사용포인트) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 적립사용포인트
from 마일리지테이블;
결과 :
2)Lead()
Lead()는 Lag()와 유사한 함수로 offset에 지정된 값(default = 1) 만큼 상대적으로 하위에 위치한 로우(오름차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우)를 참조하기 위해 사용된다는 점만 다르다.
Syntax :
Lag() 예제와는 반대로 마일리지테이블의 각 로우에 대해 동일 카드번호의 바로 다음 적립 또는 사용한 일자와 포인트를 보고자 할 경우 다음과 같이 lead()를 사용하여 그 결과를 볼 수 있다.
SQL :
select 카드번호,
포인트적립사용일자,
순번,
적립사용포인트,
lead(포인트적립사용일자) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 종전포인트적립사용일자,
lead(적립사용포인트) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 적립사용포인트
from 마일리지테이블;
결과 :
3.Lag/Lead Family의 활용 사례
이 절에서는 위에서 소개된 Lag/Lead Family의 실제업무에 이용 가능한 활용사례에 대해 살펴보고자 한다. 아래에 소개된 예들은 ORACLE 8i Version 이상에서 테스트 되었다.
1) 신용카드 마일리지 포인트 적립 또는 사용 이력의 선분화
Lag/Lead family사용 예에서 언급한 바 있는 신용카드 마일리지 포인트 적립 또는 사용에 관한 이력을 선분화 해보자. 아래의 마일리지테이블은 마일리지 포인트가 적립되거나 사용된 일자와 해당 포인트를 관리하고 있다.
대상 테이블 :
일반적으로, 운영 시스템은 마일리지 포인트가 적립되거나 사용된 business event를 중시하여 데이터화하기 때문에 event가 발생한 일자(마일리지 포인트 적립 또는 사용일자)와 event 결과(적립 또는 사용된 마일리지 포인트)에만 관심을 갖는다. 그러나 이러한 데이터 모델은 신용카드별 특정 시점의 사용 가능한 마일리지 포인트를 조회하고자 하는 요구사항에 대응하기 어렵다. 이와 같은 요구사항에 쉽게 대처하기 위해서 카드번호, 유효시작일자, 유효종료일자, 사용가능포인트 컬럼으로 구성된 사용가능포인트테이블을 설계하고 기존 마일리지테이블로부터 데이터를 추출하여 로딩하는 SQL을 Lead()를 활용하여 작성해보자. 사용가능포인트테이블의 유효시작일자는 마일리지 포인트를 적립 또는 사용한 일자이며, 유효종료일자는 그 다음에 발생한 마일리지 포인트 적립 또는 사용한 일자 - 1일로 설정한다. 또한, 사용가능포인트는 신용카드별로 현재까지 적립 또는 사용한 마일리지 포인트를 합산한다.
SQL :
create table 사용가능포인트테이블
as
select 카드번호 as 카드번호,
포인트적립사용일자 as 유효시작일자,
nvl(to_char(to_date(lead(포인트적립사용일자)
over (partition by 카드번호 order by 포인트적립사용일자),
'YYYYMMDD') - 1, 'YYYYMMDD'), '99991231') as 유효종료일자,
sum(sum(적립사용포인트)) over (partition by 카드번호
order by 포인트적립사용일자) as 사용가능포인트
from 마일리지테이블
group by 카드번호,
포인트적립사용일자;
결과 테이블(사용가능포인트테이블) :
2) 비어 있는 순번 찾기
위에서 예로 들었던 마일리지테이블의 순번은 신용카드별 포인트적립사용일자별 순번이다. 즉, 특정 신용카드에 대해 동일 일자에 마일리지 포인트를 적립 또는 사용한 횟수가 다수인 경우 순번을 1부터 차례로 증가시킨다. 신용카드의 마일리지 포인트에 대한 적립 또는 사용 이력에 대한 삭제가 가능해서 중간에 비어 있는 순번이 존재하고 이를 찾아서 조회해야 하는 요구를 만족하는 SQL을 작성해야 한다고 가정해보자.
우선 대상 마일리지테이블의 데이터는 다음과 같다.
대상 테이블 :
위의 테이블에 대해 비어 있는 순번을 카드번호와 포인트적립사용일자별로 출력하는 SQL은 다음과 같다.
SQL :
select a.카드번호,
a.포인트적립사용일자,
b.no
from (select 카드번호,
포인트적립사용일자,
순번,
lag(순번, 1, 0) over (partition by 카드번호,
포인트적립사용일자 order by 순번) as 이전순번
from 마일리지테이블) a,
copy_t b -- 비어 있는 순번 재생을 위한 복제용 테이블
where a.순번 > a.이전순번 + 1
and b.no between a.이전순번 + 1 and a.순번 - 1;
결과 집합 :
4. 맺음말
총 4회에 걸쳐서 Oracle 8i 이상의 버전에서 지원하는 각종의 analytic family에 대해 고찰하였다. 연재를 통해 살펴 본 바와 같이 analytic function은 관계형 데이터베이스의 기본 개념에 pointer와 offset의 개념을 부분적으로 도입함으로써 고난이도의 SQL 활용 수준을 요구하던 종래의 복잡한 analytic task를 효율적으로 단순화해서 처리할 수 있도록 했다. 향후에도 Oracle을 비롯한 여러 DBMS 개발업체는 analytic function과 같은 개발생산성 및 성능 향상에 도움이 되는 다양한 기능들을 양산할 것이며 이에 대한 활용도 및 기존 방법에 대한 대체 효과 등을 심층 연구하여 기회가 닿는 대로 기고할 것이다.
읽어주신 독자께 감사의 글을 전하며 조금이나마 기술적 진보에 도움이 되길 바라는 마음으로 총 4회에 걸친 analytic function에 대한 연재를 마치고자 한다.
|
