시계열 채우기

Time Dimension Table에 사용하여 시계열을 채워보자.

 

예제를 위해 아래와 같이 테이블을 생성하자. tq는 분기별, th는 반기별 데이터가 저장된 테이블이다.

-- 1
DROP TABLE tq PURGE;
DROP TABLE th PURGE;

CREATE TABLE tq AS
SELECT a.cd, b.std_dt, b.val
  FROM (SELECT     CHR (ASCII ('A') + LEVEL - 1) AS cd
              FROM DUAL
        CONNECT BY LEVEL <= 2) a
     , (SELECT     TO_CHAR (ADD_MONTHS (DATE '2014-01-01', LEVEL - 1), 'YYYYMM') AS std_dt
                 , LEVEL * 100 AS val
              FROM DUAL
        CONNECT BY LEVEL <= 12) b;

CREATE TABLE th AS
SELECT a.cd, a.std_dt, a.val
  FROM tq a
 WHERE MOD (a.val, 3) = 0;

DELETE FROM tq WHERE std_dt IN ('201405', '201410');
DELETE FROM th WHERE std_dt = '201406';

COMMIT;

 

2-1번 쿼리는 일자만 출력하는 유형으로 td 테이블을 기준으로 tq 테이블을 아우터 조인하면 된다. 2-2번 쿼리는 cd 칼럼별로 일자를 출력하는 유형으로 cd 칼럼으로 파티션 아우터 조인하면 된다. 

-- 2-1
SELECT   a.std_dt, NVL (b.val, 0) AS val
    FROM td a
       , (SELECT   a.std_dt
                 , SUM (a.val) AS val
              FROM tq a
          GROUP BY a.std_dt) b
   WHERE a.cyl_cd = 'M'
     AND a.std_dt BETWEEN '201401' AND '201412'
     AND b.std_dt(+) = a.std_dt
ORDER BY 1;

STD_DT  VAL
------ ----
201401  200
201402  400
201403  600
201404  800
201405    0
201406 1200
201407 1400
201408 1600
201409 1800
201410    0
201411 2200
201412 2400

12 행이 선택되었습니다.

-- 2-2
SELECT   b.cd, a.std_dt, NVL (b.val, 0) AS val
    FROM td a
    LEFT OUTER
    JOIN tq b PARTITION BY (b.cd)
      ON b.std_dt = a.std_dt
   WHERE a.cyl_cd = 'M'
     AND a.std_dt BETWEEN '201401' AND '201406'
ORDER BY 1, 2;

CD STD_DT VAL
-- ------ ---
A  201401 100
A  201402 200
A  201403 300
A  201404 400
A  201405   0
A  201406 600
B  201401 100
B  201402 200
B  201403 300
B  201404 400
B  201405   0
B  201406 600

12 행이 선택되었습니다.

 

3-1번 쿼리는 2-2 쿼리의 결과에 반기별 th 테이블의 시계열을 연결하는 유형이다. std_dt_q 칼럼을 사용하여 아우터 조인하면 된다. 반대로 3-2번 쿼리는 반기별 th 테이블를 기준으로 std_dt_h 칼럼을 통해 분기별 tq 테이블을 아우터 조인한다.  

-- 3-1
SELECT   b.cd
       , a.std_dt   AS std_dt_tq, NVL (b.val, 0) AS val_tq
       , a.std_dt_q AS std_dt_th, NVL (c.val, 0) AS val_th
    FROM td a
    LEFT OUTER
    JOIN tq b PARTITION BY (b.cd)
      ON b.std_dt = a.std_dt
    LEFT OUTER
    JOIN th c
      ON c.cd = b.cd
     AND c.std_dt = a.std_dt_q
   WHERE a.cyl_cd = 'M'
     AND a.std_dt BETWEEN '201401' AND '201406'
ORDER BY 1, 2;

CD STD_DT_TQ VAL_TQ STD_DT_TH VAL_TH
-- --------- ------ --------- ------
A  201401       100 201403       300
A  201402       200 201403       300
A  201403       300 201403       300
A  201404       400 201406         0
A  201405         0 201406         0
A  201406       600 201406         0
B  201401       100 201403       300
B  201402       200 201403       300
B  201403       300 201403       300
B  201404       400 201406         0
B  201405         0 201406         0
B  201406       600 201406         0

12 행이 선택되었습니다.

-- 3-2
SELECT   b.cd
       , a.std_dt   AS std_dt_th, NVL (b.val, 0) AS val_th
       , a.std_dt_h AS std_dt_tq, NVL (c.val, 0) AS val_tq
    FROM td a
    LEFT OUTER
    JOIN th b PARTITION BY (b.cd)
      ON b.std_dt = a.std_dt
    LEFT OUTER
    JOIN tq c
      ON c.cd = b.cd
     AND c.std_dt = a.std_dt_h
   WHERE a.cyl_cd = 'Q'
     AND a.std_dt BETWEEN '201401' AND '201406'
ORDER BY 1, 2;

CD STD_DT_TH VAL_TH STD_DT_TQ VAL_TQ
-- --------- ------ --------- ------
A  201403       300 201403       300
A  201406         0 201406       600
B  201403       300 201403       300
B  201406         0 201406       600

4 행이 선택되었습니다.