SQL BOOSTER 이어지는 이야기 .03

SQL BOOSTER 에 이어지는 이야기들입니다.~!

SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

 

설명의 편의상 반말체로 작성한 점 양해바랍니다.

pdf 파일을 다운 받아 보셔도 됩니다.

 

SQL_Booster_이어지는이야기03.pdf

0.40MB

 

 

 

<꽤 괜찮은 기준일자 테이블>

 

기준일자 테이블을 사용하는 사이트가 가끔 있다. 기준일자는 업무에 직접적으로 관련은 없지만 일자 정보만 모아서 저장해 놓은 테이블이다. 필자가 처음 맡았던 시스템에는 이미 기준일자 테이블을 누군가 설계 해놓았다. 덕분에 기준일자 테이블을 사용할 수 있었고 기준일자 테이블을 더욱 확장해 매우 유용하게 사용했다.

 

 

아래와 같은 형태로 기준일자 테이블을 생성할 예정이다.

[그림 1]

기준일자(BAS_YMD) 컬럼은 문자열로 ‘YYYYMMDD’ 형태의 일자 데이터를 저장한다. 기준일(BAS_DT)은 DATE형으로 일자를 저장한다. 논리명에 문자열은 ‘일자’, DATE형은 ‘일’을 쓰기로 한다. 기준요일은 기준일에 대한 요일 값이다. 기준일자순번은 기준일자 순서에 따라 1부터 시작하는 연속된 값을 부여한다. 이 값을 이용해 일자 연산을 대신할 수 있다. 기준주는 기준일자가 속한 주를 뜻한다. 기준월, 기준분기, 기준반기, 기준년도도 마찬가지다.

아래 스크립트를 이용해 기준일자 테이블을 생성할 수 있다.

[SQL-1] 기준일자 테이블 생성
CREATE TABLE C_BAS_YMD
(  BAS_YMD VARCHAR2(8) NOT NULL
  ,BAS_DT DATE NULL
  ,BAS_DY VARCHAR2(40) NULL
  ,BAS_YMD_SEQ NUMBER(9) NULL
  ,BAS_YW VARCHAR(6) NULL
  ,BAS_YM VARCHAR(6) NULL
  ,BAS_YQ VARCHAR(5) NULL
  ,BAS_YH VARCHAR(5) NULL
  ,BAS_YY VARCHAR(6) NULL
  );
  
ALTER TABLE C_BAS_YMD
  ADD CONSTRAINT PK_C_BAS_YMD PRIMARY KEY(BAS_YMD);
  
CREATE UNIQUE INDEX UX_C_BAS_YMD_01 ON C_BAS_YMD(BAS_DT);

CREATE UNIQUE INDEX UX_C_BAS_YMD_02 ON C_BAS_YMD(BAS_YMD_SEQ);

CREATE INDEX X_C_BAS_YMD_01 ON C_BAS_YMD(BAS_YM,BAS_YMD);

 

기준일자 테이블을 만든 후에는 다음 SQL을 이용해 기준일자 데이터를 생성할 수 있다. 2016년1월1일부터 2030년12월31일까지의 기준일자를 생성한다.

[SQL-2] 기준일자 데이터 생성
INSERT INTO C_BAS_YMD
        (BAS_YMD,BAS_DT,BAS_YMD_SEQ)
SELECT  TO_CHAR(TO_DATE('20160101','YYYYMMDD') + ROWNUM,'YYYYMMDD') BAS_YMD
        ,TO_DATE('20160101','YYYYMMDD') + ROWNUM BAS_DT
        ,ROWNUM BAS_YMD_SEQ
FROM    DUAL T1
CONNECT BY TO_DATE('20160101','YYYYMMDD') + ROWNUM <= TO_DATE('20301231','YYYYMMDD');

COMMIT;

 

C_BAS_YMD에는 BAS_YMD(기준일자), BAS_DT(기준일), BAS_YMD_SEQ(기준일자순번) 값만 입력되어 있다. 나머지 값들은 UPDATE로 채워 넣을 예정이다. 아래 SQL은 기준주(BAS_YW)를 제외한 나머지 값들을 채워 넣는 SQL이다. 기준주는 로직이 조금 복잡해 별도 처리한다.

