선분 이력 분리

선분 이력을 분리해보자.

 

예제를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (bgn_dt DATE, end_dt DATE, val NUMBER);

INSERT INTO t1 VALUES (DATE '2014-01-01', DATE '2014-01-10', 1);
INSERT INTO t1 VALUES (DATE '2014-01-11', DATE '2014-02-10', 2);
INSERT INTO t1 VALUES (DATE '2014-02-11', DATE '2014-04-10', 3);
COMMIT;

 

아래 쿼리는 오라클 성능 고도화 원리와 해법 2권에서 소개된 방법으로 선분 이력 테이블과 Time Dimension Table을 조인하여 선분 이력을 월별로 분리한다.

-- 2
SELECT   GREATEST (a.bgn_dt, b.bgn_dt) AS bgn_dt
       , LEAST (a.end_dt, b.end_dt) AS end_dt
       , a.val
    FROM t1 a
       , td b
   WHERE b.cyl_cd = 'M'
     AND b.bgn_dt <= TO_CHAR (a.end_dt, 'YYYYMMDD')
     AND b.end_dt >= TO_CHAR (a.bgn_dt, 'YYYYMMDD')
ORDER BY 1;

BGN_DT     END_DT     VAL
---------- ---------- ---
2014-01-01 2014-01-10   1
2014-01-11 2014-01-31   2
2014-02-01 2014-02-10   2
2014-02-11 2014-02-28   3
2014-03-01 2014-03-31   3
2014-04-01 2014-04-10   3

6 행이 선택되었습니다.

 

아래와 같이 선분 이력의 기간만큼 행 복제하여 일자를 계산하는 방법도 사용할 수 있다.

-- 3
SELECT   GREATEST (a.bgn_dt, TRUNC (ADD_MONTHS (a.bgn_dt, b.lv - 1), 'MM')) AS bgn_dt
       , LEAST (a.end_dt, LAST_DAY (ADD_MONTHS (a.bgn_dt, b.lv - 1))) AS end_dt
       , a.val
    FROM t1 a
       , (SELECT     LEVEL AS lv
                FROM DUAL
          CONNECT BY LEVEL <= 12) b
   WHERE b.lv <= MONTHS_BETWEEN (LAST_DAY (a.end_dt), LAST_DAY (a.bgn_dt)) + 1
ORDER BY 1;

BGN_DT     END_DT     VAL
---------- ---------- ---
2014-01-01 2014-01-10   1
2014-01-11 2014-01-31   2
2014-02-01 2014-02-10   2
2014-02-11 2014-02-28   3
2014-03-01 2014-03-31   3
2014-04-01 2014-04-10   3

6 행이 선택되었습니다.