점 이력을 선분 이력으로 전환

점 이력을 선분 이력으로 전환해보자.

예제를 위해 아래와 같이 테이블을 생성하자. 변경 시점에 이력이 발생하는 점 이력 테이블이다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (cd VARCHAR2(1), dt DATE, vl NUMBER);

INSERT INTO t1 VALUES ('A', DATE '2050-01-01', 1);
INSERT INTO t1 VALUES ('A', DATE '2050-01-06', 2);
INSERT INTO t1 VALUES ('A', DATE '2050-01-16', 1);
INSERT INTO t1 VALUES ('A', DATE '2050-01-31', 3);
COMMIT;

LEAD 함수를 사용하면 선분 이력을 쉽게 생성할 수 있다.

-- 2
SELECT   cd, dt AS dt_bg
       , LEAD (dt - 1, 1, DATE '9999-12-31') OVER (ORDER BY dt) AS dt_ed, vl
    FROM t1
ORDER BY 1;

CD DT_BG      DT_ED      VL
-- ---------- ---------- --
A  2050-01-01 2050-01-05  1
A  2050-01-31 9999-12-31  3
A  2050-01-16 2050-01-30  1
A  2050-01-06 2050-01-15  2

4 행이 선택되었습니다.

다음 예제를 위해 아래와 같이 테이블을 생성하자. 변경과 관련 없이 월별로 생성되는 점 이력 테이블이다.

-- 3
DROP TABLE t1 PURGE;
CREATE TABLE t1 (cd VARCHAR2(1), ym VARCHAR2(6), vl NUMBER);

INSERT INTO t1 VALUES ('A', '205001', 1);
INSERT INTO t1 VALUES ('A', '205002', 1);
INSERT INTO t1 VALUES ('A', '205003', 2);
INSERT INTO t1 VALUES ('A', '205004', 2);
INSERT INTO t1 VALUES ('A', '205005', 2);
INSERT INTO t1 VALUES ('A', '205006', 1);
INSERT INTO t1 VALUES ('A', '205007', 1);
INSERT INTO t1 VALUES ('A', '205008', 1);
INSERT INTO t1 VALUES ('A', '205009', 1);
INSERT INTO t1 VALUES ('A', '205010', 3);
INSERT INTO t1 VALUES ('A', '205011', 3);
INSERT INTO t1 VALUES ('A', '205012', 3);
COMMIT;

아래 쿼리로 선분 이력을 생성할 수 있다.

-- 4
SELECT   cd, MIN (ym) AS ym_bg
       , CASE WHEN MAX (r1) = MAX (cn) THEN '999912' ELSE MAX (ym) END AS ym_ed, vl
    FROM (SELECT a.*
               , COUNT (*) OVER (PARTITION BY a.cd) AS cn
               , ROW_NUMBER () OVER (PARTITION BY a.cd ORDER BY a.ym) AS r1
               , ROW_NUMBER () OVER (PARTITION BY a.cd, a.vl ORDER BY a.ym) AS r2
            FROM t1 a)
GROUP BY cd, vl, r1 - r2
ORDER BY 1, 2;

CD YM_BG  YM_ED  VL
-- ------ ------ --
A  205001 205002  1
A  205003 205005  2
A  205006 205009  1
A  205010 999912  3

4 행이 선택되었습니다.