꿈꾸는 개발자, DBA 커뮤니티 구루비

(2) 선분이력 끊기

선분이력 레코드를 가공해야 할 때가 있는데, 월말 기준으로 선분을 끊는 경우를 살펴보자.
본론으로 들어가기에 앞서, 두 선분이 겹치는 구간에 대한 시작일자 및 종료일자 선택 규칙에 대해 살펴보자.
시간을 나타내는 두 개의 선분이 서로 겹치는 모습을 표현하면, 아래의 그림 (a),(b),©,(d)처럼 네 가지 패턴이 있다.

'월도'와 '선분이력' 테이블 생성


create table 월도(기준월, 시작일자, 종료일자)
as
select '200906', '20090601', '20090630' from dual union all
select '200907', '20090701', '20090731' from dual union all
select '200908', '20090801', '20090831' from dual union all
select '200909', '20090901', '20090930' from dual union all
select '200910', '20091001', '20091030' from dual
;

create table 선분이력 (상품번호, 시작일자, 종료일자, 데이터)
as
select 'a', '20090713', '20090808', 'a1' from dual union all
select 'a', '20090809', '20090820', 'a2' from dual union all
select 'a', '20090821', '20091007', 'a3' from dual
;

select a.기준월, b.시작일자, b.종료일자, b.상품번호, b.데이터
    from 월도 a, 선분이력 b
   where b.시작일자 <= a.종료일자
     and b.종료일자 >= a.시작일자
   order by  a.기준월, b.시작일자
;

기준월       시작일자         종료일자         상품번호  데이터
------------ ---------------- --------- --------- ----
200907       20090713         20090808       A     A1    ----- 5 -> 8
200908       20090713         20090808       A     A1    ----- 5 -> 9
200908       20090809         20090820       A     A2    ----- 6 -> 10
200908       20090821         20091007       A     A3    ----- 7 -> 11
200909       20090821         20091007       A     A3    ----- 7 -> 12
200910       20090821         20091007       A     A3    ----- 7 -> 13

5번 선분은 8,9 두 선분으로 복제되었고, 7번 선분은 11,12,13 세 선분으로 복제되었다.
7번 선분을 기준으로 설명하면, 시작일자가 자신의 종료일자보다 작은 월도(b.종료일자 >= a.시작일자)는 1,2,3,4번이 모두 해당되고, 종료일자가 자신의 시작일자보다 큰 월도(b.시작일자 <= a.종료일자)는 2,3,4번이다.
따라서 두 조건을 모두 만족하는 월도는 2,3,4번이므로 7번 선분은 세개로 복제가 이루어진다.
선분이력이 여러 개 생기더라도 기준월은 각각 다른 값을 가진다는 점에 주목하기 바란다.

여섯 개 선분의 시작일자와 종료일자를 구하는 것이 숙제인데, 이를 위해 8~13번 선분을 그림 2-24에 예시한 스타일로 분류하면 다음과 같다.

변환된 8번과 11번 선분은 스타일 (a)에 속한다.
변환된 9번과 13번 선분은 스타일 (b)에 속한다.
변환된 12번 선분은 스타일 (d)에 속한다.

아래 쿼리의 수행 결과를 보면, 그림 2-25 하단에 있는 '변환된 선분이력'과 똑같은 결과집합인 것을 알 수 있다.


SELECT 상품번호
        ,case when lst = 시작일자1 and gst = 종료일자2 then 시작일자2   -- 스타일a
              when lst = 시작일자2 and gst = 종료일자1 then 시작일자1   -- 스타일b
              when lst = 시작일자1 and gst = 종료일자1 then 시작일자2   -- 스타일c
              when lst = 시작일자2 and gst = 종료일자2 then 시작일자2   -- 스타일d
         end 시작일자
        ,case when lst = 시작일자1 and gst = 종료일자2 then 종료일자2   -- 스타일a
              when lst = 시작일자2 and gst = 종료일자1 then 종료일자1   -- 스타일b
              when lst = 시작일자1 and gst = 종료일자1 then 종료일자2   -- 스타일c
              when lst = 시작일자2 and gst = 종료일자2 then 종료일자2   -- 스타일d
         end 종료일자
        ,데이터
    FROM (
            SELECT b.상품번호,b.데이터,a.기준월
                   ,a.시작일자 시작일자1,b.시작일자 시작일자2
                   ,a.종료일자 종료일자1,b.종료일자 종료일자2
                   ,least(a.시작일자,a.종료일자,b.시작일자,b.종료일자) lst
                   ,greatest(a.시작일자,a.종료일자,b.시작일자,b.종료일자) gst
              FROM 월도 a, 선분이력 b
             WHERE b.시작일자 <= a.종료일자
               AND b.종료일자 >= a.시작일자
          )
;

상품번호   시작일자         종료일자         데이터
--------- ---------- -------------- ----------
    A        2009/07/13         2009/07/31         A1
    A        2009/08/01         2009/08/08         A1
    A        2009/08/09         2009/08/20         A2
    A        2009/08/21         2009/08/31         A3
    A        2009/09/01         2009/09/30         A3
    A        2009/10/01         2009/10/07         A3