Oracle/SQL2014. 5. 15. 17:10

선분 이력 분리

선분 이력을 분리해보자.

 

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

1
2
3
4
5
6
7
8
-- 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을 조인하여 선분 이력을 월별로 분리한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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 행이 선택되었습니다.

 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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 행이 선택되었습니다.


'Oracle > SQL' 카테고리의 다른 글

테이블 동기화  (0) 2018.01.18
근태 내역 조회  (0) 2014.07.29
시계열 채우기  (0) 2014.05.15
Time Dimension Table  (0) 2014.05.15
다중 열 다중 행 스칼라 서브 쿼리  (0) 2014.05.14
Posted by 정희락_

티스토리툴바