[SQL-3] 기준월, 기준분기, 기준반기, 기준년도, 기준요일 UPDATE
UPDATE  C_BAS_YMD T1
SET     T1.BAS_YM = SUBSTR(T1.BAS_YMD,1,6)
        ,T1.BAS_YQ = SUBSTR(T1.BAS_YMD,1,4)||CEIL(TO_NUMBER(SUBSTR(T1.BAS_YMD,5,2)) / 3)
        ,T1.BAS_YH = SUBSTR(T1.BAS_YMD,1,4)||CEIL(TO_NUMBER(SUBSTR(T1.BAS_YMD,5,2)) / 6)
        ,T1.BAS_YY = SUBSTR(T1.BAS_YMD,1,4)
        ,T1.BAS_DY = CASE TO_CHAR(T1.BAS_DT,'D') 
                                WHEN '1' THEN 'SUN'
                                WHEN '2' THEN 'MON'
                                WHEN '3' THEN 'TUE'
                                WHEN '4' THEN 'WED'
                                WHEN '5' THEN 'THU'
                                WHEN '6' THEN 'FRI'
                                WHEN '7' THEN 'SAT' END
        ;

COMMIT;

 

[SQL-3]은 설명 없이도 이해하는데 아무 어려움이 없을 것이다. 아래 [SQL-4]는 기준주를 UPDATE하는 SQL이다. SQL을 먼저 살펴보자.

[SQL-4] 기준주 UPDATE
UPDATE  C_BAS_YMD T1
SET     T1.BAS_YW = 
        CASE
WHEN TO_CHAR(T1.BAS_DT,'MMDD') <= '0107' AND TO_CHAR(T1.BAS_DT,'IW') >= 50 THEN 
TO_CHAR(T1.BAS_DT,'YYYY')-1||TO_CHAR(T1.BAS_DT,'IW')
            WHEN TO_CHAR(T1.BAS_DT,'MMDD') >= '1224' AND TO_CHAR(T1.BAS_DT,'IW') = 01 THEN 
TO_CHAR(T1.BAS_DT,'YYYY')+1||TO_CHAR(T1.BAS_DT,'IW')
            ELSE TO_CHAR(T1.BAS_DT,'YYYY')||TO_CHAR(T1.BAS_DT,'IW')
        END;

C_BAS_YMD의 기준일(BAS_DT) 값을 이용해 기준주를 구하고 있다. TO_CHAR(T1.BAS_DT,’IW’)를 실행하면, 해당 일의 주 값을 알 수 있다.

첫 번째 WHEN 부분부터 살펴보자. 기준일이 1월7일보다 작으면서, 기준일의 주 값이 50보다 크면, 기준일의 년도가 아니라, 전년도의 년도를 가져와 주 값과 결합한다. 아래와 같은 경우다. 2016년1월1일이 2016-53주일리는 없다. 그러므로 전년도인 2015-53으로 만들어야 한다. (비교에서 년도는 제외하고 년월만 사용한다.)

[SQL-5] 첫 번째  WHEN, 기준일이 1월7일보다 작으면서 주 값이 50 이상인 경우
SELECT TO_CHAR(TO_DATE('20160101'),'IW') FROM DUAL; -- 결과 값은 53주가 나온다. 

[SQL-4]의 두 번째 WHEN 부분을 살펴보자. 기준일이 12월24일보다 크면서, 주 값이 1주인 경우다. 이 경우에는 기준일의 년도의 다음 년도를 가져와 주를 결합한다. 아래와 같은 경우다. (비교에서 년도는 제외하고 년월만 사용한다.)

[SQL-5] 두 번째 WHEN, 기준일이 12월24일보다 크면서 주 값이 1인 경우.
SELECT TO_CHAR(TO_DATE('20191231'),'IW') FROM DUAL; -- 결과 값은 1주가 나온다. 

첫 번쨰, 두 번째 WHEN은 년초의 몇 일이 작년 주에 속하거나, 년말의 몇 일이 내년 주에 속하는 경우를 예외처리한 것이다. 세 번째 WHEN은 나머지 경우로 기준일의 년도를 그대로 사용하면 되는 경우다.

