Time Dimension Table을 생성해보자.
아래와 같이 td 테이블을 생성하자. 성능을 고려하여 cyl_cd 칼럼을 파티션 키로 가진 List Partitioned IOT로 생성했다.
-- 1
DROP TABLE td PURGE;
CREATE TABLE td (
cyl_cd VARCHAR(1) -- 주기코드
, std_dt VARCHAR(8) -- 기준일자
, bgn_dt VARCHAR(8) -- 시작일자
, end_dt VARCHAR(8) -- 종료일자
, std_dt_w VARCHAR(6) -- 기준일자_주간
, std_dt_m VARCHAR(6) -- 기준일자_월간
, std_dt_q VARCHAR(6) -- 기준일자_분기
, std_dt_h VARCHAR(6) -- 기준일자_반기
, std_dt_y VARCHAR(4) -- 기준일자_연간
, out_dt VARCHAR(10) -- 출력일자
, CONSTRAINT td_pk PRIMARY KEY (cyl_cd, std_dt)
, CONSTRAINT td_c1 CHECK (bgn_dt <= end_dt))
ORGANIZATION INDEX
PCTFREE 0
PARTITION BY LIST (cyl_cd) (
PARTITION p_d VALUES ('D')
, PARTITION p_w VALUES ('W')
, PARTITION p_m VALUES ('M')
, PARTITION p_q VALUES ('Q')
, PARTITION p_h VALUES ('H')
, PARTITION p_y VALUES ('Y'));
CREATE UNIQUE INDEX td_u1 ON td (cyl_cd, end_dt, bgn_dt, std_dt) LOCAL PCTFREE 0;
기준일자 값은 Fact Table의 Time Dimension 칼럼 값으로 생성하면 되고, 출력일자 값은 UI 표준을 따라 생성하면 된다. 주기별 기준일자 칼럼(std_dt_%)은 주기간의 시계열을 연결하기 위해 사용된다.
-- 2
INSERT
INTO td
(cyl_cd, std_dt, bgn_dt, end_dt, std_dt_w, std_dt_m, std_dt_q, std_dt_h, std_dt_y, out_dt)
WITH w1 AS
(SELECT DATE '1950-01-01' AS bgn_dt
, DATE '2051-01-01' AS end_dt
FROM DUAL)
, w1_d AS
(SELECT 'D' AS cyl_cd
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYYMMDD') AS std_dt
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYYMMDD') AS bgn_dt
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYYMMDD') AS end_dt
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'IYYYIW') AS std_dt_w
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYYMM') AS std_dt_m
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYY')
|| LPAD (CEIL (TO_CHAR (a.bgn_dt + LEVEL - 1, 'MM') / 3) * 3, 2, '0') AS std_dt_q
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYY')
|| LPAD (CEIL (TO_CHAR (a.bgn_dt + LEVEL - 1, 'MM') / 6) * 6, 2, '0') AS std_dt_h
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYY') AS std_dt_y
, TO_CHAR (a.bgn_dt + LEVEL - 1, 'YYYY-MM-DD') AS out_dt
FROM w1 a
CONNECT BY LEVEL <= a.end_dt - a.bgn_dt)
, w1_w AS
(SELECT 'W' AS cyl_cd
, TO_CHAR (TO_DATE (a.std_dt, 'YYYYMMDD'), 'IYYYIW') AS std_dt
, MIN (a.bgn_dt) AS bgn_dt , MAX (a.end_dt) AS end_dt
, MAX (a.std_dt_w) AS std_dt_w, MAX (a.std_dt_m) AS std_dt_m
, MAX (a.std_dt_q) AS std_dt_q, MAX (a.std_dt_h) AS std_dt_h
, MAX (a.std_dt_y) AS std_dt_y
, TO_CHAR (TO_DATE (MAX (a.std_dt), 'YYYYMMDD'), 'IYYY-IW"W"') AS out_dt
FROM w1_d a
GROUP BY TO_CHAR (TO_DATE (a.std_dt, 'YYYYMMDD'), 'IYYYIW'))
, w1_m AS
(SELECT 'M' AS cyl_cd
, SUBSTR (a.std_dt, 1, 6) AS std_dt
, MIN (a.bgn_dt) AS bgn_dt , MAX (a.end_dt) AS end_dt
, MAX (a.std_dt_w) AS std_dt_w, MAX (a.std_dt_m) AS std_dt_m
, MAX (a.std_dt_q) AS std_dt_q, MAX (a.std_dt_h) AS std_dt_h
, MAX (a.std_dt_y) AS std_dt_y
, TO_CHAR (TO_DATE (MAX (a.std_dt), 'YYYYMMDD'), 'YYYY-MM') AS out_dt
FROM w1_d a
GROUP BY SUBSTR (a.std_dt, 1, 6))
, w1_q AS
(SELECT 'Q' AS cyl_cd
, MAX (SUBSTR (a.std_dt, 1, 6)) AS std_dt
, MIN (a.bgn_dt) AS bgn_dt , MAX (a.end_dt) AS end_dt
, MAX (a.std_dt_w) AS std_dt_w, MAX (a.std_dt_m) AS std_dt_m
, MAX (a.std_dt_q) AS std_dt_q, MAX (a.std_dt_h) AS std_dt_h
, MAX (a.std_dt_y) AS std_dt_y
, TO_CHAR (TO_DATE (MAX (a.std_dt), 'YYYYMM'), 'YYYY-Q"Q"') AS out_dt
FROM w1_m a
GROUP BY TO_CHAR (TO_DATE (a.std_dt, 'YYYYMM'), 'YYYYQ'))
, w1_h AS
(SELECT 'H' AS cyl_cd
, MAX (SUBSTR (a.std_dt, 1, 6)) AS std_dt
, MIN (a.bgn_dt) AS bgn_dt , MAX (a.end_dt) AS end_dt
, MAX (a.std_dt_w) AS std_dt_w, MAX (a.std_dt_m) AS std_dt_m
, MAX (a.std_dt_q) AS std_dt_q, MAX (a.std_dt_h) AS std_dt_h
, MAX (a.std_dt_y) AS std_dt_y
, SUBSTR (a.std_dt, 1, 4) || '-' || CEIL (SUBSTR (a.std_dt, 5, 2) / 6) || 'H' AS out_dt
FROM w1_q a
GROUP BY SUBSTR (a.std_dt, 1, 4)
, CEIL (SUBSTR (a.std_dt, 5, 2) / 6))
, w1_y AS
(SELECT 'Y' AS cyl_cd
, SUBSTR (a.std_dt, 1, 4) AS std_dt
, MIN (a.bgn_dt) AS bgn_dt , MAX (a.end_dt) AS end_dt
, MAX (a.std_dt_w) AS std_dt_w, MAX (a.std_dt_m) AS std_dt_m
, MAX (a.std_dt_q) AS std_dt_q, MAX (a.std_dt_h) AS std_dt_h
, MAX (a.std_dt_y) AS std_dt_y
, SUBSTR (a.std_dt, 1, 4) AS out_dt
FROM w1_h a
GROUP BY SUBSTR (a.std_dt, 1, 4))
SELECT a.* FROM w1_d a UNION ALL
SELECT a.* FROM w1_w a UNION ALL
SELECT a.* FROM w1_m a UNION ALL
SELECT a.* FROM w1_q a UNION ALL
SELECT a.* FROM w1_h a UNION ALL
SELECT a.* FROM w1_y a
ORDER BY 1, 2;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'HRJEONG', tabname => 'td', cascade => TRUE);
아래는 cyl_cd 별로 td 테이블을 조회한 결과다.
-- 3-1 : daily SELECT a.* FROM td a WHERE a.cyl_cd = 'D' AND a.std_dt BETWEEN '20140101' AND '20140104'; CYL_CD STD_DT BGN_DT END_DT STD_DT_W STD_DT_M STD_DT_Q STD_DT_H STD_DT_Y OUT_DT ------ -------- -------- -------- -------- -------- -------- -------- -------- ---------- D 20140101 20140101 20140101 201401 201401 201403 201406 2014 2014-01-01 D 20140102 20140102 20140102 201401 201401 201403 201406 2014 2014-01-02 D 20140103 20140103 20140103 201401 201401 201403 201406 2014 2014-01-03 D 20140104 20140104 20140104 201401 201401 201403 201406 2014 2014-01-04 4 행이 선택되었습니다. -- 3-2 : weekly SELECT * FROM td a WHERE a.cyl_cd = 'W' AND a.std_dt BETWEEN '201401' AND '201404'; CYL_CD STD_DT BGN_DT END_DT STD_DT_W STD_DT_M STD_DT_Q STD_DT_H STD_DT_Y OUT_DT ------ -------- -------- -------- -------- -------- -------- -------- -------- -------- W 201401 20131230 20140105 201401 201401 201403 201406 2014 2014-01W W 201402 20140106 20140112 201402 201401 201403 201406 2014 2014-02W W 201403 20140113 20140119 201403 201401 201403 201406 2014 2014-03W W 201404 20140120 20140126 201404 201401 201403 201406 2014 2014-04W 4 행이 선택되었습니다. -- 3-3 : monthly SELECT a.* FROM td a WHERE a.cyl_cd = 'M' AND a.std_dt BETWEEN '201401' AND '201404'; CYL_CD STD_DT BGN_DT END_DT STD_DT_W STD_DT_M STD_DT_Q STD_DT_H STD_DT_Y OUT_DT ------ -------- -------- -------- -------- -------- -------- -------- -------- ------- M 201401 20140101 20140131 201405 201401 201403 201406 2014 2014-01 M 201402 20140201 20140228 201409 201402 201403 201406 2014 2014-02 M 201403 20140301 20140331 201414 201403 201403 201406 2014 2014-03 M 201404 20140401 20140430 201418 201404 201406 201406 2014 2014-04 4 행이 선택되었습니다. -- 3-4 : quarterly SELECT a.* FROM td a WHERE a.cyl_cd = 'Q' AND a.std_dt BETWEEN '201401' AND '201412'; CYL_CD STD_DT BGN_DT END_DT STD_DT_W STD_DT_M STD_DT_Q STD_DT_H STD_DT_Y OUT_DT ------ -------- -------- -------- -------- -------- -------- -------- -------- ------- Q 201403 20140101 20140331 201414 201403 201403 201406 2014 2014-1Q Q 201406 20140401 20140630 201427 201406 201406 201406 2014 2014-2Q Q 201409 20140701 20140930 201440 201409 201409 201412 2014 2014-3Q Q 201412 20141001 20141231 201501 201412 201412 201412 2014 2014-4Q 4 행이 선택되었습니다. -- 3-5 : half-yearly SELECT a.* FROM td a WHERE a.cyl_cd = 'H' AND a.std_dt BETWEEN '201401' AND '201512'; CYL_CD STD_DT BGN_DT END_DT STD_DT_W STD_DT_M STD_DT_Q STD_DT_H STD_DT_Y OUT_DT ------ -------- -------- -------- -------- -------- -------- -------- -------- ------- H 201406 20140101 20140630 201427 201406 201406 201406 2014 2014-1H H 201412 20140701 20141231 201501 201412 201412 201412 2014 2014-2H H 201506 20150101 20150630 201527 201506 201506 201506 2015 2015-1H H 201512 20150701 20151231 201553 201512 201512 201512 2015 2015-2H 4 행이 선택되었습니다. -- 3-6 : yearly SELECT a.* FROM td a WHERE a.cyl_cd = 'Y' AND a.std_dt BETWEEN '2014' AND '2017'; CYL_CD STD_DT BGN_DT END_DT STD_DT_W STD_DT_M STD_DT_Q STD_DT_H STD_DT_Y OUT_DT ------ -------- -------- -------- -------- -------- -------- -------- -------- ------ Y 2014 20140101 20141231 201501 201412 201412 201412 2014 2014 Y 2015 20150101 20151231 201553 201512 201512 201512 2015 2015 Y 2016 20160101 20161231 201652 201612 201612 201612 2016 2016 Y 2017 20170101 20171231 201752 201712 201712 201712 2017 2017 4 행이 선택되었습니다.
'Oracle > SQL' 카테고리의 다른 글
| 선분 이력 분리 (0) | 2014.05.15 |
|---|---|
| 시계열 채우기 (0) | 2014.05.15 |
| 다중 열 다중 행 스칼라 서브 쿼리 (0) | 2014.05.14 |
| 파티션 확장 절 (0) | 2014.05.10 |
| Native LEFT OUTER JOIN 동작 개선 (0) | 2014.04.19 |