위에서 첫 번째 두 번째 WHEN절에서 1월7일, 12월24일을 사용했는데 꼭 그 날이 아니어도 된다. 대략 년초, 년말이면 된다.

데이터가 제대로 입력되었는지는 아래 SQL로 확인해볼 수 있다.

[SQL-6] 주 데이터 확인
SELECT  T1.BAS_YW
        ,COUNT(*)
        ,MIN(T1.BAS_DT) FRS_DT
        ,MAX(T1.BAS_DT) LST_DT
        ,MAX(T1.BAS_DY) KEEP(DENSE_RANK FIRST ORDER BY T1.BAS_DT ASC)
        ,MAX(T1.BAS_DY) KEEP(DENSE_RANK LAST ORDER BY T1.BAS_DT ASC)
FROM    C_BAS_YMD T1
GROUP BY T1.BAS_YW;

기준일자 테이블에 첫 주와 마지막 주를 제외하고 모두 7일씩 제대로 데이터가 만들어진 것을 확인할 수 있다. [SQL-6]에는 KEEP이 사용되었다. KEEP의 사용법은 정희락님의 ‘불친절한 SQL 프로그래밍’을 참고해보길 추천한다.

이제 기준일자(C_BAS_YMD) 테이블에 기본적인 데이터를 모두 채워 넣었다. 이 테이블은 개발 곳곳에서 유용하게 사용할 수 있다. 사용자 화면에 아래 그림과 같이 주를 고르는 콤보 박스를 만들어야 한다고 가정해보자. 현재일 기준으로 최근 30주 데이터가 나와야 한다.

[그림 2]

개발자 입장에서 보면, 이와 같은 컨트롤을 만드는 일이 어렵지는 않다. 더욱이 개발 환경이 갈수록 좋아져서 많은 것들이 모듈화가 잘 되어 있다. 어쨌든, 위와 같은 컨트롤에 값을 채워 넣는 작업을 SQL로도 쉽게 해결 할 수 있다. 현재일 기준으로 최근 30주 데이터를 구하기 위해서는 아래 SQL을 사용할 수 있다.

[SQL-7] 현재일 기준으로 최근 30주 가져오기.(현재 2019년12월10일이라고 가정)
SELECT  T1.BAS_YW
FROM    (
        SELECT  T1.BAS_YW||'('||MIN(T1.BAS_YMD)||'~'||MAX(T1.BAS_YMD)||')' BAS_YW
        FROM    C_BAS_YMD T1
        WHERE   T1.BAS_YMD <= '20191210'
        GROUP BY T1.BAS_YW
        ORDER BY T1.BAS_YW DESC
        ) T1
WHERE   ROWNUM <= 30;

 

위 SQL의 실행 결과는 아래와 같다.

결과-7] 현재일 기준으로 최근 30주 가져오기.(현재 2019년12월10일이라고 가정)
BAS_YW
=========================
201950(20191209~20191210)
201949(20191202~20191208)
201948(20191125~20191201)
201947(20191118~20191124)
…

값을 잘 보면 잘못된 부분이 있다. 2019년50주가 2019년12월10일까지 밖에 없다. 2019년50주는 12월15일까지 나와야 한다. 현재일을 기준으로 데이터를 조회했기 때문이다. 주말인 12월15일까지 포함되서 조회하기 위해서는 아래와 같이 서브쿼리를 추가해야 한다.

[SQL-8] 현재일 기준으로 최근 30주 가져오기 – 주말을 포함
SELECT  T1.BAS_YW
FROM    (
        SELECT  T1.BAS_YW||'('||MIN(T1.BAS_YMD)||'~'||MAX(T1.BAS_YMD)||')' BAS_YW
        FROM    C_BAS_YMD T1
        WHERE   T1.BAS_YW <= (SELECT A.BAS_YW FROM C_BAS_YMD A WHERE A.BAS_YMD = '20191210')
        GROUP BY T1.BAS_YW
        ORDER BY T1.BAS_YW DESC
        ) T1
WHERE   ROWNUM <= 30;

기준일자 테이블을 사용해 최근 30주를 손쉽게 구했다. 위 SQL의 경우 성능을 고려해 BAS_YW 컬럼에 인덱스를 고민해 볼 수 있다. C_BAS_YMD 테이블에는 필요하다면 얼마든지 인덱스를 만들어도 된다. 데이터가 한 번 입력되고 나면 추가로 입력되거나 변경될 일은 없기 때문이다. (물론 기준일자의 마지막쯤에 다다르면, 다음 일자들에 대한 생성은 필요하다.)

 

기준일자 테이블을 사용하면, 실적이 없는 일자의 실적도 손쉽게 구할 수 있다. 아래 SQL은 2017년3월의 특정 고객 주문을 조회하는 SQL이다. 주문이 없는 날도 0으로 표현해야 한다.

[SQL-9] 특정 고객의 일별 주문 조회
SELECT  T1.BAS_YMD
        ,COUNT(T2.ORD_SEQ) ORD_CNT
FROM    C_BAS_YMD T1
        ,T_ORD_JOIN T2
WHERE   T1.BAS_YM = '201703'
AND     T1.BAS_YMD = T2.ORD_YMD(+)
AND     T2.CUS_ID(+) = 'CUS_0010'
GROUP BY T1.BAS_YMD
ORDER BY T1.BAS_YMD;

 

위 SQL을 실행하면 CUS_0010 고객의 주문이 없는 일도 모두 0으로 집계해서 조회가 가능하다. 물론 일자 테이블이 없어도 얼마든지 위의 결과를 만들어 낼 수 있다. 아래 SQL을 참고하자.

[SQL-10] 특정 고객의 일별 주문 조회 – CONNECT BY 사용
SELECT  T1.BAS_YMD
        ,COUNT(T2.ORD_SEQ) ORD_CNT
FROM    (
          SELECT TO_DATE('20170301','YYYYMMDD') + (ROWNUM - 1) BAS_YMD 
          FROM   DUAL A
          CONNECT BY TO_DATE('20170301','YYYYMMDD') + (ROWNUM - 1) <= TO_DATE('20170331','YYYYMMDD')
        ) T1
        ,T_ORD_JOIN T2
WHERE   T1.BAS_YMD = T2.ORD_YMD(+)
AND     T2.CUS_ID(+) = 'CUS_0010'
GROUP BY T1.BAS_YMD
ORDER BY T1.BAS_YMD;

 

[SQL-9]와 [SQL-10]의 결과는 동일하다. 하지만 [SQL-9]가 좀 더 깔끔하다는 것을 모두 동의하리라 생각한다.

오라클은 그나마 CONNECT BY가 있기 때문에 기준일자 테이블이 없어도 [SQL-10]과 같은 패턴을 사용할 수 있다. 하지만 MySQL과 MS-SQL은 CONNECT BY가 없기 때문에, 기준일자 테이블이 없으면 더 복잡한 방법을 사용해야 한다. (MS-SQL은 WITH절을 중첩 사용해 CONNECT BY를 구현한다. 사용해 본 사람들은 이러한 방법이 구현도 복잡하며, 성능이 안 좋은 경우도 많다는 것을 알 것이다.)

겨울을 대비해 보일러를 들여 놓는 것처럼, 편리한 개발을 위해 기준일자 테이블 하나 들여 놓기 바란다.

 

끝으로, 숨겨진 퀴즈가 두 개 있다.
첫 번째 퀴즈, [SQL-10]에는 성능에 영향을 주는 잘 못된 부분이 하나 있다. [SQL-9]와  [SQL-10]의 실행계획을 모두 떠보고, 잘 못된 부분을 찾아 고쳐보기 바란다. 아마도 어렵지 않게 찾아 낼 수 있을 것이다.
두 번쨰 퀴즈, [SQL-9]의 IO를 좀 더 개선해 보기 바란다. CPU나 메모리 사용량이 좀 더 늘어도 상관 없다. IO를 줄이는 것이 목표다. SQL만 조금 변경해 IO를 개선해 보기 바란다. 절대 힌트는 사용하지 않아야 한다.

스스로 고민해보고 문제를 풀어보기 바란다. 고민만으로 충분한 도움이 될 것이기 때문이다.

 

 

오늘은 여기까지입니다. 감사합니다.